Each Group Against Its Own Past

Concepts covered: Window Partitioning, Row Offsets

'Per region' changes the question from 1 trend to 1 trend for each region, and each of those trends is measured against that region's own history. East in February is compared with East in January, never with West in January and never with the company's January total. That sounds too plain to need saying, and yet the most common wrong output in this round is a table where the first West row shows a prior value that belongs to East. It happens because the rollup produced 1 list of rows, and 'the previous row' in 1 list crosses from the end of one region into the start of the next.

Six rows, 2 regions. Read down the list and the row above West's January is East's March at 1100. A window with no partition hands that 1100 to West's January as its prior, and the report says West fell from 1100 to 1000 in a month where West had no earlier month at all.

What PARTITION BY Draws

PARTITION BY region inside the OVER clause splits that 1 list into 1 list per region, and the window function runs separately inside each. LAG looking back from West's January finds nothing, because nothing in West's list comes before it, and returns NULL. Looking back from West's February it finds West's January, 1000, which is the figure the question wants. The partition is the SQL spelling of 'against its own past', and saying that sentence as you type the clause tells the interviewer that the clause is deliberate.

SELECT
  region,
  month_start,
  revenue,
  LAG(revenue) OVER (
    PARTITION BY region
    ORDER BY month_start
  ) AS prior_revenue
FROM monthly;

Two NULLs now, 1 at the top of each region, where the unpartitioned version had 1. That second NULL is the tell: when the interviewer asks 'how many rows have no prior', the answer is 'one per group, because each group's first period has nothing before it', and the number of groups in the data is the number of blanks.

The Grain Follows the Question

The partition list is whatever the question says 'per' or 'by'. 'Per region and product' means PARTITION BY region, product, and the rollup's GROUP BY carries the same 2 columns, because the rollup and the comparison have to agree on what 1 row is. A rollup grouped by region alone and a window partitioned by region and product would hand the window rows where product does not exist, so the pair is always written together: the columns after GROUP BY in the rollup are the columns after PARTITION BY in the window, with the period column left out of the partition and placed in ORDER BY.

SELECT region, product, month_start, revenue,
  LAG(revenue) OVER (PARTITION BY region, product
                     ORDER BY month_start) AS prior_revenue
FROM monthly

The animation's monthly table carries 3 rows for East: Desk in January at 1000, Desk in February at 1250, and Lamp in February at 300. With PARTITION BY region, product, Desk's February row receives 1000 as its prior, because Desk's January is the row before it inside the Desk sequence. Lamp's February row receives NULL, because Lamp has no January row at all. A partition on region alone would have handed Lamp whichever East row the tie on 2024-02-01 happens to place before it, Desk's February at 1250 or Desk's January at 1000. Both are another product's revenue, so the 76% or 70% collapse it reports is fiction either way.

A different follow-up probes whether you understand that the window does not reduce rows. GROUP BY collapses many rows into 1; PARTITION BY keeps every row and only decides which neighbours a row can see. The output of the comparison step has the same 6 rows as monthly, 1 new column wider. Candidates who expect the partition to produce 1 row per region have confused the 2 clauses, and the interviewer will ask for the row count to find out.

GROUP BY region

• Collapses rows: 1 output row per region
• Columns outside the group list must be aggregated
• Lives in the rollup step

PARTITION BY region

• Keeps rows: output has as many rows as input
• Every column stays available on each row
• Lives in the comparison step

About this lesson

This section is part of the Period-over-Period: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.