Low-Engagement Sessions

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

Problem

Which users average less than 1,000 seconds per session across everything they have in user_sessions, and what is each one's average, for the retention team's weak-engagement list?

Users whose average session duration is below the engagement threshold

Tables

user_sessions

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

user_sessions: 10 of 200 rows
session_iduser_iddevice_idsession_startsession_duration_secpages_viewed
70611972302026-02-02 01:00:00533
71222942592026-03-03 02:00:00766
71833912882026-04-04 03:00:00999
72444883172026-05-05 04:00:0012212
73055853462026-06-06 05:00:0014515
73666823752026-07-07 06:00:0016818
74277794042026-08-08 07:00:0019121
7488876NULL2026-09-09 08:00:0021424
75499734622026-01-10 09:00:0023727
761010704912026-02-11 10:00:0026030

Expected output

First 5 of 12 rows
user_idavg_duration
197973
294996
488888.6666666666666
2331600
2719600

Worked solution and explanation

This is a filter on a per-user summary dressed up as a retention question. The 1,000-second line belongs to each user's average across all their rows in user_sessions, not to any single session, so the test can only run after sessions collapse to one row per user_id. Anyone can write AVG(session_duration_sec). What separates candidates is where the < 1000 goes: put it in WHERE and it trims long sessions before any average exists, so most users come back with the average of their short sessions instead of the 12 users whose whole history averages low.

Getting to the answer

Step 1: Settle the grain first

The output is one row per user, so user_id is the only key. Users here have between one and eight sessions, and the average has to see every one of them before anyone is judged.

Step 2: Average each user's sessions once

AVG(session_duration_sec) per user_id. Seventeen sessions have no recorded duration; AVG skips them in both the sum and the count, so the average reflects only sessions that were actually timed.

Step 3: Test the threshold after the average exists

HAVING AVG(session_duration_sec) < 1000 runs on the grouped rows, the only place the per-user average is visible. WHERE sees one session at a time and has no average to compare.

Users averaging under 1,000 seconds per session
SELECT user_id, AVG(session_duration_sec) AS avg_duration
FROM user_sessions
GROUP BY user_id
HAVING AVG(session_duration_sec) < 1000
Filter sessions, then average
WHERE session_duration_sec < 1000 drops every long session first. A user with sessions of 400 and 3,000 seconds really averages 1,700, but shows up here at 400.
Average, then filter users
HAVING AVG(session_duration_sec) < 1000 keeps both sessions in the average, sees 1,700, and leaves that user out.
The row-level version
SELECT user_id, AVG(session_duration_sec) AS avg_duration
FROM user_sessions
WHERE session_duration_sec < 1000
GROUP BY user_id

Wrong: it averages only the short sessions, so a long session can never disqualify anyone.

Common follow-up questions