Average Sessions Per User

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

Domain

SQL

Difficulty

hard

Seniority

L3

Asked in

Google

Problem

The board deck needs a single engagement benchmark: the average number of sessions per user across the entire platform.

How often do users come back?

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

Why this problem exists in real interviews

The denominator is the whole question. user_sessions only contains users who actually had a session, so any average computed off this table is the engagement of active users, not the platform. If the board sees "4.2 sessions per user" and the inactive half of the user base is missing, that number is wrong for the deck it's going into.

Break down the requirements

Step 1: Count sessions per user

Inner query: COUNT(*) GROUP BY user_id. Every row in user_sessions is one session by construction (session_id is the grain), so COUNT(*) and COUNT(DISTINCT session_id) agree.

Step 2: Average the counts

Outer query averages those per-user counts. This is mathematically equivalent to COUNT(*) * 1.0 / COUNT(DISTINCT user_id) over the full table, and the one-pass form is dramatically cheaper at 200M rows.

Step 3: Confirm the denominator

If the deck wants average sessions across all registered users including zeros, you need a LEFT JOIN from users to user_sessions. The expected query treats inactive users as out of scope.

The solution

SELECT AVG(session_count) AS avg_sessions_per_user
FROM (
  SELECT user_id, COUNT(*) AS session_count
  FROM user_sessions
  GROUP BY user_id
)

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• How would you compute the same metric only for the trailing 30 days? (Tests partition pruning via a `session_start >= CURRENT_DATE - INTERVAL '30 days'` filter on the partition key.)
• How would you include registered users with zero sessions in the average? (Probes `LEFT JOIN` from a `users` dimension and `COALESCE(session_count, 0)`.)
• How would you report median sessions per user instead of the mean? (Tests `PERCENTILE_CONT(0.5)` and an understanding of why median is the better summary for a long-tailed engagement distribution.)

Subquery form (as written)

Two scans of intermediate rows: inner GROUP BY user_id builds a per-user count table, outer AVG reduces it. Readable, matches the spec exactly.

One-pass equivalent

SELECT COUNT(*) * 1.0 / COUNT(DISTINCT user_id) FROM user_sessions. Same answer, single aggregate, no intermediate materialization. At 200M rows the planner appreciates it.