Session Rank

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Problem

Our session report numbers everyone's sessions in one continuous list, so nobody's longest session reliably comes out as number 1. From user_sessions, give each user's sessions a position starting at 1 for their longest and running to their shortest, leaving out sessions whose duration was never recorded.

Longest sessions rise to the top. Within each user, a pecking order.

Tables

user_sessions

Columns: session_id INTEGER, user_id INTEGER, device_id INTEGER, session_start TEXT, session_duration_sec INTEGER, pages_viewed INTEGER

Expected output

Worked solution and explanation

This is a per-user sequence dressed up as an engagement report. Anyone can sort sessions by duration; what separates candidates is making the count start over at 1 for every user and keeping sessions with no measured duration out of the count entirely. Number the whole table in one pass and only the user who owns the table's longest sessions starts at 1; everyone else's best session lands somewhere in a 183-row list. Leave the 17 unmeasured sessions in and they get positions too, rows the report never asked for.

Getting there

Step 1: Restart the count at every user_id

The report wants a position per user, so the numbering has to reset whenever user_id changes. PARTITION BY user_id is that reset: each user's sessions get their own 1, 2, 3, independent of everyone else's.

Step 2: Put the longest session first

ORDER BY session_duration_sec DESC inside the window decides which session takes position 1. It orders the numbering, not the output: the query has no outer sort and none is asked for, so rows may come back in any order as long as each carries the right position.

Step 3: Drop unmeasured sessions before numbering

17 rows have no session_duration_sec. Filtering them in WHERE keeps them out of the sequence entirely. Left in, they would be numbered after every measured session (or before them, on an engine that sorts NULL first under DESC), and the answer would carry rows the report never wanted.

SELECT
  user_id,
  session_duration_sec,
  ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY session_duration_sec DESC
  ) AS session_position
FROM user_sessions
WHERE session_duration_sec IS NOT NULL

One sequence for the table

ROW_NUMBER() OVER (ORDER BY session_duration_sec DESC) hands out 1 to 183 across all users. User 100 happens to own the three longest sessions in the table, so their first three positions look right, but their 2330-second session gets 22 instead of 4.

One sequence per user

PARTITION BY user_id restarts the count, so the same session is position 4: the fourth longest of user 100's own five measured sessions.

SELECT
  user_id,
  session_duration_sec,
  ROW_NUMBER() OVER (ORDER BY session_duration_sec DESC) AS session_position
FROM user_sessions
WHERE session_duration_sec IS NOT NULL

Common follow-up questions

• How would you keep only each user's three longest sessions? (Filtering on the computed `session_position` requires an outer query or `QUALIFY`, because a window result cannot sit in the same `WHERE`.)
• Two of a user's sessions last exactly 600 seconds. Which one gets position 1, and how do you make that repeatable? (Tests whether the candidate adds a deterministic tiebreaker such as `session_start` or `session_id`.)
• What changes if a missing duration should count as zero instead of being dropped? (Tests `COALESCE(session_duration_sec, 0)` and where those sessions land in the numbering.)