How to write SQL query with multiple conditions using CASE function

Question

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

1. have more than 5 payments that exeed 5000 dollars
2. 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.

Answer 1

TLDR: Working fiddle here

Let's break the query down into pieces:

1. 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.

Answer 2

You can get rid of the subquery by placing the aggregation directly in the having clause:

select client
from Customers
group by client
having count(*) filter(where payment > 5000) > 5
   and avg(payment) > 10000

^{(online demo)}

_{I prefer count(*) over count(payment) since the latter does not count rows with a NULL value, though it doesn't matter here due to the > 5000 condition.}

Now instead of using filter, you can use a sum that conditionally counts either 1 or 0 per row, and use a CASE statement for that:

…
having sum(case when payment > 5000 then 1 else 0 end) > 5

or

…
having count(case when payment > 5000 then 1 /* else null */ end) > 5

or

…
having sum((payment > 5000)::int) > 5

though using filter is much more elegant and straightforward. See also postgresql - sql - count of `true` values.

Answer 3

Technically, based on the question ‘Count the number of customers....’ there isn’t a way of doing this using a single select statement without a join.

It would require either a window function or CTE or subquery to return the aggregation. This is because you cannot run two different groupings within the same selection without a window function (which is required for the initial average and then to count the client IDs)