Write conditional aggregates with CASE WHEN
Company: PayPal
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Onsite
Write a query that produces conditional aggregates using CASE WHEN (e.g., counts of approved vs declined transactions per merchant and the sum of amounts flagged for review). Explain why CASE WHEN is the portable approach across SQL dialects compared with dialect-specific boolean-to-integer coercion (e.g., SUM(column = 'x')). Discuss readability and maintainability trade-offs.
Quick Answer: Write conditional aggregates with CASE WHEN evaluates SQL or pandas logic, joins, grouping, window functions, null handling, edge cases, and validation in a realistic interview setting. A strong answer states assumptions, handles edge cases, explains trade-offs, and shows how to validate the result clearly.
Solution
# Solution Alignment
Use `CASE WHEN` inside aggregate functions so the query is portable across SQL engines.
```sql
SELECT
merchant_id,
COUNT(*) AS total_transactions,
SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) AS approved_count,
SUM(CASE WHEN status = 'declined' THEN 1 ELSE 0 END) AS declined_count,
SUM(CASE WHEN review_flag = TRUE THEN amount ELSE 0 END) AS review_amount
FROM transactions
GROUP BY merchant_id;
```
`CASE WHEN` is clearer than relying on boolean-to-integer coercion such as `SUM(status = 'approved')`, which works in some dialects but not all. Keep conditions in named expressions or CTEs when business logic is complex.