Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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