Write conditional aggregation SQL queries
Company: PayPal
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
##### 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.
Overview: 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.
Using the orders table below, write SQL to:
1) Compute the total amount of PAID orders using a simple SUM with a WHERE filter.
2) Rewrite that query using conditional aggregation with SUM(CASE WHEN ... THEN ... END) instead of the WHERE filter.
3) 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.
Tables
orders(order_id INTEGER, customer_id INTEGER, status VARCHAR(20), amount DECIMAL(10,2), order_date DATE)
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.