learning-it by example

Back to the video

SQL Window Functions and CTEs in DuckDB: Two Mistakes

Learn CTEs and window functions with DuckDB on a tiny orders table, then see two common mistakes, with real output.

Video #07 ยท SQL and analytics

Steps in the video

  1. 0:00 CTEs and window functions
  2. 0:18 The two ideas
  3. 0:46 Setup
  4. 0:59 The data
  5. 1:13 A CTE
  6. 1:43 A window function
  7. 2:31 Mistake one
  8. 3:25 Mistake two
  9. 4:05 What this does not show
  10. 4:35 Mistakes to avoid
  11. 5:00 My take and next steps
  12. 5:27 Recap

Files

  • bad_where.sql (4 lines)
  • cte.sql (9 lines)
  • qualify.sql (4 lines)
  • run.py (11 lines)
  • setup.sql (11 lines)
  • ties.sql (9 lines)
  • window.sql (9 lines)

Download the code (zip)

Tested with

Python 3.12.15 | duckdb==2.0.0

What this does not show

A made-up table of eight orders in DuckDB 2.0.0; nothing here measures speed. QUALIFY is not standard SQL, and other databases can differ in syntax and in how window frames treat ties.

Sources

Learning IT by Example.