Complex SQL 2 | find new and repeat customers | SQL Interview Questions

[@devendrabhumarapu7869]

Assignment query:
with cte as(
select order_date,order_amount, row_number() over(partition by customer_id order by
order_date asc) as rn from customer_orders)
select order_date, sum(case when rn=1 then 1 else 0 end) as new_customers,
sum(case when rn>1 then 1 else 0 end) as repeat_customers,
sum(case when rn=1 then order_amount else 0 end) as new_customers_order_amount,
sum(case when rn>1 then order_amount else 0 end) as repeat_customers_order_amount
from cte
group by order_date;
select * from customer_orders;

[@satyamgoyal942]

Select a.order_date,
Sum(Case when a.order_date = a.first_order_date then 1 else 0 end) as new_customer,
Sum(Case when a.order_date != a.first_order_date then 1 else 0 end) as repeat_customer
from(
Select customer_id, order_date, min(order_date) over(partition by customer_id) as first_order_date from customer_orders) a
group by a.order_date;

[@ankitbansal6]

Good.

[@tanushreesaha2276]

impressed

[@psyche5184]

A very good solution, I feel mine is overcomplicating things

[@aniket9595]

Nice one Satyam 😊

[@TonnyPodiyan]

Nice one bro

[@mananyadav6401]

Hey Ankit , your channel is really addictive. Since yesterday I have picked more than 15 problems in a row (which indirectly means I watched 15 videos from your channel straight in a row). I am really enjoying it. People binge watch Netflix and here I am binge watching sql problem solving. Can't express in words, felt like I found the gem on the RUclips. It pumped adrenaline rush in my body when I am able to solve the problems without looking at the solution. At the end comparing my solution with your provided solution and that also is teaching me how to solve any problem in minimal joins and subqueries. Amazing....Amazing...Amazing....Thank you so much for all your hardwork and knowledge sharing.

[@ankitbansal6]

Thats a big compliment for me. Keep rocking 😊

[@shankrukulkarni3234]

For me also same thing,I am not forcefully learning, by intrest I am coming and watching your videos....I can say I addicted to your channel. The positive of your channel is you will provide table with data so it make us to practice while watching your video

[@mansipuhan4827]

Same for me also

[@Tusharchitrakar]

Exactly the same here too. I'm not able to sleep at night till i solve problems and i never see your solution till i solve it myself. Thanks for this good collection of questions

[@sowmya6471]

Exactly..Same here addicted to the explaination and over enthusiastic to solve. Initially to type single line query I was seeing and typing ,now I am watching whole video till end and then writing 5-6lines queries without seeing. @AnkitBansalYou got great teaching skills.

[@PraveenSinghRathore-df3td]

Hi Ankit, 2 years I cracked the DA role with your help, now when I'm preparing again for a switch, this is my go to source material for SQL Prep, thanks for a splendid playlist.
Here is my solution:
SELECT order_date, sum(CASE WHEN rn2 = 1 THEN 1 ELSE 0 END) AS new, sum(CASE WHEN rn2 > 1 THEN 1 ELSE 0 END) AS repeat
FROM (SELECT *,
row_number() over(PARTITION BY customer_id ORDER BY order_date ASC, customer_id ASC) AS rn2 FROM customer_orders)
GROUP BY order_date ORDER BY order_date;

[@ankitbiswas8380]

I was asked exactly the same question in my interview with dunnhumby and I failed to answer as I panicked and tried to give an answer hurriedly . Now after going through your video in steps , I completely understood the approach in how to deal with these questions. Looking forward to the rest of the playlist .

[@ankitbansal6]

Cool

[@siddhantsoni1575]

@@ankitbansal6 I was also asked this questions

[@sachinkapoor2424]

For which job position ankit

[@ankitbiswas8380]

@@sachinkapoor2424 DS

[@kartikjuneja7807]

For how many years experience did you apply?

[@sanjaythumma7537]

Hello Ankit,
A big thank you for this superb SQL playlist. I've started my SQL interview prep with this playlist.
I have come up with my own solution for this problem, this will also handle edge case where same customer has ordered multiple times in a day -
SELECT a.order_date, SUM(CASE WHEN a.cust_order_row = 1 THEN 1 ELSE 0 END) AS new_customers, SUM(CASE WHEN a.cust_order_row 1 THEN 1 ELSE 0 END) AS repeat_customers FROM
(SELECT order_date, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS cust_order_row FROM customer_orders) a GROUP BY a.order_date;

[@swethathiruppathy9973]

Hi Sir, Thank you for all your videos ..Really helpful for learning .
Here is my query
with cte as
(select customer_id,min(order_date) as first_visit_date
from customer_orders
group by customer_id)

select c.order_date,
sum(case when c.order_date = f.first_visit_date then 1 else 0 end) as first_visit_flag,
sum(case when c.order_date != f.first_visit_date then 1 else 0 end) as repeat_visit_flag,
sum(case when c.order_date = f.first_visit_date then order_amount else 0 end) as newCustAmount,
sum(case when c.order_date != f.first_visit_date then order_amount else 0 end) as repeatCustAmount

from customer_orders c
inner join cte f
on c.customer_id=f.customer_id
group by c.order_date
;

[@kothapalliramana4955]

MYSQL Query for the same:-
with cte as(
select order_date, row_number() over(partition by customer_id order by
order_date asc) as rn from customer_orders)
select order_date, sum(case when rn=1 then 1 else 0 end) as new_customers,
sum(case when rn>1 then 1 else 0 end) as repeat_customers from cte
group by order_date;

[@ankitbansal6]

This is good. Thanks for posting 👏

[@rakeshchaudhary3055]

