lesson 38 Window functions · intermediate

Logical query processing & row grain

Follow query processing and track what one row represents.

the idea

Think FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. This is a logical model, not a promise about physical execution. Row expressions preserve rows, aggregates collapse them, and windows add context without collapsing.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

SELECT customer_id,SUM(quantity) AS units
FROM orders
WHERE status='paid'
GROUP BY customer_id
HAVING SUM(quantity)>2
ORDER BY units DESC;

Watch for: SQLite accepts some alias usages other engines reject. Prefer clear expressions or an outer query for portable SQL.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Start with rows. One row is one paid order line.

   step-1.sql Run

   sql›

   SELECT id,customer_id,quantity
   FROM orders
   WHERE status='paid';

2. 2

   Aggregate. Customers become the output grain.

   step-2.sql Run

   sql›

   SELECT customer_id,SUM(quantity) AS units
   FROM orders
   WHERE status='paid'
   GROUP BY customer_id;

3. 3

   Filter groups. HAVING works on the totals, then ordering shapes the output.

   step-3.sql Run

   sql›

   SELECT customer_id,SUM(quantity) AS units
   FROM orders
   WHERE status='paid'
   GROUP BY customer_id
   HAVING SUM(quantity)>2
   ORDER BY units DESC,customer_id

4. 4

   Keep individual rows instead. A window attaches the customer total while retaining order rows.

   step-4.sql Run

   sql›

   SELECT
     id,
     customer_id,
     quantity,
     SUM(quantity) OVER(PARTITION BY customer_id) AS customer_units
   FROM orders
   WHERE status='paid'