Learn / Walkthrough
DPD and buckets in practice: a worked example with ChatGPT and SQL
This guide follows one analysis from start to finish: DPD and bucket for every loan on 30 September 2026. You fill in the Bucketwise form, paste the prompt into ChatGPT, run the SQL it writes, check the result and put it in a short report. The screens use a sample loan extract, not a real portfolio. The tool and the prompt work the same way on your own tables.
What you are building
One row per loan, with five fields: the number of unpaid instalments, the overdue amount, the oldest unpaid due date, DPD and the bucket. You need two tables to get there. Tap any screenshot to open it full size.
| Table | One row is | Columns used |
|---|---|---|
| repay_schedule | One instalment that falls due | loan_id, due_dt, due_amt |
| repay_txn | One payment received, bounced or reversed | loan_id, pay_dt, pay_amt, txn_status |
Your tables will have other names. You type yours into the form in the next step.
Before you start: check the extract
The SQL in this guide assumes that the data is clean. Check three things on your own extract before you run anything:
- No loan has two schedule rows for the same due date.
- No payment is posted twice (same loan, date and amount).
- No payment is dated before the loan was disbursed.
The extract used in this guide has been through these checks. If one of them fails on your data, fix the extract first. A duplicated instalment or a double-posted payment changes DPD and overdue amount for that loan, and the query will not warn you.
Step 1: Fill in the form
Open the DPD and buckets tool. The top of the form asks for the names of your tables and columns. The rest asks for the choices that change the answer.
- Payment status: which values count as a successful payment, and which to leave out. Here SUCCESS counts, BOUNCED is left out and reversals are negative amounts.
- Payment made before its due date: whether it settles the instalment when it falls due.
- Payment above all dues so far: whether to ignore it or carry it forward.
- Part-paid instalment: whether it counts as one unpaid instalment, with the balance as the overdue amount.
- Payment allocation and buckets: oldest due first, and the five buckets Current, 1-30, 31-60, 61-90 and 91+.
These are definitions, not table names. They are the choices a different lender would make differently, so the tool asks for them before the AI writes any code.
Step 2: Copy the prompt
Press Copy prompt. The full prompt is 29 lines and goes to your clipboard. The preview shows how it starts.
The prompt gives the AI your names and your definitions. It tells the AI to state its assumptions, write the code and ask at most two questions, and only if it cannot proceed without the answer. Use only an AI tool that your institution has approved.
Step 3: Paste it into ChatGPT
ChatGPT started with its assumptions. Read them before you read the code. Each one is a decision, and you can change any of them.
Two are worth noticing. It treats a part-paid instalment as unpaid for the balance that remains, and it assumes the schedule has one row per loan per due date. That second assumption is exactly what the extract checks above confirm.
Then it wrote the query, in ten labelled steps with a comment on each. Copy it as it is.
Step 4: Run it in your SQL tool
Paste the query into the SQL tool you use for your own database and run it.
It returned one row per loan with an instalment due on or before 30 September: 5,988 rows on this extract.
A loan with nothing overdue shows 0 unpaid instalments, an overdue amount of 0, no oldest unpaid date, DPD 0 and the bucket Current. Loan L000007 has two unpaid instalments, 26,772 overdue and an oldest unpaid due date of 20 August, so its DPD on 30 September is 41 and it sits in 31-60.
Step 5: Run the three checks ChatGPT gave you
The prompt asks for three validation queries. Run all three before you trust the result.
Check 1 counts payments by status. You should see SUCCESS, BOUNCED and REVERSAL and nothing unexpected, with reversals negative.
Check 2 looks for loans with two schedule rows for the same due date. It should return no rows.
Check 3 lists loans with no eligible payment at all. They should still appear in the main result, with all their dues unpaid.
On this extract, all three behaved as expected. The eight loans in Check 3 appear in the main result with every instalment unpaid.
Step 6: Check it yourself
The AI's checks test the logic it wrote. Add your own, because only you can compare against your own systems.
| Check | What it should show |
|---|---|
| Row count | Equals the number of loans with an instalment due by the as-of date. Here, 5,988. |
| Values | No negative DPD and no empty bucket. |
| Finance figure | The sum of the overdue amount agrees with your finance overdue figure within a small tolerance. |
| Ten loans by hand | Pick ten loans across the buckets and trace each one to the schedule and the payments. |
On this extract we also compared every loan against a separately calculated answer. DPD, bucket and overdue amount agreed on all 5,988 loans. On your own data, the ten-loan trace is how you get that comfort.
Step 7: Read the numbers and write it up
Group the result by bucket and put it on one page.
In this extract 5,290 loans are current, 428 are in 1-30, 50 in 31-60, 26 in 61-90 and 194 in 91+. That is 698 loans past due, 11.7% of the loans, with about 96.6 lakh overdue in total.
Be careful when you set the result beside a published figure. The Bucketwise benchmark for personal loans 90 or more days past due is 0.9% of balances (TransUnion CIBIL, Mar-26). This table counts loans, and its 91+ bucket includes loans past 179 days, so the two are directional at best. Compare only when the definition, the unit and the segment match.
Use it on your own tables
- Replace every name in the form with yours. Keep your own payment status values and your own allocation rule.
- Run it for one month-end first and check it before you run it for twelve.
- If your tables need joining or calculating before this step, use the Prepare your data tool first.
- Check the DPD rule against your collections policy. A different allocation rule gives a different DPD.
Try it on your own tables
The free tools give you the prompt, the checks and how to present the result. You enter table names only.
See the free toolsFrequently asked questions
Do I need to paste customer data into ChatGPT?
No. The prompt contains table and column names only, plus your definitions. Use an AI tool that your institution has approved, and check your information-security policy on schema names.
Will the AI ask me questions before it writes the query?
By default it states its assumptions and writes the code. It asks at most two questions, and only when something essential is missing. You correct any wrong assumption in one reply.
Can I use this with a database other than PostgreSQL?
Yes. The form has a database selector with PostgreSQL, MySQL, SQL Server, Oracle, BigQuery, Snowflake and Python with pandas, and the prompt asks for that syntax.
Which DPD rule does the tool use?
DPD is the as-of date minus the due date of the oldest instalment that is still unpaid after payments are allocated oldest due first, less any grace days you set. A loan with nothing overdue has DPD 0.
Keep reading
What is DPD (days past due) and how to calculate it
DPD counts how many days a loan is overdue. Learn the rule, a worked example with several instalments, and the mistakes that make DPD wrong.
BasicsDelinquency buckets: 1-30, 31-60, 61-90 and 90+ explained
What delinquency buckets are, how DPD maps to them, how they link to RBI's SMA categories and Ind AS 109 stages, and how to report them.
Using AIHow to check AI-written credit-risk SQL before you trust it
AI tools write SQL that runs but may be wrong. Ten checks to run on any credit-risk query: row counts, duplicates, DPD edges, bucket totals and a hand trace.