The Engagement Curve

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

L3

Asked in

Airbnb

Problem

Each row of user_sessions is one visit with its length in seconds and the pages it viewed, and a visit that never closed has no length logged and is filed with the over-30-minute visits. Place every visit in its length band and give each band's session count and average pages viewed, highest average first.

Longer sessions, more pages? Check.

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

What this really is

Beneath the 'do longer visits go deeper' framing, this is a bucketize-then-aggregate problem with a sting at the end. The CASE expression manufactures duration_bucket, a column the table does not have, and everything downstream groups by that label rather than raw session_duration_sec; group by the raw column and you get one row per distinct duration instead of five bands. The sting is the sort: the answer is ordered by avg_pages, highest first, not by the bands' natural short-to-long order. On this data '5_to_15min' tops the list at about 23.6 pages, ahead of 'over_30min' at 23.2, so a duration-ordered result asserts 'longer means deeper' and buries the one finding the question exists to surface.

Break down the requirements

Step 1: Define the duration buckets

Map session_duration_sec into named bands with a CASE expression, cutting with strict less-than tests (< 60, < 300, < 900, < 1800) so every boundary second lands in exactly one band. The ELSE arm catches everything 30 minutes and longer, and also the 17 sessions that never closed and have no duration logged: a NULL fails every comparison and falls through, which is exactly where the statement files them.

Step 2: Aggregate on the derived label

Group by the derived duration_bucket label, then compute COUNT(*) as session_count and AVG(pages_viewed) as avg_pages. Count rows, not durations: COUNT(session_duration_sec) skips the unclosed sessions and shrinks 'over_30min' from 76 to 59, while AVG still averages their pages.

Step 3: Sort highest average first

Order by avg_pages descending so the deepest-engagement band sits on top. The five averages are all different, so that one key settles the order; the bands' own short-to-long order plays no part in the answer.

The solution

SELECT
    CASE
        WHEN session_duration_sec < 60 THEN 'under_1min'
        WHEN session_duration_sec < 300 THEN '1_to_5min'
        WHEN session_duration_sec < 900 THEN '5_to_15min'
        WHEN session_duration_sec < 1800 THEN '15_to_30min'
        ELSE 'over_30min'
    END AS duration_bucket,
    COUNT(*) AS session_count,
    AVG(pages_viewed) AS avg_pages
FROM user_sessions
GROUP BY duration_bucket
ORDER BY avg_pages DESC

Common follow-up questions

• Product wants unclosed sessions, the ones with no `session_duration_sec`, reported in their own band instead of with '`over_30min`'. How does the CASE change, and which numbers move? (Tests whether they know a NULL falls through to ELSE and can make that case explicit.)
• Which bucket does a session of exactly 900 seconds fall into, and how does your CASE guarantee it is not counted twice? (Tests boundary reasoning and the ability to restate the half-open rule precisely.)
• This report runs hourly on 50M+ sessions. How would you avoid re-scanning the whole table every time? (Tests scaling instincts beyond a single query: incremental rollups and refresh cadence.)