Recursive SQL

!sqlite3 fin.db .databases .quit

main: /home/git_repos/blog/snippets/2022-11-20-recursive-sql/fin.db r/w

import sqlite3
import sqlalchemy

# enable sql magic for Jupyter notebook

# load jupyter extention (requires pip install ipython-sql)
%load_ext sql
# disable autocommit
%config SqlMagic.autocommit=False

%sql sqlite:///rec.db
    
# Test connection
v1 = "Ready to go!"
%sql df_test << SELECT :v1 as "Test"
assert(df_test.DataFrame().values[0]==v1)

 * sqlite:///rec.db
Done.
Returning data to local variable df_test

Running a recursive query

This is an example from the blog post Pay off Mortgage or Invest. It calculates a simple amortization plan. The recursion stops as soon as the balance reaches 0.

%%sql
with plan(year, interest, principal, balance) as
(
    SELECT 0 AS year,
           NULL AS interest,
           NULL AS principal,
           100000 AS balance
    UNION
    SELECT p.year + 1 AS year,
           -p.balance * 0.02 AS interest,
           MIN(10000 - p.balance * 0.02, p.balance) AS principal,
           p.balance - (min(10000 - p.balance * 0.02, p.balance)) AS balance
      FROM plan p
     WHERE p.balance - (min(10000 - p.balance * 0.02, p.balance) + 0) >= 0 
       AND MIN(10000 - p.balance * 0.02, p.balance) > 0 
)
SELECT year,
       round(interest) AS interest, 
       round(principal) AS principal,  
       round(-balance) AS balance
  FROM plan

 * sqlite:///rec.db
Done.

year	interest	principal	balance
0	None	None	-100000.0
1	-2000.0	8000.0	-92000.0
2	-1840.0	8160.0	-83840.0
3	-1677.0	8323.0	-75517.0
4	-1510.0	8490.0	-67027.0
5	-1341.0	8659.0	-58368.0
6	-1167.0	8833.0	-49535.0
7	-991.0	9009.0	-40526.0
8	-811.0	9189.0	-31336.0
9	-627.0	9373.0	-21963.0
10	-439.0	9561.0	-12402.0
11	-248.0	9752.0	-2650.0
12	-53.0	2650.0	0.0

Ressources

Interactive notebook

• Play with the numbers:
• Downloading the notebook:

Related Blog

• Pay off Mortgage or Invest