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

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;

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

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 RU-vid. 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.

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;

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

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

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 .

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;

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

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

such a great explanation!!!

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

You make it so easy, superb explanation

best step by step practice

awesome problem, Thank you so much for posting.

Your videos are amazing. Keep up the good work!

awesome explanation ...

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

This is a clear and concise explanation

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

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

Love the way of explanation with step by step. 😀

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;

love your great content.

Very useful video

Subscribed on 2nd video in your channel, Great stuff.

VERY Good Query

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;

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

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

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;

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

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 ;

What a Explanation mind blowing ❤️❤️❤️

Nice explanation Brother want more such videos thank you Brother 🙂

Great explanation for both approach and solution

Best tutor

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;

This is perfect!!

This was smooth.

Really good channel and informative videos.

Nice totala

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

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;

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

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

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

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

Best. Than kyou.

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

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;

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

What a beautiful question, make your brain to hit hard.

Fanstastic channel.

You are awesome 💌

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.

Good explanation bro

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

Nice

Can you pls share consolidated list of Easy and Medium SQLs if possible I'm xls or pdf format?

Hey Ankit, Your videos are really awesome and informative. Can you make some content or share resources regarding learn data analysis through python required for data analyst role

Very nicely explained

more help full

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;

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;

finished watching

Today exactly same question was asked to Me for Cummins.

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

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 ;

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

Day 2 of 47 Thanks for the video series

thank you

Thanks

@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

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

Day 1 Example 2 done

Hi Ankit if we need to add Customer_id also in the select list means, what we should do?

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

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.

Hi Ankit could you suggest some resources for data modeling

hi there may i know why i am getting syntax error following the same in pg admin and mysql

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

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

very helpful. Can you please create a playlist for python qiestions asked in Data Engineering interview ?

Hi Ankit can you also upload the video of assignment too ?

Which one approach is considered optimized the join one or without join ?

Hi Ankit, I just first saw the question and tried to do it myself. After I was done and I came back to the video, even though the result was same the methods were different. I am not sure on how to validate my answer on huge data though. Can you please help me to know if my method lacks anything? WITH CTE AS ( SELECT ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date ASC ) AS RN ,customer_id, order_date FROM customer_orders ) SELECT order_date ,SUM( CASE WHEN RN=1 THEN 1 else 0 END )AS NEW_COUNT ,SUM(CASE WHEN RN1 THEN 1 else 0 END ) AS REPEAT_COUNT FROM CTE GROUP BY order_date ORDER BY order_date Thanks in advance!

I have tried a bit different approach with counter as ( select *, count(customer_id) over(partition by customer_id order by order_date) as ct from customer_orders ) select order_date, sum(case when ct=1 then 1 else 0 end) as New_customer_counter, sum(case when ct!=1 then 1 else 0 end) as old_customer_counter, sum(case when ct=1 then order_amount else 0 end ) as New_customer_revenue, SUM(case when ct!=1 then order_amount else 0 end ) as Old_customer_revenue from counter

Thanks Ankit. Can you please share github link for code. That would be really helpful.

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;

Hi Ankit, Nice job. I have a query - lets say like given scenario of new and repeat customer, if we do have few more scenarios like New, retained, Unretained, Reactivated and Lapsed customers. I/p table has millions of rows. Then, instead of self joining main table again and again, can we apply required filters and take data in CTE and then use that as a main table will help to optimize performance?

Hi Ankit, thax for your informative video, i just started practicing sql with ur YT channel, I tried to add order_amount but i could not sumup the value of first_ and repeat_date. Here is my input below: select *from customer_orders; with first_visit as( select customer_id, min(order_date) as first_date, Order_amount from customer_orders group by customer_id), Order_amount as( select co.customer_id, co.order_date, case when co.order_date = fv.first_date then 1 else 0 end as First_time_customer, case when co.order_date != fv.first_date then 1 else 0 end as Repeated_customer from customer_orders as co join first_visit as fv on co.customer_id = fv.customer_id) select ca.customer_id, om.order_date, om.First_time_customer,om.Repeated_customer,ca.order_amount, case when om.First_time_customer = 1 then sum(ca.order_amount) else 0 end as firsttime_customer_order_amount, case when om.Repeated_customer = 1 then sum(ca.order_amount) else 0 end as Repeated_customer_order_amount from Order_amount as om join customer_orders as ca on ca.customer_id = om.customer_id group by ca.customer_id, ca.order_date order by ca.order_date;

can i also use window function

Please make video on thumb rules that are must to be followed

Hi ankit , added solution for assignment part with customer_first_ord_dt as ( select customer_id , min( order_date) as first_order_date from customer_orders group by 1 ) , final_temp_table as ( select * , case when order_date = first_order_date then 1 else 0 end as new_customer_count , case when order_date != first_order_date then 1 else 0 end as repeat_customer_count from customer_orders co inner join customer_first_ord_dt as cot on co.customer_id = cot.customer_id ) 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 , sum( case when new_customer_count = 1 then order_amount else 0 end) as new_customer_sales, sum( case when repeat_customer_count = 1 then order_amount else 0 end) as repeat_customer_sales from final_temp_table group by 1 order by 1

Using rank /row number this can be done in a. More smaller and efficient query?

Hey Ankit what do you think about my approach for this question >>>>> with cte as( select *, row_number() over(partition by customer_id order by order_date ) as rank from customer_orders ) select order_date, sum(case when rank = 1 then 1 else 0 end) as new_cus, sum(case when rank = 1 then order_amount else 0 end) as new_cus_revenue, sum(case when rank != 1 then 1 else 0 end) as old_cus, sum(case when rank != 1 then order_amount else 0 end) as old_cus_revenue from cte group by order_date Please let me know if this is good enough

if i want to know Repeat Customer vs New Customer Last year vs current year pls advise

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.

@ankit , Can you review my solution: with abc as (select customer_id,order_date, min(order_date) over(partition by customer_id) as first_purchase from customer_orders), new_cust as( select *, (case when order_date = first_purchase then 'new_customer' else 'old' end) as status from abc) select order_date,sum(case when status = 'new_customer' then 1 else 0 end) as new_cust, sum(case when status = 'old' then 1 else 0 end) as repeating_cust from new_cust group by 1