#07 · SQL and analytics · 5.9 minSQL 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.
Python 3.12.15 | duckdb==2.0.09 videos tagged "sql tutorial".
#07 · SQL and analytics · 5.9 minLearn CTEs and window functions with DuckDB on a tiny orders table, then see two common mistakes, with real output.
Python 3.12.15 | duckdb==2.0.0
#122 · SQL and analytics · 6.6 minThree queries asked which customers have no orders; NOT IN returned 0 rows because of one NULL.
Python 3.12.15
#124 · SQL and analytics · 6.6 minA 20-line SQL query builds a cohort retention table; remove DISTINCT and retention shows 150 percent.
Python 3.12.15
#125 · SQL and analytics · 6.7 minSix made-up sales pivot into 3 rows in SQLite, which has no PIVOT or LATERAL keyword.
Python 3.12.15
#126 · SQL and analytics · 6.8 minOne recursive SQL query finds that Asha has 9 people under her; another goes round a loop until a LIMIT stops it.
Python 3.12.15
#127 · SQL and analytics · 6.6 minSix made-up orders total 220, but two joins make the same SUM say 505, with no error.
Python 3.12.15
#128 · SQL and analytics · 6.9 minIn SQLite, a total stored as the text 9.50 passed a filter for totals over 20; a cast fixed it.
Python 3.12.15
#134 · SQL and analytics · 6.4 minTwo missing days made SQLite report 156.6 in sales a day for a week that averaged 111.9 once the gaps were filled.
Python 3.12.15
#135 · SQL and analytics · 6.7 minOn 200,000 made-up orders, one index turned a full scan into a search that was at least 20 times faster.
Python 3.12.15