# Daily Cross-Platform Users > Mobile and web. Same day, same users? Canonical URL: 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. ## Tables ### user_sessions - session_id INTEGER - user_id INTEGER - device_id INTEGER - session_start TEXT - session_duration_sec INTEGER - pages_viewed INTEGER Sample rows (10 of 200): | session_id | user_id | device_id | session_start | session_duration_sec | pages_viewed | | --- | --- | --- | --- | --- | --- | | 7061 | 197 | 230 | 2026-02-02 01:00:00 | 53 | 3 | | 7122 | 294 | 259 | 2026-03-03 02:00:00 | 76 | 6 | | 7183 | 391 | 288 | 2026-04-04 03:00:00 | 99 | 9 | | 7244 | 488 | 317 | 2026-05-05 04:00:00 | 122 | 12 | | 7305 | 585 | 346 | 2026-06-06 05:00:00 | 145 | 15 | | 7366 | 682 | 375 | 2026-07-07 06:00:00 | 168 | 18 | | 7427 | 779 | 404 | 2026-08-08 07:00:00 | 191 | 21 | | 7488 | 876 | NULL | 2026-09-09 08:00:00 | 214 | 24 | | 7549 | 973 | 462 | 2026-01-10 09:00:00 | 237 | 27 | | 7610 | 1070 | 491 | 2026-02-11 10:00:00 | 260 | 30 | ### devices - device_id INTEGER - device_type TEXT - os_name TEXT - os_version TEXT - browser TEXT Sample rows (10 of 200): | device_id | device_type | os_name | os_version | browser | | --- | --- | --- | --- | --- | | 201 | desktop | Android | 15.1 | Chrome | | 230 | tablet | Windows | 16.2 | Firefox | | 259 | smart_tv | macOS | 17.0 | Edge | | 288 | wearable | Linux | 11 | Opera | | 317 | mobile | tvOS | 12 | Samsung Internet | | 346 | desktop | watchOS | 13 | NULL | | 375 | tablet | iOS | 10.0.19041 | Safari | | 404 | smart_tv | Android | 14.1 | Chrome | | 433 | wearable | Windows | NULL | Firefox | | 462 | mobile | macOS | 14.0 | Edge | ## Expected output First 5 of 104 rows: | session_date | user_count | | --- | --- | | 2023-01-01 | 1 | | 2023-01-13 | 1 | | 2023-01-17 | 1 | | 2026-03-12 | 2 | | 2026-08-08 | 2 | ## 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** ```sql 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; ``` > **Sessions are not people** > > Swap `COUNT(DISTINCT us.user_id)` for `COUNT(*)` and 2026-06-15 jumps from 1 to 3: one viewer opened a desktop, a tablet, and a smart TV that day, so three sessions are really one person. Distinct-count is the whole point, not decoration. > **Row counts inflate the audience** > > `COUNT(*)` counts rows, and a busy viewer produces several rows a day. On any day with multi-screen usage the session count overstates the real audience. Only `COUNT(DISTINCT us.user_id)` answers the question that was asked. > **The key outlives the clock time** > > Grouping on the raw `session_start` timestamp instead of `DATE(session_start)` splits a single day into a row per session clock-time, so the daily rollup silently becomes per-session. Collapse to the calendar day in both the `GROUP BY` and the `ORDER BY`. > **Filter early, aggregate on less** > > Hash-join 50M sessions to 8M devices, filter to three device types, then a per-day `COUNT(DISTINCT us.user_id)`. The `device_type` predicate prunes the working set early, and the distinct aggregation runs over the pruned rows grouped by date. > **Distinct users, calendar day** > > Whether the candidate reaches for `COUNT(DISTINCT us.user_id)` unprompted and collapses the timestamp with `DATE(session_start)`. Both are the difference between a per-day audience and a per-session tally. --- ## Common follow-up questions - How would you find the users who used more than one of these screens on the same day? _(Tests HAVING COUNT(DISTINCT `device_type`) > 1 after grouping by user and day.)_ - What if the list of big-screen device types grew to twenty entries? _(The IN clause grows; tests whether to use a lookup/reference table instead of a literal list.)_ - How would you trend this metric month over month? _(Change the day bucket to a month bucket with strftime('%Y-%m', `session_start`).)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/daily_cross_platform_users) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.