Daily Cross-Platform Users

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

Domain
SQL
Difficulty
easy
Seniority
L3
Asked in
Meta

Problem

The growth team tracks daily engagement on the big-screen apps: the sessions that ran on a desktop, tablet, or smart TV. For each date, count the unique users who opened at least one such session, oldest day first.

Mobile and web. Same day, same users?

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

devices

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

devices: 10 of 200 rows
device_iddevice_typeos_nameos_versionbrowser
201desktopAndroid15.1Chrome
230tabletWindows16.2Firefox
259smart_tvmacOS17.0Edge
288wearableLinux11Opera
317mobiletvOS12Samsung Internet
346desktopwatchOS13NULL
375tabletiOS10.0.19041Safari
404smart_tvAndroid14.1Chrome
433wearableWindowsNULLFirefox
462mobilemacOS14.0Edge

Expected output

First 5 of 104 rows
session_dateuser_count
2023-01-011
2023-01-131
2023-01-171
2026-03-122
2026-08-082

Worked solution and explanation

What this problem is really about

This is a distinct-count over a filtered join wearing a daily-engagement costume. The skill being probed: collapse session_start to a calendar day, keep only the big-screen devices, and count PEOPLE, not sessions. Anyone can join user_sessions to devices and group by day. The trap is COUNT(*): on a day when one viewer opens a desktop, a tablet, and a smart TV, COUNT(*) reports 3 users when the honest answer is 1. COUNT(DISTINCT us.user_id) is what turns sessions back into distinct people.

Break down the requirements

Step 1: Join sessions to devices

Inner-join user_sessions to devices on device_id so each session carries its device_type. The inner join also drops the sessions whose device_id is NULL: no device means it can't be one of these screens.

Step 2: Filter to the big screens

Keep only sessions where device_type is in ('desktop', 'tablet', 'smart_tv'), the big-screen apps the growth team cares about.

Step 3: Aggregate per day

Bucket each session by its calendar date with DATE(session_start), then count distinct users per day with COUNT(DISTINCT us.user_id) so a viewer who used several screens counts once.

The solution

Big-screen daily user count
SELECT DATE(us.session_start) AS session_date,
       COUNT(DISTINCT us.user_id) AS user_count
FROM user_sessions us
JOIN devices d ON us.device_id = d.device_id
WHERE d.device_type IN ('desktop', 'tablet', 'smart_tv')
GROUP BY DATE(us.session_start)
ORDER BY session_date;

Common follow-up questions