I ended up being very close to your solution with a little difference.
with old_new_counter as (
SELECT *,row_number() over (partition by customer_id order by order_date) old_new_flag FROM customer_orders)
select order_date,
count(case when old_new_flag=1 then 'new_customer' end) count_new_customer,
count(case when old_new_flag>1 then 'old_customer' end) count_repeat_customer
from old_new_counter group by order_date
order by order_date;
Cheers

[@ls47295]

This query will not give expected output in a case where same user has more than 1 order the same date.
I tested using same records 2 times in a table .
Just my input..

[@yashwani209]

Now i will never Forget CTE... Great teaching skill

[@mantisbrains]

select order_date, sum(case when first_purchase = order_date then 1 else 0 end) as new_customer_count,
sum(case when first_purchase != order_date then 1 else 0 end) as repeat_customer_count,
sum(order_amount) as total_amount from
(select * from
(select customer_id, min(order_date) as first_purchase from customer_orders1
group by customer_id)a join (select * from customer_orders1)b
on a.customer_id = b.customer_id)ab
group by ab.order_date
order by ab.order_date; Thank you, Ankit, learning a lot from your channel :)

[@Ian15278]

Thanks, Ankit for this brainstorming question,
MY QUERY
SELECT order_date,
Count(CASE WHEN rnk = 1 THEN cnt END) AS "New Customer",
Count(CASE WHEN rnk > 1 THEN cnt END) AS "Old Customer"
FROM (
SELECT order_date, customer_id,
DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY order_date) AS rnk,
COUNT(*) OVER (PARTITION BY customer_id, order_date) AS cnt
FROM customer_orders1
) A
GROUP BY order_date;

[@parth_pm16]

Hi everyone,
HW Task: add two columns of first_visit_order_amount, last_first_visit_order_amount
Solution:
sum(case when fv.first_visit_date = co.order_date then co.order_amount else 0 end) as first_visit_Order_amt_flag
, sum(case when fv.first_visit_date != co.order_date then co.order_amount else 0 end) as repeat_visit_order_amt_flag
add this two columns in Ankit's solution.

[@siddharth4251]

thanks buddy.

[@jainwho]

Hi Ankit,
with cte as
(select *, row_number() over (partition by customer_id order by order_date) as order_flag
from customer_orders)
select order_date,
sum(case when order_flag=1 then 1 else 0 end) as new_customer_count,
sum(case when order_flag>1 then 1 else 0 end) as repeat_customer_count
from cte
group by order_date

[@vigneshshetty2149]

Hey Ankit,
Thank you for this problem questions. I specially like the assignment you give at the end. As I am a beginner for SQL its very my encouraging and confidence boosting for me.
Assignment sol:-
with first_visit_flag as
(SELECT customer_id, MIN(order_date) as first_visit_date
FROM customer_orders
GROUP BY customer_id),
repeat_visit_flag AS
(
SELECT co.order_date,
fv.first_visit_date,
CASE WHEN co.order_date=fv.first_visit_date THEN 1 ELSE 0 END AS first_visit,
CASE WHEN co.order_date!=fv.first_visit_date THEN 1 ELSE 0 END AS repeat_visit,
CASE WHEN co.order_date=fv.first_visit_date THEN SUM(order_amount) ELSE 0 END AS new_order,
CASE WHEN co.order_date!=fv.first_visit_date THEN SUM(order_amount) ELSE 0 END AS repeat_order
FROM customer_orders co
inner join first_visit_flag fv ON co.customer_id = fv.customer_id
GROUP BY co.order_date,fv.first_visit_date
)
SELECT order_date, SUM(first_visit) as new_customer,SUM(repeat_visit) as repeat_customer, SUM(new_order) AS new_order_amount, SUM(repeat_order) AS repeat_order_amount
FROM repeat_visit_flag
GROUP BY order_date;

[@gagansingh3481]

sELECT order_date,
Sum(cASE WHEN ORDER_DATE = fIRST_dATE THEN 1 else 0 END) AS nEW,
Sum(cASE WHEN ORDER_DATE fIRST_dATE THEN 1 else 0 END )AS rEPEATCUS
FROM
(Select Customer_id,order_date,
Min(order_date) over (Partition by customer_id order by order_date) as First_Date
from customer_orders ) as a
group by order_date got the answer by this too thanks

[@Nikhilg-rs7iv]

seriously no one in the entire youtube explained CTE like this. Made it so simple thank you ankit bro

[@ankitbansal6]

🙏

[@BhanuGupta-x2j]

Very good question.
WITH cte AS
(
SELECT order_id, customer_id, order_date, order_amount, RANK() OVER(PARTITION BY customer_id ORDER BY order_date) AS rnk
FROM customer_orders
)
SELECT order_date,
SUM(CASE WHEN rnk =1 THEN 1 ELSE 0 END) AS new_customer_count,
SUM(CASE WHEN rnk 1 THEN 1 ELSE 0 END) AS repeat_customer_count
FROM cte
GROUP BY order_date;

[@maxsteel4590]

I was asked the same question in curefit in 3rd round. There were 2 extra tables to refer but now I realize it could have been done using single table with min order date criteria. Glad I stumbled on your channel

[@ankitbansal6]

Glad to know 🙏

[@m04d10y1996]

You got this for which profile.

[@maxsteel4590]

@@m04d10y1996 Product analytics

[@Prasad1487]

MY only SQL guru.. Thank you guruji.. love you for ever.

[@debashreesarkar1403]

with cte1 as
(select order_date, case when customer_id= rep_cs then 1 else 0 end as rep_flag,
case when customer_id rep_cs then 1 else 0 end as new_flag from
(select order_date,customer_id,lag(customer_id,3,0) over(order by order_id )as rep_cs
from customer_orders) e1)
select order_date,sum(new_flag) as new_customer, sum(rep_flag) as rep_customer
from cte1
group by order_date

