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

select * from customer_orders;

with cte as (select order_id,customer_id, order_date,order_amount, rank() over(partition by customer_id order by order_date) as visit_order
from customer_orders)
,
cte2 as(
select o.order_id, o.customer_id,o.order_date, o.order_amount,c.visit_order from
customer_orders o join cte c on o.order_id = c.order_id
order by order_date)

select order_date,
sum(case when visit_order = 1 then 1 else null end) as new_customer,
sum(case when visit_order >1 then 1 else null end) as repeated_customer
from cte2
group by order_date;

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

finished watching

with cte as(select *,min(order_date)over(partition by customer_id) as min_date
from customer_orders)
select order_date,
sum(case when order_date = min_date then 1 else 0 end) as new_customers,
sum(case when order_date != min_date then 1 else 0 end) as rep_customers
from cte
group by order_date ;

with table_2 as(
with table_1 as
(SELECT customer_id, min(order_date) as first_visit from customer_orders
GROUP BY customer_id)
SELECT a.order_date, b.first_visit from customer_orders a INNER JOIN
table_1 b on a.customer_id = b.customer_id
)
SELECT order_date, sum(CASE
WHEN order_date = first_visit THEN 1
ELSE 0
END) as first_visit_flag,
sum(CASE
WHEN order_date != first_visit THEN 1
ELSE 0
END) as Repeat_visit_flag
from table_2
GROUP BY order_date;

create table customer_orders (
order_id integer,
customer_id integer,
order_date date,
order_amount integer
);
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)
;

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 :)

df = df.withColumn('rn',expr('rank() over (partition by customer_id order by order_date )'))
df.filter(df.rn == 1).groupby('order_date').agg(count('order_date')).show()

-- finding new and repeat customers including total_amount for newcustomers and repeat customers
with first_order 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=fo.first_order_date then 1 else 0 end) as new_customer,
sum(case when co.order_date=fo.first_order_date then order_amount else 0 end) as new_cust_total,
sum(case when co.order_date!=fo.first_order_date then 1 else 0 end) as repeat_customer,
sum(case when co.order_date!=fo.first_order_date then order_amount else 0 end) as repeat_cust_total
from customer_orders co
join first_order fo on co.customer_id=fo.customer_id
group by co.order_date

Here is My Solution
with cte1 as(
select customer_id, min(order_date) as fir_dt from customer_orders group by customer_id)
,cte2 as (
select co.customer_id,co.order_date,co.order_amount,cte1.customer_id as cust_id,cte1.fir_dt,
case when co.order_date = cte1.fir_dt then 1 else 0 end as new_cust,
case when co.order_date <> cte1.fir_dt then 1 else 0 end as repeat_cust,
case when co.order_date = cte1.fir_dt then co.order_amount else 0 end as new_cust_amt,
case when co.order_date <> cte1.fir_dt then co.order_amount else 0 end as repeat_cust_amt
from customer_orders co join cte1 on co.customer_id = cte1.customer_id)
select order_date,sum(new_cust) as new_cust, sum(repeat_cust) as repeat_cust, sum(new_cust_amt) as new_cust_amt, sum(repeat_cust_amt) as repeat_cust_amt
from cte2 group by order_date order by 1;

select count(*) from(select customer_id,order_date,count(*) over (partition by customer_id order by order_id )as c
from Customer_order) as t1 where c<2 group by order_date this is only for new Customers

Hi Sir, Solution to the amt for old and new customer

with cte as (
select customer_id, order_date,order_amount,
case when (customer_id, order_date) in (select customer_id, min(order_date)
from customer_orders group by customer_id) then 1 else 0 end as new_flag,
case when (customer_id, order_date) in (select customer_id, min(order_date)
from customer_orders group by customer_id) then 0 else 1 end as rep_flag
from customer_orders )
select order_date,
sum(case when new_flag=1 then order_amount else 0 end) as new_amount,
sum(case when rep_flag = 1 then order_amount else 0 end) as repeat_amount
from cte
group by order_date;


order_date new_amt repeat_amount
2022-01-01 6600 0
2022-01-02 4900 2000
2022-01-03 3000 4000

with cte1 as(
select *,
row_number() over(partition by customer_id order by order_date) as rn
from customer_orders)
select order_date,
count(case when rn=1 then 1 else null end) as new_orders,
count(case when rn!=1 then 1 else null end) as repeat_orders
from cte1 group by order_date

Select order_date, sum(case when rn =1 then 1 else 0 end) as fv ,
sum(case when rn !=1 then 1 else 0 end) as rv
from(
select *,
count(1) over(partition by customer_id order by order_date) as rn
from customer_orders
)A
group by order_date

it is giving same result

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

with cte as(
select
customer_id,order_date,
FIRST_VALUE(order_date) over(PARTITION by customer_id order by order_date asc)fv from customer_orders)

select order_date,
SUM(case when fv=order_date then 1 else 0 end)new_customer,SUM(case when fv!=order_date
then 1 else 0 end)old_customer from cte group by order_date

with A as
(select customer_id,min(order_date) as first_order_date
from customer_orders
group by customer_id),
B as
(select order_date,sum(order_amount) as all_customers_amount
from customer_orders group by order_date),
C as (SELECT A.first_order_date,
sum(co.order_amount) as new_customers_amount
FROM A join customer_orders co
on (A.first_order_date = co.order_date and A.customer_id = co.customer_id)
group by first_order_date)
select order_date,all_customers_amount,new_customers_amount,(all_customers_amount - new_customers_amount) as old_customers_amount
from B join C on B.order_date = c.first_order_date

Really good channel and informative videos.

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

Another approch using row number


WITH cte1 AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn
FROM customer_orders
),
cte2 AS(
SELECT *,
CASE WHEN rn > 1 THEN 1 ELSE 0 END AS is_old,
CASE WHEN rn = 1 THEN 1 ELSE 0 END AS is_new
FROM cte1
)
SELECT order_date, sum(is_old) as old_customers, sum(is_new) as new_customers
FROM cte2
GROUP BY order_date
ORDER BY order_date