Counts of re-purchase same product by the same customer in certain days or within certain duration days

I need to have the counts of re-purchase same product by the same customer in tow scenarios: in certain days and within certain duration.

Here is the sample data:

create table tbl
(
  Customer  varchar(5),
  StartDay date,
  EndDay date,
  Product  varchar(5),
  Cost  decimal(10,2)
);

insert into tbl values 
('A',       '1/1/2019',      '1/4/2019',      'Shoe',        10.00),
('B',       '2/4/2021',      '2/7/2021',      'Hat',          10.00),
('A',       '1/7/2019',      '1/8/2019',      'Shoe',        10.00),
('B',       '5/8/2018',      '5/9/2018',      'Shoe',         10.00),
('A',       '2/1/2019',      '2/3/2019',      'Shoe',        10.00),
('C',       '6/6/2020',      '6/6/2020',      'Hat',         10.00),
('C',       '11/9/2021',     '12/9/2021',     'Cloth',       10.00),
('A',       '3/3/2019',      '3/17/2019',     'Cloth',        10.00),
('C',       '7/8/2020',      '7/12/2020',      'Hat',         10.00),
('E',       '7/2/2020',      '9/1/2020',      'Hat',        10.00),
('A',       '3/3/2019',      '3/7/2019',      'Shoe',        10.00),
('A',       '7/5/2022',      '7/9/2022',      'Hat',       10.00),
('C',       '6/6/2020',      '6/8/2020',      'Shoe',        10.00),
('B',       '8/2/2018',      '8/9/2018',      'Shoe',         10.00),
('A',       '1/1/2019',      '1/11/2019',     'Cloth',        10.00),
('E',       '9/3/2020',      '10/1/2020',      'Hat',        10.00),
('E',       '7/2/2020',      '7/8/2020',      'Shoe',       10.00);

Duration is the difference between the last EndDay and the next StartDay for same customer purchasing same product.

For example:

For customer A, the EndDay at the first time he purchased “Shoe” was ‘1/4/2019. And the StartDay at the second time he purchased the ‘shoe’ was ‘1/7/2019’. So the duration was within 30 days.

For customer B, the EndDay at the first time he purchased “Shoe” was ‘5/8/2018. And the StartDay at the second time he purchased the ‘shoe’ was ‘8/2/201’. So the duration was within 60 – 90 days.

Expected outcome for first scenario:

Expected outcome for second scenario:

Thank you very much for your help in advance!!

Answer

Scenario 1:

select  Customer    
       ,StartDay    
       ,EndDay  
       ,Product 
       ,Cost
       ,[0]                  as '0-30 days'
       ,[0] + [1]            as '0-60 days'
       ,[0] + [1] + [2]      as '0-90 days'
       ,[0] + [1] + [2]+ [3] as 'total'
from
(
select  t.Customer
       ,t.StartDay
       ,t.EndDay
       ,t.Product
       ,t.Cost
       ,case when (datediff(day, t.EndDay, t2.StartDay)-1)/30 > 3 then 3 else (datediff(day, t.EndDay, t2.StartDay)-1)/30 end as dd
from    t left join t t2 on  t2.Customer = t.Customer 
                         and t2.Product = t.Product
                         and t2.StartDay > t.EndDay
) t
pivot(count(dd) for dd in([0],[1],[2],[3])) p
order by Customer, Product, StartDay

Customer	StartDay	EndDay	Product	Cost	0-30 days	0-60 days	0-90 days	total
A	2019-01-01	2019-01-11	Cloth	10.00	0	1	1	1
A	2019-03-03	2019-03-17	Cloth	10.00	0	0	0	0
A	2022-07-05	2022-07-09	Hat	10.00	0	0	0	0
A	2019-01-01	2019-01-04	Shoe	10.00	2	3	3	3
A	2019-01-07	2019-01-08	Shoe	10.00	1	2	2	2
A	2019-02-01	2019-02-03	Shoe	10.00	1	1	1	1
A	2019-03-03	2019-03-07	Shoe	10.00	0	0	0	0
B	2021-02-04	2021-02-07	Hat	10.00	0	0	0	0
B	2018-05-08	2018-05-09	Shoe	10.00	0	0	1	1
B	2018-08-02	2018-08-09	Shoe	10.00	0	0	0	0
C	2021-11-09	2021-12-09	Cloth	10.00	0	0	0	0
C	2020-06-06	2020-06-06	Hat	10.00	0	1	1	1
C	2020-07-08	2020-07-12	Hat	10.00	0	0	0	0
C	2020-06-06	2020-06-08	Shoe	10.00	0	0	0	0
E	2020-07-02	2020-09-01	Hat	10.00	1	1	1	1
E	2020-09-03	2020-10-01	Hat	10.00	0	0	0	0
E	2020-07-02	2020-07-08	Shoe	10.00	0	0	0	0

Fiddle