[@debashreesarkar1403]

using lag function with cte

[@Mayank-jw9yy]

Thanks Ankit for great explanatory video>
Here is solution of assignment given in video
WITH first_visit AS (
SELECT customer_id, min(order_date) AS first_visit_date
FROM customer_orders
GROUP BY customer_id)
SELECT co.order_date,
SUM(CASE WHEN co.order_date = fv.first_visit_date THEN 1 ELSE 0 END) AS first_visit_customer,
SUM(CASE WHEN co.order_date != fv.first_visit_date THEN 1 ELSE 0 END) AS repeat_visit_customer,
SUM(CASE WHEN co.order_date = fv.first_visit_date THEN order_amount ELSE 0 END) AS first_visit_customer_order,
SUM(CASE WHEN co.order_date != fv.first_visit_date THEN order_amount ELSE 0 END) AS repeat_visit_customer_order
FROM customer_orders co
INNER JOIN first_visit fv ON co.customer_id = fv.customer_id
GROUP BY co.order_date

[@shubhamchakravorty]

Thanks, ankit for this brainstorming question, initially couldnt figure out the but the min(order_date) that you gave was the key. I accomplished this with subqueries:
select order_date,count(new_customer) as new,count(repeat_customer) as repeat from (
select order_date,
case when order_date=first_order_date then 'new_customer'
end as new_customer,
case when order_date!=first_order_date then 'repeat_customer'
end as repeat_customer
from (
select a.*,b.first_order_date from customer_orders a
join(
select customer_id,min(order_date) as first_order_date
from customer_orders
group by customer_id) b
on a.customer_id=b.customer_id)c)d
group by order_date
order by order_date asc;

[@rakshithareddy498]

Here is my query
With cte_1 as (
Select *, rank() over(partition by customer_id order by order_date) as ranked from customer_orders
),
cte_2 as (
Select order_date, case when ranked = 1 then 'new' else 'repeat' end as new_or_repeat from cte_1
)
Select order_date, sum(case when new_or_repeat = 'new' then 1 else 0 end) as new_customer,sum(case when new_or_repeat = 'repeat' then 1 else 0 end) as repeat_customer from cte_2
group by order_date;
Thank you for your efforts

[@tamojeetchatterjee9385]

Hey Ankit Thanks for providing this question my solution for this problem
with cte as (select order_id , customer_id , order_date ,
lag(customer_id)over(partition by customer_id order by order_date) as statements
from customer_orders)
select order_date , sum(case when statements is null then 1 else 0 end) as new_customer_count
, sum(case when statements is not null then 1 else 0 end) as old_customer_count
from cte
group by order_date order by order_date

[@rakeshpanigrahi577]

Thanks Ankit, Here is my solution -
with cte as (
select *,
count(order_id) over(partition by customer_id
order by order_date
rows between unbounded preceding and current row) as cnt
from customer_orders
)
select order_date,
sum(case when cnt = 1 then 1 else 0 end) as new_cust_ind,
sum(case when cnt > 1 then 1 else 0 end) as repeat_cust_ind,
sum(case when cnt = 1 then order_amount else 0 end) as new_cust_amt,
sum(case when cnt > 1 then order_amount else 0 end) as repeat_cust_amt
from cte
group by order_date;

[@varunas9784]

Good one as always!
Here's my approach:
==================================
with cust_count as (select *,
COUNT(customer_id) over(partition by customer_id order by order_date) [flag]
from customer_orders)
select order_date,
COUNT(case when [flag] = 1 then 1 end) [New customers],
COUNT(case when [flag] > 1 then 1 end) [Repeat customers]
from cust_count
group by order_date
==================================

[@husnabanu4370]

Hi Ankit
Thanks your videos are helping me to break down complex scnearios into smaller parts and then combine the whole query.....so i did the query in different way..Please do let me know if thats correct since the motive is only to find duplicate and new customers
with cte as (
select customer_id,count(1) as ranking from customer_orders group by customer_id)
select * , case when ranking>1 then 'Duplicate' else 'New' end as status_customer from cte;
customer_id ranking status_customer
100 3 Duplicate
200 1 New
300 1 New
400 2 Duplicate
500 1 New
600 1 New

[@susmitakundu6120]

Thanks Ankit for your guidance. Please have a look below query
select sum(case when tc> 1 then 1 else 0 end )as repeat_customer,
sum(case when tc= 1 then 1 else 0 end )as new_customer from (select customer_id, count( customer_id) as tc
from customer_orders
group by customer_id) a ;

[@ujjwalvarshney3188]

select order_date, sum(case when order_date =mn then 1 else 0 end) as new_customer ,sum(case when order_date mn then 1 else 0 end) as old_customer
from
(
select * ,min(order_date)over(partition by customer_id) as mn from customer_orders )
group by 1 order by 1

[@bukunmiadebanjo9684]

Great content as always.
Here is my attempt to the homework
with first_order_table as
(select customer_id, min(order_date) as first_order_date
from customer_orders
group by customer_id)
select co.order_date,
sum(case when co.order_date = fot.first_order_date then co.order_amount else 0 end) as order_amount_by_new_customer,
sum(case when co.order_date fot.first_order_date then co.order_amount else 0 end) as order_amount_by_repeat_customer
from customer_orders co
join first_order_table fot on fot.customer_id = co.customer_id
group by co.order_date
order by 1

[@SundarKumar-pg6wx]

I have implemented with this logic.
with cte as (
select *,ROW_NUMBER() over(partition by customer_id order by order_date asc) as rnk from customer_orders),
cte_not_1 as (select order_date,count(*) as cnt from cte where rnk 1 group by order_date)
select * from (
select order_date,count(*) as new_customer_count from cte t1
where t1.rnk = 1
group by order_date) t1 left join cte_not_1 t2
on t1.order_date = t2.order_date

