How to write SQL query with multiple conditions using CASE function

The table The task is: Count the number of customers who simultaneously:

have more than 5 payments that exeed 5000 dollars
and have an average payment value of more than 10,000 dollars

I have done it using window function and subquery:

CREATE TABLE Customers (
client INT,
payment INT);

INSERT INTO Customers(client, payment) VALUES
(1, 1000),
(1, 7000),
(1, 6000),
(1, 50000),
(1, 5500),
(1, 5600),
(2, 1000),
(2, 1000);

select client, count(payment) from
(select *, avg(payment) over(partition by client) as avg_payment from Customers) as t1
where payment > 5000
group by client
having count(payment)>5

But I have to make it without window function and subquery. I've been told it is possible to do it only with the use of CASE function. I'll be happy if someone could help me optimize my query.

Solution

TLDR: Working fiddle here

Let's break the query down into pieces:

Find customers who have more than 5 payments that exceed 5000 dollars

You can query for payments more then $5,000 in your WHERE clause, and then specify the "more than 5 payments" in your HAVING clause (after aggregating by Client ID):

SELECT 
  client, 
  COUNT(*) AS payment_gt_5000
FROM customers
WHERE payment > 5000
GROUP BY client
HAVING COUNT(*) >= 5

(note that I changed >5 to >=5, since Client ID 1 has exactly 5 matching payments).

Then if we wanted to capture "average payment value of more than 10,000 dollars", we'd use a very similar query:

SELECT 
  client, 
  AVG(payment)
FROM customers
GROUP BY client
HAVING AVG(payment) > 10000

Since these 2 queries are very similar, we should be able to combine them. The only tricky part is we have to get rid of the payment > 5000 from the WHERE clause, since we want to calculate averages for all payments. But wait…it's a bird! It's a plane! It's conditional aggregation to the rescue:

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

We're not applying the payment > 5000 to the WHERE clause, so we're getting the average for all payments like we want. But we're still getting the count of payments > 5000 (COUNT(CASE WHEN payment > 5000 THEN 1 END)), so we can still figure out in the HAVING clause which clients have 5+ payments of more than $5,000.