DB Exercises
0
← Exercises/
CTEs•11 of 12
PrevNext
CTEsAdvancedPRO

Recursive Loan Balance Paydown Schedule

Scenario

Consumer lending systems calculate loan repayment schedules. Simulate 4 monthly payments of $300 on an initial $1,000 principal loan with 0% interest using a Recursive CTE.

Task
Write a Recursive CTE generating 4 monthly payment periods: - Period 1 starts with `remaining_balance = 1000 - 300 = 700.00`. - Each subsequent period increments `month_num` by 1 and reduces `remaining_balance` by 300.00. - Stop when `month_num = 4` (final balance = `1000 - 1200`, or remaining amount). Return: 1. `month_num` 2. `payment_amount`: 300.00 3. `remaining_balance` Order by `month_num` ascending.
Expected Output Format

Monthly paydown schedule showing remaining loan balance after 4 periods.

Input Tables & Sample Data0 tables
Loading database schema...
Need a hint?
query.sql|Local Sandbox
Loading SQL editor...
Click "Run" or press ⌘ + Enter to execute your query

About the “Recursive Loan Balance Paydown Schedule” SQL Problem

Business Scenario

Consumer lending systems calculate loan repayment schedules. Simulate 4 monthly payments of $300 on an initial $1,000 principal loan with 0% interest using a Recursive CTE.

Expected Output & Criteria

Monthly paydown schedule showing remaining loan balance after 4 periods.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

This challenge executes directly against the following relational schema in your browser:

CREATE TABLE loan_config (
        loan_id INT PRIMARY KEY,
        initial_principal NUMERIC(10, 2) NOT NULL,
        monthly_payment NUMERIC(10, 2) NOT NULL
      );

Target SQL Clauses & Operators

WITHRECURSIVEUNION ALL

Want more CTEs practice?

Explore the full syllabus of 12 exercises, syntax guides, and interview pitfalls.

View CTEs Track→