Average Session Duration

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

Meta

Problem

user_sessions holds one row per session with its length in session_duration_sec, and some sessions never recorded a length. Each user's average session duration is their total recorded session time divided by the number of their sessions that have a recorded length.

How long do users actually stay?

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

This is a weighted average carried through two levels of grouping, dressed up as an engagement metric. Rolling sessions up to (user_id, day) first is harmless. The second level is where candidates fail. Averaging the daily averages gives a one-session day the same weight as a fifty-session day. Counting sessions with no session_duration_sec inflates the denominator and adds no time. Get either wrong and each user's number drifts from total time over total sessions, and it drifts most for the heaviest users, whose days are the most uneven.

Break down the requirements

Step 1: Filter NULL durations first

Put WHERE session_duration_sec IS NOT NULL in the inner CTE. Without the filter, COUNT(*) counts those rows while SUM ignores them, and the denominator inflates. The counts and the sums must cover the same rows.

Step 2: Inner CTE: daily roll-up per user

Use GROUP BY user_id, date(session_start) with SUM(session_duration_sec) AS total_duration and COUNT(*) AS session_count. Daily grain reduces cardinality before the outer aggregate and lines up with the session_start partition. Keep the numerator and the denominator as separate additive columns so they can be summed again later.

Step 3: Outer CTE: divide sums, not average averages

SUM(total_duration) / SUM(session_count) GROUP BY user_id is the correct per-user mean. AVG(daily_avg) would be a flat average of days, weighting a one-session day the same as a fifty-session day.

Step 4: Cast for real division

Write CAST(SUM(total_duration) AS REAL) / SUM(session_count). Integer division silently floors the result in SQLite and Postgres, so cast the numerator.

The solution

WITH daily_sessions AS (
    SELECT user_id, date(session_start) AS day,
        SUM(session_duration_sec) AS total_duration,
        COUNT(*) AS session_count
    FROM user_sessions
    WHERE session_duration_sec IS NOT NULL
    GROUP BY user_id, date(session_start)
),
user_avg AS (
    SELECT user_id,
        CAST(SUM(total_duration) AS REAL) / SUM(session_count) AS avg_session_duration
    FROM daily_sessions
    GROUP BY user_id
)
SELECT user_id, avg_session_duration
FROM user_avg;

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• Why divide `SUM(total_duration) / SUM(session_count)` instead of `AVG(daily_avg)`? (Probes whether you can explain weighted vs unweighted means under aggregation.)
• Return only users with at least 10 sessions in the window. (Forces `HAVING SUM(session_count) >= 10` on the outer CTE, testing `HAVING` vs `WHERE` ordering.)
• Now compute the company-wide average of these per-user averages. (Layered aggregation; tests whether you wrap `user_avg` in a third stage rather than collapsing back to raw sessions.)
• How would you make this incremental, refreshing only yesterday's slice? (Opens partitioned incremental ETL: the daily CTE is already keyed for an `INSERT ... ON CONFLICT` upsert into a `user_daily_sessions` table.)