Platform Speed

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

The product team is investigating whether device type affects how long users stay engaged. For each device type with at least 35 logged sessions, show the typical session length, the longest single session on record, and how many sessions were logged.

Which devices keep users longest?

Tables

user_sessions

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

devices

Columns: device_id INTEGER, device_type TEXT, os_name TEXT, os_version TEXT, browser TEXT

Expected output

Worked solution and explanation

What this really tests

Under the session-analytics costume this is a post-aggregation filter: you keep only the device types whose session count clears a floor, and that count does not exist until the rows are joined, grouped and counted. Anyone can join user_sessions to devices and average the durations. The tell is where the '35 sessions' rule lands. A candidate who reaches for WHERE is filtering individual sessions, not groups, and either errors on the aggregate or silently keeps every device type. Get it wrong and a thinly-sampled platform like wearable, with only 33 sessions, rides into the ranking as if its average were as trustworthy as desktop's.

Break down the requirements

Step 1: Join sessions to devices

JOIN devices ON user_sessions.device_id = devices.device_id attaches device_type to each session so the aggregates can be grouped by platform.

Step 2: Group by device type and aggregate

GROUP BY device_type with AVG(session_duration_sec), MAX(session_duration_sec), and COUNT(*) produces the three per-platform metrics in one pass.

Step 3: Filter with HAVING

HAVING COUNT(*) >= 35 runs after the grouping and drops device types below the sample-size floor. This cannot be a WHERE clause: the count it tests does not exist until the group is formed.

Step 4: Order by average duration descending

ORDER BY avg_duration DESC surfaces the most-engaged platforms first, matching the requested top-to-bottom ranking.

The solution

SELECT
    d.device_type,
    AVG(s.session_duration_sec) AS avg_duration,
    MAX(s.session_duration_sec) AS max_duration,
    COUNT(*) AS session_count
FROM user_sessions s
JOIN devices d ON s.device_id = d.device_id
GROUP BY d.device_type
HAVING COUNT(*) >= 35

Common follow-up questions

• The `session_duration_sec` column has a 4% null rate. AVG and MAX ignore those rows but `COUNT(*)` still counts them toward the threshold. Is that the count you want, or should the threshold use `COUNT(session_duration_sec)`? (Tests whether the candidate accounts for NULLs in `user_sessions.session_duration_sec` and understands that AVG and MAX skip NULLs while COUNT(*) does not.)
• `user_sessions` has 500,000,000 rows. What index would let this group-and-aggregate avoid a full scan? (Tests indexing knowledge specific to the high-cardinality fact table.)
• If the sample-size floor became 'the top quartile of device types by session volume' instead of a fixed 35, how would you restructure the query? (Tests ability to replace a static HAVING threshold with a dynamic, data-driven one.)