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

This video is about finding new and repeat customers .using SQL. In this video we will learn following concepts:
how to approach complex query step by step
how to use CASE WHEN with SUM
how to use common table expression (CTE)

Here is the script :
create table customer_orders (
order_id integer,
customer_id integer,
order_date date,
order_amount integer
);
select * from customer_orders
insert into customer_orders values(1,100,cast('2022-01-01' as date),2000),(2,200,cast('2022-01-01' as date),2500),(3,300,cast('2022-01-01' as date),2100)
,(4,100,cast('2022-01-02' as date),2000),(5,400,cast('2022-01-02' as date),2200),(6,500,cast('2022-01-02' as date),2700)
,(7,100,cast('2022-01-03' as date),3000),(8,400,cast('2022-01-03' as date),1000),(9,600,cast('2022-01-03' as date),3000)
;

[satyamgoyal]

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;

[nidhisingh]

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

[shilpajaiswal]

with a as (select order_date, count(*) as all_cust from customer_orders group by order_date),

b as (select min_1, count(customer_id) as new_cust from (
select customer_id, min(order_date) as min_1 from customer_orders group by customer_id)
group by min_1)

select a.order_date, b.new_cust, (a.all_cust-b.new_cust) as repeat_cust from a join b on
a.order_date=b.min_1

[anjulaxmipillai]

select order_date, sum(case when rnk>1 then 1 else 0 end) as repeat_customer, (count(*)-sum(case when rnk>1 then 1 else 0 end)) as new_customer
from (
select *, row_number()over(partition by customer_id order by order_date )rnk from customer_orders)a
group by order_date

[gauravmalik]

Alternate ans :

with ct as (select *, row_number() over(partition by customer_id order by order_date) c1
from co)
select order_date, sum(case when c1 = 1 then 1 else 0 end ) new_customers, sum(case when c1 != 1 then 1 else 0 end ) repeated_customers
from ct
group by order_date

[praveensundarsundar]

Someone confirm whether this logic works?
First we find count of new customers everyday and then find active customers count similarly. Now by subtracting them we'll get the repeat or continuing customers.

[mananyadav]

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 Youtube. 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. you so much for all your hardwork and knowledge sharing.

[devendrabhumarapu]

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
from cte
group by order_date;

select * from customer_orders;

[kothapalliramana]

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;

[ankitbiswas]

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 .

[bhajans-ml]

with cte as (
select *,
row_number () over (partition by customer_id order by order_date ) as rn

from customer_orders),

cte1 as
(select order_date, count(1) as new_cust from cte
where rn=1 group by order_date),

cte2 as (
select order_date, count(1) as rep_cust from cte
where rn>1 group by order_date)


select a.order_date, new_cust, case when rep_cust is null then 0 else rep_cust end as rep_cust from cte1 a left join cte2 b on a.order_date=b.order_date

[BakhriddinIndustry]

SELECT order_date,
SUM(CASE WHEN rn = 1 THEN 1 ELSE 0 END) first_customer,
SUM(CASE WHEN rn > 1 THEN 1 ELSE 0 END) repeated_customer
FROM (
SELECT customer_id,
order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn
FROM customer_orders
) t
GROUP BY order_date

[yadavikasAnalyst]

SIR I HAVE A DOUBT WHAT IF A NEW CUSTOMER PURCHASES TWO DIFFERENT PRODUCT ON SAME DAY SO AT THE END WHEN WE WILL AGGREGATE THE DATA ONE NEW CUSTOMER WILL BE COUNTED MORE THAN FOR EXAMPLE IN THE DATA SET CUSTOMER ID 600 PURCHASE A DIFFERENT PRODUCT OF PRODUCT ID 10 AT 2022-01-03 ??? THEN HOW TO SOLVE THIS ONE ??

[keerthanakalyanapu]

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;

[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

[yadavikasAnalyst]

with cte as
(
select c.customer_id, t.first_visit_date, c.order_date, c.order_amount
FROM
(
select customer_id, min(order_date) as first_visit_date from customer_orders
group by 1) t inner join
customer_orders c on c.customer_id=t.customer_id
)
select order_date, -- customer_id,
sum(CASE WHEN order_date=first_visit_date THEN 1 else 0 end) as new_customer,
sum(case when order_date<> first_visit_date then 1 else 0 end) as repeat_customer,
sum(CASE WHEN order_date=first_visit_date THEN order_amount else 0 end) as new_customer_revenue,
sum(case when order_date<> first_visit_date then order_amount else 0 end) as repeat_customer_revenue
from cte
group by 1
order by 1;


soluntion of the

[varunas]

Good one as always!

Here's my approach:



with cust_count as (select *,
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

[swethathiruppathy]

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
;

[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

[rishabhralli]

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