Ordering Rows Before Grouping

Concepts covered: Row Numbering, Window Partitioning, Sorting Results

A table has no order. The rows of logins come back in whatever order the storage engine finds convenient, and 2 runs of the same query can return them differently. A run is a statement about which row comes after which, so until the query has imposed an order on the rows inside each user there are no runs to find: there is a bag of dates. ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) is the step that turns the bag into a sequence, and every later step depends on it being right.

Alice logged in from March 1 to March 3 and again on March 5, and Bob on March 2 and 3. Read as stored, nothing about these 6 rows says that Alice's first 3 dates belong together, and a row number handed out in storage order would mean nothing. The pattern needs the rows ordered by date within each user, and the query has to build that order itself.

Numbering across users is the bug

-- Wrong: 1 numbering across everyone
SELECT
  user_id,
  login_date,
  ROW_NUMBER() OVER (ORDER BY login_date) AS rn
FROM logins;

-- Right: numbering restarts for each user
SELECT
  user_id,
  login_date,
  ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY login_date
  ) AS rn
FROM logins;

On the left, Alice's March 3 login carries row number 4 because Bob's March 2 login took number 3. Her dates go up by 1 each time, but her row numbers skip from 2 to 4, so when section 3 subtracts one from the other the difference changes in the middle of a real streak, splitting it in 2. The query runs without an error and returns a wrong answer, which is the worst kind of bug in an interview because nothing prompts you to look for it. On the right, each user's numbering starts at 1 and climbs by 1 per login, which is the property the trick needs.

What ORDER BY inside OVER promises

The ORDER BY inside the OVER clause is the only one that matters to the run. It tells the engine the sequence the row numbers follow, within each partition, independent of how the rows are stored and of any ORDER BY at the end of the query. The final ORDER BY arranges the output for a human and has no effect on the numbering. Two different orders can appear in 1 query without conflict: number by login_date, then sort the result by streak_length DESC.

The direction matters too. Number ascending by the sequence column, so that the row number and the value climb together. Numbering descending while the value climbs makes the difference change on every row, and every row becomes its own island. If a question asks for streaks that are still running today, keep the ascending numbering and pick out the island whose end is today's date afterwards, rather than reversing the window.

The window runs after the WHERE clause, so a filter such as only mobile logins is applied before the numbering and the row numbers count only the kept rows. Apply the same filter in an outer query after the numbering and the removed rows take their row numbers with them: if Alice's March 2 login came from a laptop, March 1 keeps row number 1 and March 3 keeps row number 3, both give the same difference, and 2 days that are not consecutive merge into 1 streak. The row number skips exactly where the date skips, which hides the gap, so every filter on which rows take part belongs before the window, in the same ranked stage.

When the interviewer asks "what if 2 users log in on the same date", the answer is that the partition keeps them apart: Alice's March 2 and Bob's March 2 each take the number their own sequence gives them. The trouble starts when a single user has 2 rows on the same date, which the next lesson in this track covers in full. Here, say that you would use SELECT DISTINCT user_id, login_date to reduce the table to 1 row per user per day before numbering, then move on.

About this lesson

This section is part of the Gaps and Islands: 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.