Skip to content
Quantoros

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

Related questions