lesson 38 Window functions · intermediate

Logical query processing & row grain

Follow query processing and track what one row represents.

the idea

SQL clauses are written SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY, but they are evaluated FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. That order explains which names are visible where and how each clause changes the grain. A window function computes across rows without collapsing them.

Dataset: Retail

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Filter, group, then sort. Run this. WHERE limits to sales, GROUP BY counts per customer, ORDER BY ranks by order count.the pattern

   example.sql Run

   sql›

   SELECT customer_id, COUNT(*) AS orders
   FROM orders
   WHERE order_type = 'sale'
   GROUP BY customer_id
   ORDER BY orders DESC
   LIMIT 5;

2. 2

   Keep every line and add a window total. Run this. COUNT(*) OVER (PARTITION BY order_id) adds how many lines each order has without collapsing rows.

   step-2.sql Run

   sql›

   SELECT
     order_item_id,
     order_id,
     COUNT(*) OVER (PARTITION BY order_id) AS lines_on_order
   FROM order_items
   WHERE order_id <= 3;

3. 3

   Filter groups after counting. Run this. HAVING keeps only customers with at least 50 sale orders after GROUP BY.

   step-3.sql Run

   sql›

   SELECT customer_id, COUNT(*) AS orders
   FROM orders
   WHERE order_type = 'sale'
   GROUP BY customer_id
   HAVING COUNT(*) >= 50
   ORDER BY orders DESC;

common mistake

GROUP BY collapses rows to one per group. To keep every line and still show a group total, use SUM(…) OVER (PARTITION BY …).