[@anil5612ag]

i tried it and here is my solution:
with rn1 as(
select *,row_number() over(partition by customer_id order by order_date) as rn from customer_orders
)
select order_date,sum(case when rn =1 then 1 else 0 end) as new_customer,sum(case when rn > 1 then 1 else 0 end) as repeat_customer from rn1 group by order_date

[@arijitsaha5499]

Hi Ankit, thanks for creating such videos.
Here is my approach:
with sequenced_order_table as(
select *, dense_rank() over(partition by customer_id order by order_date) as order_seq
from customer_orders)
SELECT order_date,
count(case when order_seq = 1 then customer_id end) as new_customer,
count(case when order_seq > 1 then customer_id end) as old_customer
FROM sequenced_order_table
group by 1
order by 1

[@kartikjuneja7807]

Bro , did you check this in the db?

[@abhishekjain4895]

Hey Ankit, I have used a different approach:
with new_table as(
select order_date,count(customer_id) as new_customer from customer_orders a
where 0=( select count(*) from customer_orders b where a.order_date>b.order_date and a.customer_id=b.customer_id) group by order_date),
repeat_table as (select order_date,count(customer_id) as old_customer from customer_orders a
where ( select count(*) from customer_orders b where a.order_date>b.order_date and a.customer_id=b.customer_id)>0 group by order_date)
select case when a.order_date is null then b.order_date else a.order_date end as date ,new_customer,old_customer from
new_table a full outer join repeat_table b on a.order_date=b.order_date;

[@anishchhabra6085]

Solved the question without looking into the solution in MySQL, I have used the concept of sum with case when after seeing it in your other video, it is very helpful and important concept:
with cte as
(
select *,
row_number() over(partition by customer_id) as rn
from customer_orders
order by order_date
)
select order_date,
sum(case when rn = 1 then 1 else 0 end) as new_customer_count,
sum(case when rn = 1 then 0 else 1 end) as repeat_customer_count
from cte
group by order_date;
Please let me know if there is some issue in this code

[@diveshrai8490]

Nice one bro

[@aparnapaul-x5r]

Hi Ankit,your channel is very helpful and the way you are explaining is just amazing. Here is my solution for this,WITH CTE AS (
SELECT ORDER_ID,CUSTOMER_ID,ORDER_DATE,ORDER_AMOUNT,
CASE WHEN PRIV IS NULL THEN 1 ELSE 0 END AS NEW_FLAG,
CASE WHEN PRIV IS NOT NULL THEN 1 ELSE 0 END AS OLD_FLAG FROM (
select *,
lag(ORDER_DATE) over(partition by CUSTOMER_ID order by ORDER_DATE) as PRIV from customer_orders) ORDER BY ORDER_ID)
SELECT ORDER_DATE,SUM(NEW_FLAG) AS NEW_CUSTOMER,SUM(OLD_FLAG) AS OLD_CUTOMER FROM CTE
GROUP BY ORDER_DATE;

[@suman3316]

this what a real time problems...thanks and keep bring such

[@ankitbansal6]

Sure. Thanks.

[@saib7231]

hi ankith this is also working
with cte as (
SELECT *,min(order_date) over (partition by customer_id) as first_date
FROM customer_orders as a
)
select order_date,count(case when order_date first_date then customer_id end) as repeat,
count(case when order_date = first_date then customer_id end) as new,
count(customer_id) as total
from cte
group by order_date

[@plumbarch]

There is a small difference between the repeat customers (those purchased for the consecutive days) and old customers (no consecutive days condition).
For the repeat customers - purchasing for consecutive days
select order_date, sum(new_customer) as new_customer, sum(repeat_customer) as repeat_customer from
(select customer_id, order_date, case when order_date=first_visit then 1 else 0 end as new_customer,
case when date_diff(order_date, prev_day)=1 then 1 else 0 end as repeat_customer (
select customer_id, order_date, min(order_date) over (partition by customer_id) as first_visit , lag(order_date,1,0) over (partition by customer_id order by order_date) as prev_day from customer_orders)x)y group by order_date

[@mohdtoufique7446]

Hi Ankit..Thanks for your efforts.. I have an alternate solution as well
WITH CTE AS(
select *,CASE WHEN(DENSE_RANK()OVER(PARTITION BY customer_id ORDER BY order_date)=1) THEN 'New'
ELSE 'Repeat' END AS IND_CUSTOMER
from customer_orders
)
SELECT order_date,count(CASE WHEN IND_CUSTOMER='New' THEN order_id END) AS no_new_customer,
count(CASE WHEN IND_CUSTOMER='Repeat' THEN order_id END) AS no_repeat_customer
FROM CTE
GROUP BY order_date
ORDER BY order_date

[@ankitbansal6]

Thanks for posting. This is also good. 👍

[@techmania979]

I used below query,
with cte as(
select *,min(order_date) over(partition by customer_id) as first_visit from customer_orders
)
select order_date,
sum(
case when order_date=first_visit then 1 else 0 end
) as first_time,
sum(
case when order_datefirst_visit then 1 else 0 end
) as sec_time
from cte
group by order_date

[@ankitbansal6]

Thank you for posting.

[@arunbagh-db4lp]

Thanks Ankit you providing this kind of promblem solving videos , it very helpful 😄

[@zeeshanahmed2594]

Thank you very much Sir, for this practical question and your step by step explanation.

[@ankitbansal6]

You are most welcome

[@Datalab_FP]

This is a clear and concise explanation

[@hackeymabel8296]

