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!!

CodePudding user response：

select  Customer    
       ,StartDay    
       ,EndDay  
       ,Product 
       ,Cost
       ,[0] as '0-30 days'
       ,[1] as '30-60 days'
       ,[2] as '60-90 days'
       ,[3] as '90  days'
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

Fiddle