Overdue Invoices Query
Problem
You have two tables from a billing system:
customers(id, name, country)invoices(id, customer_id, amount, due_date, paid_at)—paid_atis NULL while unpaid
Write a query that, as of 2026-01-31, returns each customer who has at least two overdue invoices, together with the count of overdue invoices and their total amount. An invoice is overdue when it is unpaid and its due date is before the reference date. Order by total amount descending, then customer name.
Examples
Example 1
Customer "Delta Foods" has invoices due 2025-12-01 (unpaid, 400), 2026-01-10 (unpaid, 700), 2026-02-15 (unpaid, 900). Only the first two are overdue → row Delta Foods, 2, 1100.
Example 2 Customer "Nile Cargo" has invoices due 2025-11-01 (paid on 2025-11-20, 500) and 2026-01-05 (unpaid, 300). Only one overdue → not returned at all.
Constraints
- Standard SQL; you can assume PostgreSQL or SQLite semantics
- Customers with no invoices must not appear
- Do not use a subquery where a
HAVINGclause suffices
What they look for
Correct NULL handling (paid_at IS NULL, not = NULL), the join, GROUP BY with HAVING COUNT(*) >= 2, and then a couple of follow-ups about indexes and how the plan changes when the table has 100M rows.