SQL CTEs and Ranking
A local workbook for readable SQL, top-N results and recursive hierarchies.
A focused 6-page practical workbook by Thomas Reid, with runnable SQLite examples, expected-result checks, setup instructions, exercises and worked answers.
Name intermediate results with a CTE, select the top two rows per group, compare ROW_NUMBER, RANK and DENSE_RANK, and walk a small employee hierarchy with a recursive CTE. Understand tie handling, final ordering and the limits of a recursion depth cap.
Download: one ZIP containing the PDF, Python/SQL source and README. Uses Python’s built-in sqlite3 module; no database server or third-party Python package is needed. Tested on Windows with Python 3.13.17.
This revised practical edition combines lessons from two of my published Medium articles, with replacement runnable examples. It is not a verbatim reprint. Suitable for readers familiar with basic SELECT queries who can run a Python script.