dbdbexercises
Practice Free
← Syllabus|
Hard

14. Delinquency Aging Buckets (30/60/90 Days Past Due)

HardCTEsPostgreSQL 16PRO
Table: consumer_loans
+------------------+------------+
| Column Name      | Type       |
+------------------+------------+
| loan_id          | int        |
| unpaid_principal | numeric(10 |
| days_overdue     | int        |
+------------------+------------+

loan_id is the unique key for this table. Each row contains record details for this enterprise dataset.

Credit risk modeling teams group delinquent consumer installment loans into standardized delinquency aging buckets to calculate required loan loss capital provisions.

Write a query using a CTE `classified_loans` to categorize each loan in `consumer_loans` into an `aging_bucket`: - If `days_overdue >= 90`, bucket is `'90+ DPD'` - If `days_overdue >= 60`, bucket is `'60-89 DPD'` - If `days_overdue >= 30`, bucket is `'30-59 DPD'` - Otherwise, bucket is `'1-29 DPD'` In the outer query, group by `aging_bucket` where `days_overdue >= 1`. Return: 1. `aging_bucket` 2. `loan_count`: Total loans in bucket 3. `total_unpaid_principal`: Sum of `unpaid_principal` Order results by `total_unpaid_principal` descending.

Return the result table ordered as specified. The result format is in the following example.

Example 1:
Input:
Preparing sample rows...
Output:
Calculating benchmark target...
Explanation:

Aggregate outstanding unpaid principal grouped by loan delinquency aging bucket.

Constraints:
  • Query must utilize clause(s): WITHCASESUMGROUP BY
  • Result order matters (tie-breakers and sort order must be strictly preserved).
  • Engine: PostgreSQL 16 standard syntax (in-browser WASM).
query.sql|PostgreSQL 16 WASM
Loading SQL editor...
You must run your code first

Write your SQL query in the editor and click Run or Submit to test against in-browser PostgreSQL 16 WASM.

About the “Delinquency Aging Buckets (30/60/90 Days Past Due)” SQL Problem

Business Scenario

Credit risk modeling teams group delinquent consumer installment loans into standardized delinquency aging buckets to calculate required loan loss capital provisions.

Expected Output & Criteria

Aggregate outstanding unpaid principal grouped by loan delinquency aging bucket.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM
CREATE TABLE consumer_loans (
        loan_id INT PRIMARY KEY,
        unpaid_principal NUMERIC(10, 2) NOT NULL,
        days_overdue INT NOT NULL
      );

Target SQL Clauses & Operators

WITHCASESUMGROUP BY

Want more CTEs practice?

Explore the full syllabus of 16 exercises and syntax guides.

View CTEs Track