Flipkart: Pay Later Accounts Spending Past Their Limit
Flipkart Pay Later lets a customer buy now and repay at the end of the month, up to a credit limit.
- Difficulty: Easy
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #653
Problem statement
Flipkart Pay Later lets a customer buy now and repay at the end of the month, up to a credit limit. The risk team wants the accounts where an approval bug let unpaid spends exceed the limit.
For each account, its outstanding amount is the sum of its spends with repaid = 0. Return customer_name, credit_limit and outstanding for every account whose outstanding is strictly greater than its credit_limit. Return the rows in any order.
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: PayLaterAccount
| Column | Type |
|---|---|
| account_id | int |
| customer_name | varchar |
| credit_limit | int |
Primary key: account_id.
One row per Pay Later account; credit_limit is in rupees.
Table: PayLaterSpend
| Column | Type |
|---|---|
| spend_id | int |
| account_id | int |
| amount | int |
| repaid | bool |
Primary key: spend_id.
One row per purchase charged to Pay Later; repaid = 1 once the customer has paid it back.
Examples
Example 1
PayLaterAccount
| account_id | customer_name | credit_limit |
|---|---|---|
| 1 | Aarav | 10000 |
| 2 | Diya | 5000 |
| 3 | Kabir | 20000 |
| 4 | Meera | 5000 |
PayLaterSpend
| spend_id | account_id | amount | repaid |
|---|---|---|---|
| 1 | 1 | 6000 | 0 |
| 2 | 1 | 4500 | 0 |
| 3 | 2 | 3000 | 0 |
| 4 | 2 | 4000 | 1 |
| 5 | 3 | 25000 | 1 |
| 6 | 4 | 5000 | 0 |
| 7 | 4 | 1 | 1 |
Output
| customer_name | credit_limit | outstanding |
|---|---|---|
| Aarav | 10000 | 10500 |
How to solve Flipkart: Pay Later Accounts Spending Past Their Limit
Outstanding is a per-account total, so join the spends to their account, keep only the unpaid ones in WHERE, and GROUP BY the account. The comparison with the limit uses the aggregate, so it belongs in HAVING SUM(s.amount) > a.credit_limit. Because credit_limit is also selected and compared, it is listed in the GROUP BY; it is a column of the account, so this changes no group.
Repaid spends are filtered before grouping, so an account whose every spend is repaid forms no group and cannot be reported — it owes nothing. The comparison is strict: an account at exactly its limit has used all of it, which is allowed.
The alternative computes the outstanding in a derived table with a conditional sum and filters with a plain WHERE after joining. Both scan the spends once; an index on PayLaterSpend(account_id) serves the join.
Reference solution (MySQL)
SELECT a.customer_name, a.credit_limit, SUM(s.amount) AS outstanding
FROM PayLaterAccount a
JOIN PayLaterSpend s ON s.account_id = a.account_id
WHERE s.repaid = 0
GROUP BY a.account_id, a.customer_name, a.credit_limit
HAVING SUM(s.amount) > a.credit_limitAnother way
SELECT a.customer_name, a.credit_limit, t.outstanding
FROM PayLaterAccount a
JOIN (SELECT account_id, SUM(CASE WHEN repaid = 0 THEN amount ELSE 0 END) AS outstanding FROM PayLaterSpend GROUP BY account_id) t
ON t.account_id = a.account_id
WHERE t.outstanding > a.credit_limit← Flipkart: Product Questions Still Waiting for an Answer · Flipkart: Shopsy Reseller Store Handles →