Zomato: Hyperpure Invoices Paid Late or Still Overdue

Easy
1030.

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. 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

ColumnType
restaurant_idint
restaurant_namevarchar

Primary key: restaurant_id.

One row per restaurant buying from Hyperpure.

Table: Invoice

ColumnType
invoice_idint
restaurant_idint
amountint
due_ondate
paid_ondate

Primary key: invoice_id.

Amounts in rupees. paid_on is NULL while the invoice is unpaid; no invoice was paid after 2024-08-31.

Example

The rows must come back in the order shown.

Restaurant

restaurant_idrestaurant_name
1Spice Route
2Biryani House
3Chaat Junction

Invoice

invoice_idrestaurant_idamountdue_onpaid_on
5011185002024-07-102024-07-15
502192002024-08-05NULL
5032304002024-08-202024-08-20
5042120002024-09-05NULL
505376002024-08-262024-08-31
506341002024-07-012024-06-28

Output

invoice_idrestaurant_nameamountdays_late
502Spice Route920026
501Spice Route185005
505Chaat Junction76005
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
restaurant_idint
restaurant_namevarchar

Primary key: restaurant_id.

One row per restaurant buying from Hyperpure.

Table: Invoice

ColumnType
invoice_idint
restaurant_idint
amountint
due_ondate
paid_ondate

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_idrestaurant_name
1Spice Route
2Biryani House
3Chaat Junction

Invoice

invoice_idrestaurant_idamountdue_onpaid_on
5011185002024-07-102024-07-15
502192002024-08-05NULL
5032304002024-08-202024-08-20
5042120002024-09-05NULL
505376002024-08-262024-08-31
506341002024-07-012024-06-28

Output

invoice_idrestaurant_nameamountdays_late
502Spice Route920026
501Spice Route185005
505Chaat Junction76005

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_id

Another 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 →