Investigate negative amounts and quantify refund impact
Finance flagged that some purchase amounts are negative. I need a full picture: total number of refunds, total refund amount, total positive purchases, total positive revenue, and the refund rate as a percentage of positive revenue. One row, five columns.
Each hint you reveal reduces the XP you can earn. Try the query first.
Use CASE inside aggregate functions to split purchases and refunds in one query: `SUM(CASE WHEN amount < 0 THEN 1 ELSE 0 END) AS refund_count`
For refund total: `ROUND(ABS(SUM(CASE WHEN amount < 0 THEN amount ELSE 0 END))::numeric, 2) AS refund_total` — use ABS() since refunds are negative
Refund rate: `ROUND(100.0 * ABS(SUM(CASE WHEN amount < 0 THEN amount ELSE 0 END)) / SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END), 2) AS refund_rate`