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 Customers (
client INT,
payment INT);
CREATE TABLE
INSERT INTO Customers(client, payment) VALUES
(1, 1000),
(1, 7000),
(1, 6000),
(1, 50000),
(1, 5500),
(1, 5600),
(2, 1000),
(2, 1000);
INSERT 0 8
/* which clients have 5+ payments at > 5000 */
SELECT
client,
COUNT(*) AS payment_gt_5000
FROM customers
WHERE payment > 5000
GROUP BY client
HAVING COUNT(*) >= 5
| client | payment_gt_5000 |
|---|---|
| 1 | 5 |
SELECT 1
/* which clients have an average payment > 10000 */
SELECT
client,
AVG(payment)
FROM customers
GROUP BY client
HAVING AVG(payment) > 10000
| client | avg |
|---|---|
| 1 | 12516.666666666667 |
SELECT 1
/* which clients fulfill both criteria */
SELECT
client,
COUNT(CASE WHEN payment > 5000 THEN 1 END) AS payment_gt_5000,
AVG(payment) AS avg_payment
FROM customers
GROUP BY client
HAVING
COUNT(CASE WHEN payment > 5000 THEN 1 END) >= 5
AND AVG(payment) > 10000
| client | payment_gt_5000 | avg_payment |
|---|---|---|
| 1 | 5 | 12516.666666666667 |
SELECT 1