Skip to content
Tutorials Center
My learning

Topics

Lesson 38 of 38 ยท Projects and Practice

Practice: Analytics Query Workout

Ten analytics SQL exercises with solutions: aggregates, joins, windows, top-N per group, gaps, deduplication and cohorts, plus DuckDB-only variations for each.

Skills stick when you use them. This workout gives you ten analytics tasks on the shop sample database (tables categories, products, customers, orders, order_items). Try each exercise yourself before you open the solution; the editor under each task already holds a working answer you can edit and extend. Everything is portable SQL, so it runs here, and the DuckDB variation of each is noted after.

As elsewhere in this track, the real DuckDB cannot run in the browser, so DuckDB-only syntax stays read-only.

Rules for the workout: revenue means quantity * unit_price on orders whose status is shipped or delivered, unless a task says otherwise.

1. Revenue by category

Task: Total revenue and units sold per category, biggest first.

SQL Solution 1 ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

DuckDB variation: GROUP BY ALL and ORDER BY ALL; SELECT * REPLACE (round(revenue, 2) AS revenue) when polishing columns.

2. Customers who never ordered

Task: List customers with no orders at all.

SQL Solution 2 ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

DuckDB variation: FROM customers c ANTI JOIN orders o ON o.customer_id = c.id.

3. The latest order per customer

Task: For each customer, show their most recent order (id, date, status).

SQL Solution 3 ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

DuckDB variation: SELECT * FROM orders QUALIFY row_number() OVER (PARTITION BY customer_id ORDER BY ordered_at DESC) = 1. Another option is arg_max(id, ordered_at) with GROUP BY customer_id.

4. Top 3 products per category

Task: For each category, the three best products by units sold (ties share a place).

SQL Solution 4 ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

DuckDB variation: put QUALIFY dense_rank() OVER (...) <= 3 directly on the aggregate query.

5. Average order value by country

Task: For each country, the number of orders, the average order value and how it compares with the overall average.

SQL Solution 5 ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The subquery computes the overall average once. Note it averages orders, not countries: an average of averages would be wrong.

6. Month-over-month growth

Task: Monthly revenue with the change from the previous month.

SQL Solution 6 ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

DuckDB variation: date_trunc('month', ordered_at::TIMESTAMP) gives a real date to sort and chart.

7. Running total and 3-month average

Task: On top of exercise 6, add the cumulative revenue and a trailing three-month average.

SQL Solution 7 ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

8. Customers with a second order

Task: What percentage of customers who ordered at least once placed more than one order?

SQL Solution 8 ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

DuckDB variation: count(*) FILTER (orders > 1).

9. Days between orders

Task: For each order, the number of days since the same customer's previous order.

SQL Solution 9 ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

DuckDB variation: date_diff('day', lag(ordered_at) OVER (...), ordered_at) on real dates.

10. Cohort retention

Task: Group customers by the month of their first order, and count how many of them ordered again in each later month offset (0, 1, 2).

SQL Solution 10 ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Divide m1 by m0 to get the one-month retention rate. In DuckDB, date_diff('month', cohort_date, active_date) replaces the arithmetic, and PIVOT can turn the offsets into columns automatically.

flowchart LR
    A["orders"] --> B["first order month = cohort"]
    A --> C["active months per customer"]
    B --> D["offset = active - cohort"]
    C --> D
    D --> E["count customers per cohort and offset"]

Cohort analysis in four steps.

How to keep practicing

  • Redo every task using only DuckDB syntax in a local install (pip install duckdb), with the shop data exported to CSV.
  • Re-solve tasks with different tools: a CTE instead of a subquery, a join instead of EXISTS, QUALIFY instead of an outer query.
  • Add quality checks to your answers: do the orders balance, are there nulls?
  • Time each query with EXPLAIN ANALYZE on a bigger generated dataset (range(10000000) makes a quick one).
  • Invent questions about your own data; the best exercises are ones you need answered.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which pattern solves 'the latest row per key'?

    Numbering rows inside each key and keeping the first gives the latest row per key.
  2. 2. Why is an average of country averages usually wrong for an overall average?

    Countries with few orders would count as much as countries with many. Average over orders instead.
  3. 3. What does DENSE_RANK() <= 3 return when two products tie for first place?

    Ties share a rank, so the number of rows can exceed three.
  4. 4. What is a cohort in retention analysis?

    Cohorts are groups defined by a shared starting event, tracked over later periods.

Summary

  • Joins and GROUP BY answer the "how much" questions; windows answer "which is first", "what changed" and "how much so far".
  • Anti-joins find what is missing; ROW_NUMBER finds the latest or best per group.
  • Cohorts combine a first-event table, activity per period and a distinct count.
  • DuckDB shortens many of these with QUALIFY, GROUP BY ALL, ANTI JOIN, FILTER and PIVOT.

Next: Back to the DuckDB track