tysm for your video
here's my attempt
SELECT t1.order_date,
sum( CASe when t1.customer_id not in
(select t2.customer_id
from customer_orders as t2
WHERE t2.order_date=t1.order_date - INTERVAL '1day')
then 1 ELSE 0 end)
as news,
sum( CASe when t1.customer_id in
(select t2.customer_id
from customer_orders as t2
WHERE t2.order_date=t1.order_date - INTERVAL '1day')
then 1 ELSE 0 end)
as repeat
from customer_orders as t1
GROUP by t1.order_date

[@hackeymabel8296]

it is wrong tho

[@KoushikT]

Without using join
***********************
with A as (select customer_id,order_date,lag(order_date) over (partition by customer_id) as previous_visit from customer_orders)
select
order_date,
sum(case when previous_visit is null then 1 else 0 end) as new_customer,
count(*)-sum(case when previous_visit is null then 1 else 0 end) as repeat_customer from A
group by order_date
order by order_date

[@ankitbansal6]

Good one

[@asishnayak108]

for assignment tasks. Add separate order amounts for the first visitor and repeater
sum(case when co.order_date = fv.first_visit_date then co.order_amount else 0 end) as Total_fv_order,
sum(case when co.order_date fv.first_visit_date then co.order_amount else 0 end) as Total_repeat_order

[@arthurmorgan9010]

My solution was:
with firsttable(customerid,firstorderdate) as
(
select customer_id,min(order_date) as first_order_date from customer_orders
group by customer_id
)
select order_date,sum(case when (customerid = customer_id and firstorderdate = order_date) then 1 else 0 end) as newcustomer,
sum(case when (customerid = customer_id and firstorderdate != order_date) then 1 else 0 end) as oldcustomer
from customer_orders,firsttable
group by order_date

[@akashkamble9902]

Amazing Video and Interpretation, thanks a lot for making this video, here is the answer for assignment
with fv as (
select CUSTOMER_ID, min(ORDER_DATE) as fisrtVist from customer_orders group by CUSTOMER_ID),
final as(
select co.ORDER_DATE,
SUM(case when co.ORDER_DATE = fv.fisrtVist then 1 else 0 end) as firstVistFinal,
SUM(case when co.ORDER_DATE != fv.fisrtVist then 1 else 0 end) as repeatVistFinal,
SUM(case when co.ORDER_DATE = fv.fisrtVist then order_amount else 0 end) as firstVistAmout,
SUM(case when co.ORDER_DATE != fv.fisrtVist then order_amount else 0 end) as repeatVistAmout
from customer_orders co , fv where
fv.CUSTOMER_ID = co.CUSTOMER_ID
group by co.ORDER_DATE
)
select * from final

[@shourya4092]

What a Explanation mind blowing ❤️❤️❤️

[@ankitbansal6]

Thank you so much 😀

[@nidhisingh4973]

----------------count of new and repeat customer
with cte as(
select customer_id, order_date,case when order_date =min(order_date) over (partition by customer_id) then 'New customer' end as new,
case when order_date min(order_date) over (partition by customer_id) then 'Repeat' end as r
from customer_orders)
select distinct order_date, count(new) as new_customer ,count(r) as repeat_customer from cte
group by order_date

[@prashant887]

with temp as (
select cust_id,order_date,lag(order_date) over(PARTITION by cust_id order by order_date) as prev_order
from cust_orders )
select trunc(order_date),
sum(case when prev_order is null then 1 else 0 end) first,sum(case when prev_order is null then 0 else 1 end) as prev from temp
group by trunc(order_date);

[@ankitbansal6]

Thanks for posting 👏

[@smitpatel8782]

select c.order_date ,
sum(case when c.customer_id in (select co.customer_id from customer_orders co
where co.order_date < c.order_date) then 1 else 0 end) as Repeat_customer,
sum(case when c.customer_id not in (select co.customer_id from customer_orders co
where co.order_date < c.order_date) then 1 else 0 end) as Not_Repeat_customer
from customer_orders c
group by 1

[@florincopaci6821]

Thank you for this video! Please come with problems like this. Thank you

[@ankitbansal6]

Sure.

[@techlearnersmarathi5461]

Thanks for sharing this problem and approach behind same. below is my query
select co.order_date,
sum(case when CO.order_date= fv.first_date then 1 else 0 end) as no_of_new_cust,
sum(case when CO.order_date!= fv.first_date then 1 else 0 end) as no_of_repeat_cust,
sum(case when CO.order_date= fv.first_date then order_amount else 0 end) as total_amount_by_new_cust,
sum(case when CO.order_date!= fv.first_date then order_amount else 0 end) as total_amount_by_old_cust
from [dbo].[customer_orders] co
inner join
(select customer_id, min(order_date) as first_date from [dbo].[customer_orders]
group by customer_id) fv
on co.customer_id=fv.customer_id
group by order_date
order by CO.order_date;

[@hairavyadav6579]

A big thank you sir,, Nice explanation ❤

[@ankitarora6329]

select order_date, sum(new_cust), count(cust_id) - sum(new_cust) as old_cust from (
select *, case when min_date = order_date then 1 else 0 end as new_cust from (
select *, min(order_date) over (partition by cust_id) as min_date
from customer_orders) T1 order by order_id) T2 group by order_date;

[@ankitbansal6]

Looks good. thanks for posting

[@ashish31416]

Thank you so much Ankit Bansal. This is really helpful.

[@janhavighuge7095]

