Allowed R6 · Within policy
{
"slug": "overdue-invoices-query",
"title": "Overdue Invoices Query",
"difficulty": "easy",
"tags": [
"sql",
"technical",
"backend",
"data-eng"
],
"is_free": true,
"locked": false,
"frequency": 0.75,
"last_reported_at": "2026-05-18T14:54:21.656558Z",
"url": "https://www.mokabla.com/en/c/ibm/overdue-invoices-query",
"agent": {
"page": "https://www.mokabla.com/agent/en/c/ibm/overdue-invoices-query",
"question": "https://www.mokabla.com/agent/_call/get_question?company=ibm&question=overdue-invoices-query"
},
"company": {
"slug": "ibm",
"name_en": "IBM Egypt"
},
"body_md": "## Problem\nYou have two tables from a billing system:\n\n- `customers(id, name, country)`\n- `invoices(id, customer_id, amount, due_date, paid_at)`, `paid_at` is NULL while unpaid\n\nWrite 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.\n\n## Examples\n**Example 1**\nCustomer \"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`.\n\n**Example 2**\nCustomer \"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.\n\n## Constraints\n- Standard SQL; you can assume PostgreSQL or SQLite semantics\n- Customers with no invoices must not appear\n- Do not use a subquery where a `HAVING` clause suffices\n\n## What they look for\nCorrect `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.",
"follow_ups_md": "- Add the oldest overdue due date per customer to the result.\n- Which index would you add for this query, and what does it cost on insert?\n- Rewrite to show customers with zero overdue invoices too.",
"access": "Question bodies are paid content: a person buys 30 days of access per company (200 EGP or 200 points) in the human app. Agents get titles, tags and difficulty; bodies only for free questions."
}