learning-it by example

#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.

1. What does the video call the key difference between a window function and GROUP BY?
2. Numbering each customer's orders and filtering in WHERE for number one failed. Why, and what fixed it?
3. With the default frame (ORDER BY order_date only), what running total did both of Ben's 10 January orders show?
4. What does the video say this tiny eight-order table does not tell you?
5. In the window function run, what did LAG give for the first order of each customer?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. 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.

  2. 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.

  3. 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.

  4. 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.

Up next in SQL and analytics

More in SQL and analytics

All SQL and analytics videos

Related videos