Thankyou for another great question!
My solution to this question:
SELECT new.order_date,
COUNT(CASE WHEN previous.customer_id IS NULL THEN 1 END) AS new_cust,
COUNT(DISTINCT(CASE WHEN previous.customer_id IS NOT NULL THEN previous.customer_id END)) AS repeat_cust,
SUM(CASE WHEN previous.customer_id IS NULL THEN new.order_amount END) AS amount_by_new_cust,
SUM(DISTINCT(CASE WHEN previous.customer_id IS NOT NULL THEN new.order_amount ELSE 0 END)) AS amount_by_repeat_cust
FROM customer_orders new
LEFT JOIN customer_orders previous
ON
previous.customer_id = new.customer_id
AND previous.order_date < new.order_date
GROUP BY new.order_date;

[@kunnalkhatreja6191]

select b.order_date,sum(case when a.first_order = b.order_date then 1 else 0 end) as first_time_customers, Sum(case when a.first_order b.order_date then 1 else 0 end) as repeat_customers,
sum(case when a.first_order = b.order_date then order_amount end) as new_cust_order,sum(case when a.first_order b.order_date then order_amount end) as repeat_cust_order
from
(select customer_id,min(order_date) as first_order from customer_orders group by customer_id) a
join customer_orders b on a.customer_id=b.customer_id
group by b.order_date

[@Mahi_RSV]

New and repeat customers each day:
with cte_1
as
(
select customer_id,order_date,rank() over(partition by customer_id order by order_date) as rnk
from customer_orders
)
select order_date,
sum(case when rnk=1 then 1 else 0 end ) as new_customers,
sum(case when rnk>1 then 1 else 0 end ) as repeat_customers
from cte_1
group by order_date
Sales by New and repeat customers each day:
with cte_1
as
(
select customer_id,order_date,order_amount,rank() over(partition by customer_id order by order_date) as rnk
from customer_orders
)
select order_date,
sum(case when rnk=1 then order_amount else 0 end ) as new_customers_sales,
sum(case when rnk>1 then order_amount else 0 end ) as repeat_customers_sales
from cte_1
group by order_date
Enjoyed solving this question! Thanks Ankit Bhai :)

[@IndianHacker-hisBest]

Really good channel and informative videos.

[@ankitbansal6]

Glad you like them!

[@NehaAgarwal-l8l]

with CTE as
(
select *, dense_rank() over(partition by customer_id order by order_date) as rnk from customer_orders
)select order_date,sum(case when rnk=1 then 1 else 0 end ) as new_order, sum(case when rnk1 then 1 else 0 end ) as repaet_customer
from CTE
group by order_date

[@ahmedhusain5415]

Slightly elaborate, but just from solving point of view
with cte as
(
Select customer_id
from customer_orders
group by customer_id
having COUNT (customer_id)>1
), aggregate_query as
(
Select order_date, customer_id, (case when customer_id in (Select customer_id from cte) then 1 else null end) as repeat_customers
from customer_orders
where order_date (Select MIN(order_date) from customer_orders)
)
Select order_date, COUNT(customer_id) as total_customers, COUNT(repeat_customers) as repeat_customers, COUNT(customer_id)-COUNT(repeat_customers) as new_customers
from aggregate_query
group by order_date
union
Select order_date, COUNT(customer_id) as total_cutomers, 0 as repeat_customers, 0 as new_customers
from customer_orders
where order_date = (Select MIN(order_date) from customer_orders)
group by order_date

[@Ashu23200]

My assignment query
with cte as (
select customer_id, order_date, order_amount, MIN(order_date) over (partition by customer_id) as first_order_date from customer_orders
)
, cte2 as (
select *, case when order_date = first_order_date then 'New_Customer' else 'Repeat_Customer' end as Customer_Type
--, COUNT(1) as mnjh
from cte
)
select Customer_Type,COUNT(1) as Count_of_Customers ,sum(order_amount) as sales from cte2 group by Customer_Type

[@ajeshrajan8079]

Great explanation for both approach and solution

[@ankitbansal6]

Glad you liked it

[@harshSingh-if4zb]

with cte as(
select distinct cust_id, first_value(order_dt) over(partition by cust_id order by order_dt) as fv
from emp )
select
order_dt,
sum(case when c.fv = e.order_dt then 1 else 0 end ) as first_visit
,sum(case when c.fv e.order_dt then 1 else 0 end ) as repete_visit
from emp e join cte c
on e.cust_id = c.cust_id
group by e.order_dt

[@prekshasharma1216]

Assignment:
with CTE as(
select customer_id, min(order_date) as first_visit
from customer_orders
group by customer_id)
select c.order_date,
sum(case when c.order_date = t.first_visit then 1 else 0 end) as first_visit_flag,
sum(case when c.order_date != t.first_visit then 1 else 0 end) as repeat_visit_flag,
sum(case when c.order_date = t.first_visit then order_amount else 0 end) as first_visit_amount,
sum(case when c.order_date != t.first_visit then order_amount else 0 end) as repeat_visit_amount
from customer_orders c join CTE t
on c.customer_id = t.customer_id
group by order_date
order by order_date

[@vijaygupta7059]

