I have below table called orders which has customer id and their order date (Note: there can be multiple orders from same customer on a single day)
create table orders (Id char, order_dt date)
insert into orders values
('A','1/1/2020'),
('B','1/1/2020'),
('C','1/1/2020'),
('D','1/1/2020'),
('A','1/1/2020'),
('B','1/1/2020'),
('A','2/1/2020'),
('B','2/1/2020'),
('C','2/1/2020'),
('B','2/1/2020'),
('A','3/1/2020'),
('B','3/1/2020')
I'm trying to write an SQL query to find the number of customers who shopped for 3 consecutive days in month of January 2020
Based on above order values, the output should be: 2
I referred other similar questions but still wasn't able to come the exact solution
Here is my solution which works fine even there are many orders of one customer in one day;
Some scripts to build test environment:
create table orders (Id varchar2(1), order_dt date);
insert into orders values('A',to_date('01/01/2020','dd/mm/yyyy'));
insert into orders values('B',to_date('01/01/2020','dd/mm/yyyy'));
insert into orders values('C',to_date('01/01/2020','dd/mm/yyyy'));
insert into orders values('D',to_date('01/01/2020','dd/mm/yyyy'));
insert into orders values('A',to_date('01/01/2020','dd/mm/yyyy'));
insert into orders values('B',to_date('01/01/2020','dd/mm/yyyy'));
insert into orders values('A',to_date('02/01/2020','dd/mm/yyyy'));
insert into orders values('B',to_date('02/01/2020','dd/mm/yyyy'));
insert into orders values('C',to_date('02/01/2020','dd/mm/yyyy'));
insert into orders values('B',to_date('02/01/2020','dd/mm/yyyy'));
insert into orders values('A',to_date('03/01/2020','dd/mm/yyyy'));
insert into orders values('B',to_date('03/01/2020','dd/mm/yyyy'));
select distinct id, count_days from (
select id,
order_dt,
count(*) over(partition by id order by order_dt range between 1 preceding and 1 following ) count_days
from orders group by id, order_dt
)
where count_days = 3;
-- Insert for test more days than 3 consecutive
insert into orders values('A',to_date('04/01/2020','dd/mm/yyyy'));