I have a table which stores when customers registered and when they made their purchases respectively.

select * from customers_table

cust_id      cust_reg_date     cust_purchase_date  purchase_made
   1           1/10/21              1/28/21           laptop
   1           1/10/21              1/31/21            car
   1           1/10/21              2/9/21            shirt
   2           2/5/21               2/26/21           pajamas
   2           2/5/21               3/5/21            phone
   2           2/5/21               4/25/21           laptop
   3           2/15/21              4/10/21           dirt bike
   3           2/15/21              5/10/21           towel
   3           2/15/21              6/1/21            pen

I got this snippet which records what week starting from the cust_reg_date did the customers make the purchase!

select *, case when cust_purchase_date between cust_reg_date   1 and cust_reg_date   10 then 'Week 1 Purchase' 
               when cust_purchase_date between cust_reg_date   11 and cust_reg_date   20 then 'Week 2 Purchase' 
               when cust_purchase_date between cust_reg_date   21 and cust_reg_date   30 then 'Week 3 Purchase'
               .... end as week_purchase

cust_id      cust_reg_date     cust_purchase_date  purchase_made      week_purchase
   1           1/10/21              1/28/21           laptop         Week 2 Purchase        
   1           1/10/21              1/31/21            car           Week 3 Purchase
   1           1/10/21              2/26/21            shirt         Week 5 Purchase
   2           2/5/21               2/21/21           pajamas        Week 2 Purchase
   2           2/5/21               3/5/21            phone          Week 3 Purchase
   2           2/5/21               4/25/21           laptop         Week 5 Purchase
   3           3/15/21              4/10/21           dirt bike      Week 3 Purchase
   3           3/15/21              5/10/21           towel          Week 6 Purchase ....

How can I show the missing weeks in which NO purchases were made like below:

cust_id      cust_reg_date     cust_purchase_date  purchase_made      week_purchase
   1           1/10/21                 -                -            Week 1 Purchase  <--- Requested   
   1           1/10/21              1/28/21           laptop         Week 2 Purchase        
   1           1/10/21              1/31/21            car           Week 3 Purchase
   1           1/10/21                 -                -            Week 4 Purchase  <--- Requested 
   1           1/10/21              2/26/21           shirt          Week 5 Purchase
   2           2/5/21                  -                -            Week 1 Purchase  <--- Requested  
   2           2/5/21               2/21/21           pajamas        Week 2 Purchase
   2           2/5/21               3/5/21            phone          Week 3 Purchase
   2           2/5/21                  -                -            Week 4 Purchase  <--- Requested  
   2           2/5/21               4/25/21           laptop         Week 5 Purchase
   3           3/15/21                 -                -            Week 1 Purchase  <--- Requested  
   3           3/15/21                 -                -            Week 2 Purchase  <--- Requested  
   3           3/15/21              4/10/21           dirt bike      Week 3 Purchase
   3           3/15/21                 -                -            Week 4 Purchase  <--- Requested  
   3           3/15/21                 -                -            Week 5 Purchase  <--- Requested  
   3           3/15/21              5/10/21           towel          Week 6 Purchase

CodePudding user response：

select * from
(
select *, case when cust_purchase_date between cust_reg_date   1 and cust_reg_date   10 then 'Week 1 Purchase' 
               when cust_purchase_date between cust_reg_date   11 and cust_reg_date   20 then 'Week 2 Purchase' 
               when cust_purchase_date between cust_reg_date   21 and cust_reg_date   30 then 'Week 3 Purchase'
               when cust_purchase_date between cust_reg_date   31 and cust_reg_date   40 then 'Week 4 Purchase'
               when cust_purchase_date between cust_reg_date   41 and cust_reg_date   50 then 'Week 5 Purchase'
               when cust_purchase_date between cust_reg_date   51 and cust_reg_date   60 then 'Week 6 Purchase'
               end as week_purchase

from customers_table
)x
where x.week_purchase is null

CodePudding user response：

First I use a recursive query to generate all weeks (from week 1 to week 10). Then I make a CROSS JOIN to get each customer with all generate weeks. Finally I use LEFT JOIN to join all_weeks_by_cust subquery with your customers_table table:

WITH RECURSIVE weeks(start, finish, week) AS (
                        SELECT 1, 10, 1

                        UNION ALL
                               
                        SELECT finish   1, finish   10, week   1
                        FROM weeks
                        WHERE week < 10),
                        
   all_cust AS (SELECT cust_id, MIN(cust_reg_date) AS cust_reg_date
                 FROM customers_table 
                 GROUP BY cust_id),
                        
   all_weeks_by_cust AS (SELECT *
                         FROM weeks
                         CROSS JOIN all_cust)              

SELECT cw.cust_id, cw.cust_reg_date, ct.cust_purchase_date, ct.purchase_made, 
       'Week ' || cw.week || ' Purchase' AS week_purchase
FROM all_weeks_by_cust cw
LEFT JOIN customers_table ct ON cw.cust_id = ct.cust_id AND ct.cust_purchase_date BETWEEN (ct.cust_reg_date   cw.start * INTERVAL '1 day') AND (ct.cust_reg_date   cw.finish * INTERVAL '1 day')
ORDER BY cw.cust_id, cw.week;

Output for customer 1 with example data from the question:

You need to generate as many weeks as the biggest difference between a customer's registration and their last purchase. In my example I used 10 weeks, so the last purchase of customer 3 is out of the report, because he made this purchase on week 11.