Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE

INSERT 0 6

/* use generate_series to generate a series of dates
   starting from the min date of datetime up to the 
   max datetime with one-month intervals, then do a 
   cross join with the distinct cust_id to map each cust_id 
   to each generated date.*/
WITH cust_dates AS
(
  SELECT EX.cust_id, to_char(dts, 'YYYY-mm') dts
  FROM generate_series 
        (
         (SELECT MIN(datetime)::timestamp FROM example), 
         (SELECT MAX(datetime)::timestamp + '2 month'::interval  FROM example),
         '1 month'::interval
        ) dts
  CROSS JOIN (SELECT DISTINCT cust_id FROM example) EX
),
/* do a left join with your table to find prices
   for each cust_id/ month, and aggregate for cust_id, month_date
   to find the sum of prices for each cust_id, month_date.
*/
monthly_price AS
(
  SELECT CD.cust_id,
       CD.dts AS month_date,
       COALESCE(SUM(price), 0) total_price
  FROM cust_dates CD LEFT JOIN example EX
  ON CD.cust_id = EX.cust_id AND 
     CD.dts = to_char(EX.datetime, 'YYYY-mm')
  GROUP BY CD.cust_id, CD.dts
)
/* Now, we have the sum of monthly prices for each cust_id,
   we can use the max window function with "ROWS BETWEEN 2 PRECEDING AND CURRENT ROW"
   to check if one of the (current month or the previous two months) has a sum of prices > 0.
*/
SELECT cust_id, month_date, total_price,
  CASE MAX(total_price) OVER 
      (PARTITION BY cust_id ORDER BY month_date 
       ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
    WHEN 0 THEN 'inactive' 
    ELSE 'active'
  END AS is_active
FROM monthly_price
ORDER BY cust_id, month_date

cust_id	month_date	total_price	is_active
1	2022-01	3500	active
1	2022-02	0	active
1	2022-03	0	active
1	2022-04	0	inactive
1	2022-05	0	inactive
1	2022-06	300	active
1	2022-07	0	active
2	2022-01	0	inactive
2	2022-02	200	active
2	2022-03	0	active
2	2022-04	250	active
2	2022-05	0	active
2	2022-06	0	active
2	2022-07	0	inactive
3	2022-01	0	inactive
3	2022-02	0	inactive
3	2022-03	1600	active
3	2022-04	0	active
3	2022-05	0	active
3	2022-06	0	inactive
3	2022-07	0	inactive

SELECT 21