Servicing teams track how long since each customer last paid. Using 2026-06-30 as "today": return customerid, their…
SQLLevel: IntermediateServicing and risk analytics
Servicing teams track how long since each customer last paid. Using 2026-06-30 as "today": return customer_id, their latest payment_date, and days_since for customers whose most recent payment is MORE than 45 days old. Days-since-last-activity reports are bread and butter in servicing analytics.
Table definitions (from the source exercise):
CREATE TABLE customers(customer_id INTEGER PRIMARY KEY, name TEXT, city TEXT, signup_date TEXT, credit_score INTEGER);
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: Group payments by customer, take MAX(payment_date), compute DATEDIFF against 2026-06-30, filter in HAVING. A customer at exactly 45 days does not qualify.
SELECT customer_id,
MAX(payment_date) AS last_payment,
DATEDIFF('2026-06-30', MAX(payment_date)) AS days_since
FROM payments
GROUP BY customer_id
HAVING DATEDIFF('2026-06-30', MAX(payment_date)) > 45;
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