using other method
with cte as(
Select *
, case when customer_id in (select customer_id from customer_orders where order_date

[@keerthanakalyanapu7475]

Select a.order_date,
Sum(Case when a.order_date = a.first_order_date then 1 else 0 end) as new_customer,
Sum(Case when a.order_date != a.first_order_date then 1 else 0 end) as repeat_customer,
Sum(Case when a.order_date = a.first_order_date then A.order_amount else 0 end) as new_customer_amt,
Sum(Case when a.order_date != a.first_order_date then A.order_amount else 0 end) as repeat_customer_amt
from(
Select customer_id, order_date,order_amount, min(order_date) over(partition by customer_id) as first_order_date from customer_orders) a
group by a.order_date order by a.order_date;

[@rishabhralli9151]

with cte as(
select customer_id,order_date,row_number() over(partition by customer_id order by order_date) as rn from customer_orders)
select order_date,
sum(case when rn=1 then 1 else 0 end) as new_customer_count,
sum(case when rn>1 then 1 else 0 end) as repeated_count
from cte
group by 1
my approach

[@ItssLeviYT]

Alternate method for solving the problem:
with cte as
(
select *, row_number() over(partition by customer_id order by order_date) as ranking
from customer_orders
order by customer_id
)
select order_date,
sum(case when ranking = 1 then 1 else 0 end) as newly_joined_customers,
sum(case when ranking 1 then 1 else 0 end) as repeat_customers
from cte
group by order_date
;

[@md.shabbirhossainbhuiyea4363]

What if the same customer visits the website twice or thrice and orders each time? In that case, he should be a repeat customer. However, according to your solution, he won't be counted as a repeated customer as his min(order_date) = order_date.
What do you think?
However, your tutorials have been really helpful to me. Really appreciate your effort.

[@mdfurqan]

Hey so what do you think the solution for this ?
Could you please help in this ?

[@vandanaK-mh9zo]

then I think we need timestamp as well not only the date part.

[@mocococo2877]

Well, he will be both new AND repeat customer as per current problem explanation. So in this case new problem explanation will be needed.

[@sarveshrajan88]

Amazing Video Ankit, Commenting my approach SELECT
B.order_date,
SUM(CASE WHEN FC.customer_id IS NULL THEN 0 ELSE 1 END) AS NEW_CUSTOMER_COUNT
,SUM(CASE WHEN FC.customer_id IS NULL THEN 1 ELSE 0 END) AS REPEAT_CUSTOMER_COUN
,SUM(CASE WHEN FC.CUSTOMER_ID IS NOT NULL THEN B.ORDER_AMOUNT ELSE 0 END) AS NEW_CUSTOMER_SUM
,SUM(CASE WHEN FC.customer_id IS NULL THEN B.order_amount ELSE 0 END) AS REPEAT_CUSTOMER_SUM
FROM customer_orders AS B
LEFT JOIN
(SELECT A.CUSTOMER_ID,A.order_date,ROW_NUMBER() OVER(PARTITION BY A.CUSTOMER_ID ORDER BY A.ORDER_DATE) AS RNK
FROM customer_orders AS A) AS FC
ON B.order_date=FC.order_date AND B.customer_id=FC.customer_id AND FC.RNK='1'
GROUP BY B.order_date

[@ft_peakhd2921]

I solved this using Window functions and cte,
My solution:
with cte as (select customer_id,row_number() over(partition by customer_id order by customer_id) num,order_date
from customer_orders group by customer_id,order_date)
select order_date, sum(case when num=1 then 1 else 0 end ) as new_cust,sum(case when num>1 then 1 else 0 end) repeat_cust
from cte group by order_date;

[@ashwingupta4765]

Query with Assignment
with cte as (
select *,
min(order_date) over(partition by customer_id) as first_vist_date
from customer_orders
)
Select order_date,
sum(case when order_date = first_vist_date then 1 else 0 end) as first_customer,
sum(case when order_date!=first_vist_date then 1 else 0 end) as repeat_customer,
sum(case when order_date = first_vist_date then order_amount else 0 end) as first_customer_amount,
sum(case when order_date!=first_vist_date then order_amount else 0 end) as repeat_customer_amount
from cte
group by order_date

[@anupampurkait6066]

I used below approach:
select order_date,
sum(case when rn=1 then 1 else 0 end) as new_customer_count,
sum(case when rn > 1 then 1 else 0 end) as repeat_customer_count,
sum(case when rn=1 then order_amount else 0 end) as rev_new_customers,
sum(case when rn > 1 then order_amount else 0 end) as rev_repeat_customers
from
(select *, row_number() over(partition by customer_id) as rn
from customer_orders) as c group by order_date;

[@vinaykumar-hb7rf]

Love the way of explanation with step by step. 😀

[@ankitbansal6]

🙏

[@Liftsquat]

I was asked this same question today in amazon interview. The question was lil changed but concept was same. They needed new user counts in a year 2022 only and wanted the output to be displayed as weekly level. So for week 1 how many new users till week 52. I was able to solve this though i took some time not sure if they judge on the speed as well. Thanks for this video

[@VishalKumar-xl2xd]

Thanks

[@vijaypalmanit]

You make it so easy, superb explanation

[@kidsfun9701]

Assignment done:-
Select t1.order_date,Sum(case when t1.order_date=t2.first_order_date then 1 else 0 end) as New_count,
Sum(case when t1.order_datet2.first_order_date then 1 else 0 end) as Repeat_count,
Sum(case when t1.order_date=t2.first_order_date then order_amount else 0 end) as New_order_amount,
Sum(case when t1.order_datet2.first_order_date then order_amount else 0 end) as Repeat_order_amount
from customer_orders t1
left join(
Select customer_id, min(order_date) as first_order_date from customer_orders group by customer_id) as t2 on t2.customer_id=t1.customer_id
group by t1.order_date;

[@bapanbairagya9642]

awesome problem, Thank you so much for posting.

[@rahuldave6699]

My solution
with cte as(
select *, rank() over (partition by customer_id order by order_date) as rnk from customer_orders)
select order_date ,count(case when rnk = 1 then 1 else Null end ) as new_customer, count(case when rnk>1 then 1 else Null end) as repeat_customer
from cte
group by order_date

[@hairavyadav6579]

My approach
with cte as(select *,dense_rank() over(partition by customer_id order by order_date) as rnk from customer_orders order by order_id)
select order_date, sum(case when rnk=1 then 1 else 0 end) as new_customer_count ,sum(case when rnk!=1 then 1 else 0 end) as repeat_cutomer_count
from cte group by order_date;

[@mdliyaquathusain1239]

My solution:
with first_order as
(
select
order_date,
customer_id,
min(order_date) over(partition by customer_id) as first_order_date
from customer_orders
)
select
order_date,
sum(case when order_date = first_order_date then 1 else 0 end) as new_customer_count,
sum(case when order_date first_order_date then 1 else 0 end) as repeat_customer_count
from first_order
group by 1
order by 1;

[@saurabhsomkuwar1276]

Wow Ankit, your videos on SQL are so good, informative and helpful. Thanks a lot for making them. Keep going.

[@ankitbansal6]

Thank you 😊

[@HimanshuSharma-uk6td]

Hi Ankit,
Please have a look on below answer using windows function.
with ft as (select customer_id, order_date,
Dense_rank() over(Partition by customer_id order by order_date) as R
from customer_orders)
select order_date,sum(new_customer) as new_customer,
sum(existing_customer) as existing_customer
from
(select *,
case when R > 1 then 1 else 0 end as existing_customer,
case when R = 1 then 1 else 0 end as new_customer
from ft) t1
group by order_date
order by order_date;

[@akashwatar6633]

I am planning to complete all the SQL videos created by you in order to learn SQL. I will post a comment on each video and like it as a checklist for completed videos, starting from the beginning.

[@luckyagrawal6029]

Here's my solution:
Select a.order_date,
SUM(CASE WHEN a.order_date = a.first_order_date THEN 1 ELSE 0 END) AS new_customer,
SUM(CASE WHEN a.order_datea.first_order_date THEN 1 ELSE 0 END) AS repeat_customer
FROM(Select customer_id, order_date, MIN(order_date) OVER(PARTITION BY customer_id) As first_visit_customers)a
GROUP BY a.order_date;

[@ankitbansal6]

Good one

[@AshutoshVerma-d4z]

with temp as(
select order_date, case
when customer_id in (select customer_id from customer_orders c2 where c2.order_date

[@suriyas6338]

Hi Aniket,
Here is my solution :)
with cte1 as(
select *, ROW_NUMBER() OVER(PARTITION BY customer_id order by order_date) as row_num
from customer_orders
)
select order_date, sum(case when row_num = 1 then 1 else 0 end) as new_cus,
sum(case when row_num > 1 then 1 else 0 end) as rep_cus,
sum(case when row_num =1 then order_amount else 0 end) as newCus_order_amnt,
sum(case when row_num >1 then order_amount else 0 end) as repCus_order_amnt
from cte1
group by order_date

[@arindamnath1233]

with temptable as(select *, Row_number() over(partition by customer_id order by order_date) as seq from orders)
select order_date, sum(case when seq = 1 then 1 else 0 end) as new_cust,
sum(case when seq > 1 then 1 else 0 end) as repeat_cust
from temptable
group by order_date

[@thestackingwomen1055]

select
Order_date ,
Count(case when R>1 then 1 else Null end) as Number_Of_Repeated_Customers,
count(*) -Count(case when R>1 then 1 else Null end) As Number_Of_New_Customers
from (
select * , row_Number() over(Partition by customer_id order by Order_date asc) As R from customer_orders
) As TT
Group by Order_date
Order by Order_date asc

[@ankitbansal6]

Thanks for posting. Good stuff.

[@vaibhavtiwari8670]

# 1 asigmnet solution
case when first_visit_date=order_date then order_amount else 0 end as amount_spend_new,
case when first_visit_date!=order_date then order_amount else 0 end as amount_spend_old
select sum(amount_spend_new) as new_customer_spend,sum(amount_spend_old) as old_customer_spend,sum(new_customer) as new_customers,sum(repeat_customer) as repeat_customer,order_date from customer
group by order_date
assignment completed

[@abhishekshrivastava9097]

we can add twist by allowing customer to order multiple time in single day.
Test case: Lets add one more row - insert into customer_orders values(1,100,'2022-01-01',3000);
now we have to modify our solutions to handle this case.

[@vigneshnagaraj7137]

In that case there has to time factor column has to be in consideration.

[@sandeepanmahapatra4888]

This was my approach, using RANK() concept -
WITH Orders_with_rank AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS Order_rank,
CASE WHEN Order_rank = 1 THEN 1 ELSE 0 END AS "New Customer" ,
CASE WHEN Order_rank > 1 THEN 1 ELSE 0 END AS "Returning Customer"
FROM customer_orders
)
SELECT order_date,
SUM("New Customer") AS "New Customers",
SUM("Returning Customer") AS "Returning Customers"
FROM Orders_with_rank
GROUP BY order_date

[@venkataram6460]

Nice work Ankit, your way of solving the problem is simple but effective.

[@kinzorize]

correct query in case you are facing error in creating the table :
CREATE TABLE customer_orders (
order_id integer,
customer_id integer,
order_date date,
order_amount integer
);
INSERT INTO customer_orders VALUES
(1, 100, '2022-01-01', 2000),
(2, 200, '2022-01-01', 2500),
(3, 300, '2022-01-01', 2100),
(4, 100, '2022-01-02', 2000),
(5, 400, '2022-01-02', 2200),
(6, 500, '2022-01-02', 2700),
(7, 100, '2022-01-03', 3000),
(8, 400, '2022-01-03', 1000),
(9, 600, '2022-01-03', 3000);

[@rajasundar9547]

@ankit bansal
can we also write this query in this way Ankit? Same output
select order_date,
count(case when rn = 1 then 'new' end) newcust,
count(case when rn != 1 then 'repeated' end) repcust from
(select
customer_id,
order_date,
row_number() over(partition by customer_id order by order_date asc ) 'rn'
from newrepeat) newt
group by order_date
order by order_date asc

[@fahadmahmood7150]

Very good question and very well explained. Great video Ankit :)

[@ankitbansal6]

Thank you 😊