HardCTEs High Frequency

Recursive Loan Balance Paydown Schedule

Frequently Asked By:StripeCoinbaseBloomberg

Interactive In-Browser Workspace

Practice this problem on live schema tables in PostgreSQL 16 WASM with instant row verification and visual EXPLAIN plans.

Solve in Workbench

Problem Statement

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.

Why Technical Interviewers Ask This

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.

Common Pitfalls & Interview Traps

Forgetting required sorting and deterministic tie-breaking.

Not accounting for NULL or zero-value denominators during calculations.

Using inefficient quadratic subqueries instead of single-pass ctes constructs.

Canonical Modern SQL Solution

PostgreSQL 16 / ANSI SQL

WITH RECURSIVE schedule AS (
        SELECT 
          1 AS month_num,
          monthly_payment AS payment_amount,
          (initial_principal - monthly_payment) AS remaining_balance
        FROM loan_config
        WHERE loan_id = 1

        UNION ALL

        SELECT 
          s.month_num + 1,
          s.payment_amount,
          (s.remaining_balance - s.payment_amount)
        FROM schedule s
        WHERE s.month_num < 4
      )
      SELECT 
        month_num,
        payment_amount,
        remaining_balance
      FROM schedule
      ORDER BY month_num ASC;

Approach Breakdown

This query addresses the problem requirements by filtering and structuring data in CTEs. It meets the target criteria: Monthly paydown schedule showing remaining loan balance after 4 periods.

Edge Cases to Verbally Clarify with Your Interviewer

The dataset contains zero matching records (must return valid empty result without crashing).

Multiple records tie on sorting criteria (must evaluate tie-breaking order deterministically).

Foreign keys or secondary attributes have NULL values.

Frequently Asked Follow-Up Questions

What makes this question a favorite among Stripe and Coinbase interviewers?

Interviewers use this problem to test whether candidates write defensive, scalable CTEs queries that run efficiently on multi-million row production databases without excessive memory usage.

How does the query optimizer execute this query?

Using single-pass table scans with index lookups or sort buffers. Running EXPLAIN (ANALYZE, BUFFERS) in the DB Exercises workbench visualizes the execution nodes and buffer hit cache.