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?.
WITH product_table AS (
SELECT 'Prod A' AS product, '2019-01-01'::date AS order_date, 100 AS units_sold UNION ALL
SELECT 'Prod A', '2019-01-02'::date, 200 UNION ALL
SELECT 'Prod A', '2019-01-03'::date, 300 UNION ALL
SELECT 'Prod A', '2019-01-04'::date, 100 UNION ALL
SELECT 'Prod A', '2019-01-05'::date, 200 UNION ALL
SELECT 'Prod A', '2019-01-06'::date, 300 UNION ALL
SELECT 'Prod A', '2019-01-07'::date, 200 UNION ALL
SELECT 'Prod A', '2019-01-08'::date, 200
),
cte1 AS (
SELECT *, LAG(units_sold, 1, 0) OVER (PARTITION BY product ORDER BY order_date) AS prev_units_sold,
units_sold - LAG(units_sold, 1, 0) OVER (PARTITION BY product ORDER BY order_date) diff
FROM product_table
),
cte2 AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY product ORDER BY order_date) rn1,
ROW_NUMBER() OVER (PARTITION BY product, diff ORDER BY order_date) rn2
FROM cte1
)
SELECT product, order_date, units_sold, prev_units_sold, diff,
CASE WHEN diff > 0
THEN ROW_NUMBER() OVER (PARTITION BY product, rn1 - rn2 ORDER BY order_date)
ELSE 0 END AS streak,
CASE WHEN diff > 0
THEN AVG(units_sold) OVER (PARTITION BY product, rn1 - rn2 ORDER BY order_date)
ELSE 0 END AS running_avg
FROM cte2
ORDER BY product, order_date;
| product | order_date | units_sold | prev_units_sold | diff | streak | running_avg |
|---|---|---|---|---|---|---|
| Prod A | 2019-01-01 | 100 | 0 | 100 | 1 | 100.0000000000000000 |
| Prod A | 2019-01-02 | 200 | 100 | 100 | 2 | 150.0000000000000000 |
| Prod A | 2019-01-03 | 300 | 200 | 100 | 3 | 200.0000000000000000 |
| Prod A | 2019-01-04 | 100 | 300 | -200 | 0 | 0 |
| Prod A | 2019-01-05 | 200 | 100 | 100 | 1 | 200.0000000000000000 |
| Prod A | 2019-01-06 | 300 | 200 | 100 | 2 | 250.0000000000000000 |
| Prod A | 2019-01-07 | 200 | 300 | -100 | 0 | 0 |
| Prod A | 2019-01-08 | 200 | 200 | 0 | 0 | 0 |