Flipkart: Pay Later Accounts Spending Past Their Limit

Easy
653.

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

ColumnType
account_idint
customer_namevarchar
credit_limitint

Primary key: account_id.

One row per Pay Later account; credit_limit is in rupees.

Table: PayLaterSpend

ColumnType
spend_idint
account_idint
amountint
repaidbool

Primary key: spend_id.

One row per purchase charged to Pay Later; repaid = 1 once the customer has paid it back.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

PayLaterAccount

account_idcustomer_namecredit_limit
1Aarav10000
2Diya5000
3Kabir20000
4Meera5000

PayLaterSpend

spend_idaccount_idamountrepaid
1160000
2145000
3230000
4240001
53250001
6450000
7411

Output

customer_namecredit_limitoutstanding
Aarav1000010500
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 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

ColumnType
account_idint
customer_namevarchar
credit_limitint

Primary key: account_id.

One row per Pay Later account; credit_limit is in rupees.

Table: PayLaterSpend

ColumnType
spend_idint
account_idint
amountint
repaidbool

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_idcustomer_namecredit_limit
1Aarav10000
2Diya5000
3Kabir20000
4Meera5000

PayLaterSpend

spend_idaccount_idamountrepaid
1160000
2145000
3230000
4240001
53250001
6450000
7411

Output

customer_namecredit_limitoutstanding
Aarav1000010500

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_limit

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