Flipkart: Pay Later Bills Not Cleared by the Due Date

Medium
664.

Flipkart: Pay Later Bills Not Cleared by the Due Date

Every month Flipkart Pay Later raises one bill per account, due on the 5th of the next month. Customers may repay a bill in several parts. Collections wants the bills that were short on the due date.

For each bill, paid_by_due is the sum of its repayments made on or before due_on (0 if none). Return bill_id, account_id, paid_by_due and shortfall (amount - paid_by_due) for every bill where paid_by_due is less than the bill amount. Repayments after the due date do not help. Order by shortfall descending, then bill_id.

A practice scenario set at Flipkart: the tables and data are invented, and this is not a question from, or endorsed by, Flipkart.

Tables

Table: PayLaterBill

ColumnType
bill_idint
account_idint
bill_monthchar
amountint
due_ondate

Primary key: bill_id.

One row per monthly bill; bill_month is 'YYYY-MM' and amount is in rupees.

Table: Repayment

ColumnType
repayment_idint
bill_idint
paid_ondate
amountint

Primary key: repayment_id.

One row per payment towards a bill; bill_id always names a bill.

Example

The rows must come back in the order shown.

PayLaterBill

bill_idaccount_idbill_monthamountdue_on
1112024-0840002024-09-05
2122024-0825002024-09-05
3112024-0930002024-10-05
4132024-0912002024-10-05

Repayment

repayment_idbill_idpaid_onamount
112024-09-012500
212024-09-051500
322024-09-041000
422024-09-081500
532024-10-063000

Output

bill_idaccount_idpaid_by_dueshortfall
31103000
21210001500
41301200
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.

Flipkart: Pay Later Bills Not Cleared by the Due Date

Every month Flipkart Pay Later raises one bill per account, due on the 5th of the next month. Customers may repay a bill in several parts.

  • Difficulty: Medium
  • Topics: Joins, Aggregation, Dates
  • Dialect: MySQL
  • Problem: #664

Problem statement

Every month Flipkart Pay Later raises one bill per account, due on the 5th of the next month. Customers may repay a bill in several parts. Collections wants the bills that were short on the due date.

For each bill, paid_by_due is the sum of its repayments made on or before due_on (0 if none). Return bill_id, account_id, paid_by_due and shortfall (amount - paid_by_due) for every bill where paid_by_due is less than the bill amount. Repayments after the due date do not help. Order by shortfall descending, then bill_id.

A practice scenario set at Flipkart: the tables and data are invented, and this is not a question from, or endorsed by, Flipkart.

Tables

Table: PayLaterBill

ColumnType
bill_idint
account_idint
bill_monthchar
amountint
due_ondate

Primary key: bill_id.

One row per monthly bill; bill_month is 'YYYY-MM' and amount is in rupees.

Table: Repayment

ColumnType
repayment_idint
bill_idint
paid_ondate
amountint

Primary key: repayment_id.

One row per payment towards a bill; bill_id always names a bill.

Examples

Example 1

PayLaterBill

bill_idaccount_idbill_monthamountdue_on
1112024-0840002024-09-05
2122024-0825002024-09-05
3112024-0930002024-10-05
4132024-0912002024-10-05

Repayment

repayment_idbill_idpaid_onamount
112024-09-012500
212024-09-051500
322024-09-041000
422024-09-081500
532024-10-063000

Output

bill_idaccount_idpaid_by_dueshortfall
31103000
21210001500
41301200

How to solve Flipkart: Pay Later Bills Not Cleared by the Due Date

Each bill needs the total of its repayments up to its own due date. The LEFT JOIN carries both conditions in its ON clause — the repayment belongs to the bill, and r.paid_on <= b.due_on — so a late repayment is never joined, and a bill with nothing paid on time keeps one NULL-filled row. Had the date test been in WHERE, those bills would have disappeared instead of showing their full shortfall.

After grouping by bill, SUM(r.amount) is NULL for such a bill, so COALESCE(…, 0) turns it into 0 paid. The HAVING clause keeps the bills where the on-time total is below the amount; a bill paid off exactly on the due date has a shortfall of 0 and is left out.

The alternative computes paid_by_due with a correlated scalar subquery per bill and filters in an outer query. Both read each bill's repayments once; an index on Repayment(bill_id, paid_on) serves either.

Reference solution (MySQL)

SELECT b.bill_id, b.account_id,
       COALESCE(SUM(r.amount), 0) AS paid_by_due,
       b.amount - COALESCE(SUM(r.amount), 0) AS shortfall
FROM PayLaterBill b
LEFT JOIN Repayment r ON r.bill_id = b.bill_id AND r.paid_on <= b.due_on
GROUP BY b.bill_id, b.account_id, b.amount
HAVING COALESCE(SUM(r.amount), 0) < b.amount
ORDER BY shortfall DESC, b.bill_id

Another way

SELECT bill_id, account_id, paid_by_due, amount - paid_by_due AS shortfall
FROM (SELECT b.bill_id, b.account_id, b.amount,
             (SELECT IFNULL(SUM(r.amount), 0) FROM Repayment r WHERE r.bill_id = b.bill_id AND r.paid_on <= b.due_on) AS paid_by_due
      FROM PayLaterBill b) t
WHERE paid_by_due < amount
ORDER BY shortfall DESC, bill_id

← Flipkart: Top Three Brands per Category Plus an Others Line · Flipkart: Running GMV Through the Sale by Category →