REPEAT CUSTOMER & NEW CUSTOMER -SQL Interview Question -

D ay-2:

Question-2:

Date-5th Nov 2022

New and repeat customers Count from a customer table.

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)

select * from customer_orders

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;