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 t1 (rep_id, place_id, sales_qty) AS (values
(0, 1, 3),
(1, 1, 1),
(1, 2, 2),
(1, 3, 4),
(1, 4, 1),
(2, 2, 1),
(2, 3, 3)
),
cte AS (
SELECT SUM(CASE WHEN rep_id = 1 THEN sales_qty ELSE 0 END) /
COUNT(CASE WHEN rep_id = 1 THEN 1 END) AS baseline
FROM t1
)
SELECT
rep_id,
COUNT(DISTINCT place_id) AS qty_places,
SUM(sales_qty) AS qty,
SUM(sales_qty) / COUNT(place_id) AS productivity,
t2.baseline,
(1.0*SUM(sales_qty) / COUNT(place_id)) / t2.baseline AS productivity_pct
FROM t1
CROSS JOIN cte t2
GROUP BY
t1.rep_id, t2.baseline;
| rep_id | qty_places | qty | productivity | baseline | productivity_pct |
|---|---|---|---|---|---|
| 0 | 1 | 3 | 3 | 2 | 1.5000000000000000 |
| 1 | 4 | 8 | 2 | 2 | 1.00000000000000000000 |
| 2 | 2 | 4 | 2 | 2 | 1.00000000000000000000 |