For each month (YYYY-MM) of payments, return the percent of payment DOLLARS that came in as cash, rounded to 1 decimal…
SQLLevel: IntermediateServicing and risk analytics
For each month (YYYY-MM) of payments, return the percent of payment DOLLARS that came in as cash, rounded to 1 decimal (cash_pct), in chronological order. Payment-mix shifts are a servicing health indicator.
Table definitions (from the source exercise):
CREATE TABLE payments(payment_id INTEGER PRIMARY KEY, customer_id INTEGER, payment_date TEXT, amount REAL, method TEXT);
Hints
- Define one output row and the source-table keys.
- Check duplicates, NULLs and the order of filtering and aggregation.
Answer
Approach: Conditional aggregation inside a percent: SUM(CASE WHEN method = cash THEN amount ELSE 0 END) / SUM(amount), grouped by DATE_FORMAT month.
SELECT DATE_FORMAT(payment_date, '%Y-%m') AS pay_month,
ROUND(100.0 * SUM(CASE WHEN method = 'cash' THEN amount ELSE 0 END)
/ SUM(amount), 1) AS cash_pct
FROM payments
GROUP BY pay_month
ORDER BY pay_month;
The source uses SQLite with a MySQL compatibility layer. Date and string functions are dialect dependent; the query is preserved as published.
Source and licence: SQL Practice Lab — Ebrahim Sharifi · MIT