Zomato: Hyperpure Invoices Paid Late or Still Overdue
Hyperpure supplies vegetables, meat and groceries to restaurants on credit, and each invoice has a due date.
- Difficulty: Easy
- Topics: Dates, Joins, Conditional Logic
- Dialect: MySQL
- Problem: #1030
Problem statement
Hyperpure supplies vegetables, meat and groceries to restaurants on credit, and each invoice has a due date. On 2024-08-31 the collections team wants every invoice that was, or still is, late.
An invoice's lateness is the days from due_on to paid_on; for an unpaid invoice (paid_on NULL) it is the days from due_on to 2024-08-31. Return the invoices whose lateness is greater than 0, with invoice_id, restaurant_name, amount and days_late. Order by days_late descending, then invoice_id ascending.
A practice scenario set at Zomato: the tables and data are invented, and this is not a question from, or endorsed by, Zomato.
Tables
Table: Restaurant
| Column | Type |
|---|---|
| restaurant_id | int |
| restaurant_name | varchar |
Primary key: restaurant_id.
One row per restaurant buying from Hyperpure.
Table: Invoice
| Column | Type |
|---|---|
| invoice_id | int |
| restaurant_id | int |
| amount | int |
| due_on | date |
| paid_on | date |
Primary key: invoice_id.
Amounts in rupees. paid_on is NULL while the invoice is unpaid; no invoice was paid after 2024-08-31.
Examples
Example 1
Restaurant
| restaurant_id | restaurant_name |
|---|---|
| 1 | Spice Route |
| 2 | Biryani House |
| 3 | Chaat Junction |
Invoice
| invoice_id | restaurant_id | amount | due_on | paid_on |
|---|---|---|---|---|
| 501 | 1 | 18500 | 2024-07-10 | 2024-07-15 |
| 502 | 1 | 9200 | 2024-08-05 | NULL |
| 503 | 2 | 30400 | 2024-08-20 | 2024-08-20 |
| 504 | 2 | 12000 | 2024-09-05 | NULL |
| 505 | 3 | 7600 | 2024-08-26 | 2024-08-31 |
| 506 | 3 | 4100 | 2024-07-01 | 2024-06-28 |
Output
| invoice_id | restaurant_name | amount | days_late |
|---|---|---|---|
| 502 | Spice Route | 9200 | 26 |
| 501 | Spice Route | 18500 | 5 |
| 505 | Chaat Junction | 7600 | 5 |
How to solve Zomato: Hyperpure Invoices Paid Late or Still Overdue
Both kinds of late invoice reduce to one number once the missing payment date is replaced by the report date: COALESCE(paid_on, '2024-08-31') is the day the lateness is measured to, and DATEDIFF of that and due_on is the lateness in days.
Keeping only days_late > 0 handles every other case in one test. An invoice paid on or before its due date gives 0 or a negative number; an unpaid invoice that is not due until September gives a negative number too, since 31 August is before its due date. Neither is late.
The same expression appears in SELECT and WHERE because a column alias cannot be used in WHERE; the alternative computes days_late once in a derived table with an explicit CASE and filters the outer query instead. The order is fixed by lateness with invoice_id breaking ties. Both versions scan the invoices once and join each to its restaurant by key.
Reference solution (MySQL)
SELECT i.invoice_id, r.restaurant_name, i.amount,
DATEDIFF(COALESCE(i.paid_on, '2024-08-31'), i.due_on) AS days_late
FROM Invoice i
JOIN Restaurant r ON r.restaurant_id = i.restaurant_id
WHERE DATEDIFF(COALESCE(i.paid_on, '2024-08-31'), i.due_on) > 0
ORDER BY days_late DESC, i.invoice_idAnother way
SELECT invoice_id, restaurant_name, amount, days_late FROM (
SELECT i.invoice_id, r.restaurant_name, i.amount,
CASE WHEN i.paid_on IS NULL THEN DATEDIFF('2024-08-31', i.due_on)
ELSE DATEDIFF(i.paid_on, i.due_on) END AS days_late
FROM Invoice i, Restaurant r WHERE r.restaurant_id = i.restaurant_id
) t
WHERE days_late > 0
ORDER BY days_late DESC, invoice_id ASC← Zomato: Blinkit Store Items Below Their Reorder Level · Zomato: Gold Membership Renewal Rate by Plan →