Loan Delinquency Buckets

SQL practice question · Hard · Date & Time · JPMorgan

As of 2026-04-30: only payments with paid_date <= '2026-04-30' count, and partial payments for an installment add up. An installment is overdue when due_date < '2026-04-30' and the amount paid for it is less than amount_due. A loan's days past due (DPD) = days from its OLDEST overdue due_date to 2026-04-30. Bucket each loan: 'Current' (nothing overdue), '1-30', '31-60', '61-90', '90+'. Return bucket, loans and overdue_amount (sum of unpaid amounts of overdue installments, ROUND 2) for buckets that have loans, ordered Current, 1-30, 31-60, 61-90, 90+.

Table: emi_schedule

Schema of emi_schedule
ColumnType
loan_idTEXT
installment_noINTEGER
due_dateDATE
amount_dueREAL
Sample rows of emi_schedule
loan_idinstallment_nodue_dateamount_due
L112026-01-051000
L122026-02-051000
L132026-03-051000
L142026-04-051000
L152026-05-051000
L212026-01-051000
L222026-02-051000
L232026-03-051000

Table: emi_payments

Schema of emi_payments
ColumnType
payment_idINTEGER
loan_idTEXT
installment_noINTEGER
paid_dateDATE
amount_paidREAL
Sample rows of emi_payments
payment_idloan_idinstallment_nopaid_dateamount_paid
1L112026-01-051000
2L122026-02-041000
3L132026-03-051000
4L142026-04-061000
5L212026-01-051000
6L222026-02-051000
7L312026-01-051000
8L322026-02-051000
Expected output on the sample data
bucketloansoverdue_amount
Current10
1-301600
31-6012000

Solve it in the live editor on this page — run on sample data, then submit against hidden tests.

‹ As-Of Price Join for Trade Valuation · MRR Movements: New, Expansion, Contraction, Churn ›

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Question Asked in Interview Blog
SQL Interview Practice

Company-wise SQL questions

{{ totalCount }} questions in the style asked at Amazon, Microsoft, Google, Meta, Flipkart and more — {{ hardPct }}% Hard. Write SQL in a real database engine in your browser, run it on sample data, then evaluate against hidden tests.

🏆 Weekly SQL Contest
{{ totalCount }}
Questions
{{ companyCount }}
Companies
{{ prog.total.solved }}
Solved
Bookmark Question Companies Difficulty Category Status
{{ r.n }} - {{ r.title }} {{ c }} {{ r.difficulty }} {{ r.category }} {{ r.statusIcon }}
No questions match these filters.
Showing {{ rangeLabel }} of {{ filteredCount }}

Company tags show where this type of question is commonly reported in interviews. Questions, data and solutions are written by TechTown.
← All questions

{{ cur.n }} - {{ cur.title }}

🏆 Contest {{ contestWeek }}
{{ cur.catLabel }} {{ cur.diffLabel }} {{ c }}
Embed this questionPaste on a blog, course page or LMS. Free to use — educator guide 
{{ embedCode }}

{{ cur.statement }}

Table: {{ tb.name }}
ColumnType {{ c.name }}{{ c.type }}
{{ h }} {{ v.text }}
Expected output on the sample data:
{{ h }} {{ v.text }}
  • {{ h }}

No submissions yet. Press Evaluate to submit your query.
{{ s.verdict }} {{ s.meta }}
The solution unlocks after you solve this question or after 3 evaluations that didn't pass ({{ failCount }}/3 so far). Trying first is the fastest way to learn. 
{{ solutionSql }}
  1. {{ e }}
⏱ {{ timerLabel }}
{{ fnHelpTitle }}
{{ fnHelpText }}
Writing {{ dialectName }} · Ctrl/Cmd + Enter runs the query · Tab indents · your code is saved automatically
Output {{ out.status }}
  • {{ w }}
    •
{{ out.verdict }}
{{ out.detail }}
 
{{ out.error }}
Expected (sample data)
{{ h }}{{ v.text }}
Your output (sample data)
{{ h }}{{ v.text }}
{{ h }} {{ v.text }}
Run your query to see the result here.
🔒

To Track Your Progress, Log In / Sign Up

You have solved your 3 free questions — nice work! Log in (free) to unlock all 150 questions, keep your streak and appear on the leaderboard.

Sign Up Free Log In