#07 SQL and analytics 5.9 min
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.
Lower the CTE threshold
Copy the code folder first. In cte.sql change 110 to 100. Add up each customer's amounts in setup.sql by hand, then predict which customers will appear and in what order. Run python run.py cte.sql and compare. Does Ben appear now?
A hint
The final select keeps totals above the number and sorts with the largest first.
Find the smallest order instead
In qualify.sql change DESC to ASC. Predict the three amounts you will get, one per customer, by looking at setup.sql. Run python run.py qualify.sql and check. Which part of the query decides which order gets number one?
A hint
ROW_NUMBER numbers the rows inside each customer in the order given by ORDER BY inside the OVER clause.
Run the tie query on a customer without ties
In ties.sql change 'Ben' to 'Ava'. Ava has no two orders on the same day. Predict whether default_total and row_total will differ on any row, then run python run.py ties.sql. Why did Ben's rows behave differently in the video?
A hint
With only ORDER BY order_date, rows that share a date are treated as one group. Check Ava's dates in setup.sql.
Rewrite the failing query with a CTE
Make a new file ranked.sql. Put the query from bad_where.sql inside a WITH clause called ranked, then write an outer SELECT from ranked that keeps rows where n is 1, ordered by customer. Predict whether it runs without an error and whether it returns the same rows as qualify.sql. Run python run.py ranked.sql to check.
A hint
Inside the outer query, n is an ordinary column of ranked, so WHERE can use it. The video suggests this where QUALIFY is missing.
These tasks are for you to try on your own machine. Nothing is checked or saved.
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.
