Coding questions · PayPal
Write conditional aggregation SQL queries
This question evaluates proficiency with conditional aggregation in SQL, including the use of SUM(CASE WHEN ...) versus filtering with WHERE and the implications of placing aggregation filters in HAVING for grouped queries, within the Data Manipulation (SQL/Python) domain.
Question
Write an SQL query to compute the total amount for rows satisfying a condition, comparing approaches that use SUM(CASE WHEN … THEN … END) versus a simple SUM with a WHERE filter. Rewrite the query from Question 1 using CASE WHEN for conditional aggregation. Given a grouped query, place an aggregation filter in the HAVING clause; explain whether the syntax works in MySQL and why.
Conditional Aggregation and HAVING with Aliases in MySQL
Using the orders table below, write SQL to:
- Compute the total amount of PAID orders using a simple SUM with a WHERE filter.
- Rewrite that query using conditional aggregation with SUM(CASE WHEN ... THEN ... END) instead of the WHERE filter.
- Group the results by customer_id to find, for each customer, the total PAID amount, and then return only customers whose total PAID amount is at least 200 using a HAVING clause. Explain whether using the aggregate column alias in the HAVING clause (e.g., HAVING total_paid_amount >= 200) works in MySQL and why.
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INTEGER | PRIMARY KEY, NOT NULL |
| customer_id | INTEGER | NOT NULL |
| status | VARCHAR(20) | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
| order_date | DATE | NOT NULL |
Sample data:
| amount | customer_id | order_date | order_id | status |
|---|---|---|---|---|
| 120 | 101 | 2024-01-10 | 1 | PAID |
| 50 | 101 | 2024-01-13 | 2 | PENDING |
| 80.5 | 101 | 2024-02-05 | 3 | PAID |
| 200 | 102 | 2024-02-10 | 4 | CANCELLED |
| 60 | 102 | 2024-03-01 | 5 | PAID |
| 300 | 103 | 2024-03-07 | 6 | PAID |
| 20 | 103 | 2024-03-15 | 7 | PENDING |
| 20 | 103 | 2024-04-02 | 8 | PAID |
Expected output
Using either a WHERE filter on status = 'PAID' or conditional aggregation with SUM(CASE WHEN status = 'PAID' THEN amount ELSE 0 END), the overall total for PAID orders is 580.50. When grouping by customer_id and filtering to customers whose total PAID amount is at least 200 via HAVING, the result contains customer_id 101 with a total_paid_amount of 200.50 and customer_id 103 with a total_paid_amount of 320.00. MySQL allows using the select-list alias (total_paid_amount) directly in the HAVING clause, so HAVING total_paid_amount >= 200 is valid. In standard SQL or some other databases, you may need to repeat the aggregate expression instead of using the alias.
| customer_id | total_paid_amount |
|---|---|
| 101 | 200.5 |
| 103 | 320 |
Hints
- Use WHERE status = 'PAID' to filter rows before aggregation when computing the total of PAID orders.
- For conditional aggregation, use SUM(CASE WHEN status = 'PAID' THEN amount ELSE 0 END) so non-PAID rows contribute 0.
- To filter on an aggregate per group, use HAVING, not WHERE, because WHERE cannot contain aggregate functions.
- MySQL allows referring to a SELECT-list alias in HAVING; in more strict SQL dialects you may need to repeat the aggregate expression instead of using the alias.