SQL ORDER BY in a Window Function

Define analytic row sequence independently from final result sorting.

What & Why

ORDER BY inside OVER controls window order for ranking, offsets, and frames; the query's outer ORDER BY controls display order. An explicit analytic sequence is required for meaningful ranks and offsets, even when the result is displayed in a different order.

See How It Works

BUSINESS QUESTION

Growth numbers user signups chronologically but displays the newest signup first.

id	created_at	country	channel	plan	activated_at	churned_at
101	2024-01-15 09:10:00+00	US	organic	pro	2024-01-16 14:25:00+00	NULL
102	2024-02-10 11:05:00+00	CA	paid	free	NULL	2024-03-20 10:00:00+00
103	2024-03-12 16:35:00+00	GB	referral	pro	2024-03-13 08:15:00+00	NULL
104	2024-04-01 13:20:00+00	US	organic	free	2024-04-03 12:00:00+00	NULL

EXAMPLE QUERY

SELECT
  id AS user_id,
  created_at,
  ROW_NUMBER() OVER (ORDER BY created_at, id) AS signup_sequence
FROM growth.users
ORDER BY created_at DESC, user_id DESC;

RESULT — sequence calculated, newest displayed first

user_id	created_at	signup_sequence
104	2024-04-01 13:20:00+00	4
103	2024-03-12 16:35:00+00	3
102	2024-02-10 11:05:00+00	2
101	2024-01-15 09:10:00+00	1

The window order assigns the sequence independently of the final descending display order.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice