# Daily Cross-Platform Users

> Mobile and web. Same day, same users?

Canonical URL: <https://datadriven.io/problems/daily_cross_platform_users>

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The growth team is tracking daily cross-platform engagement for mobile and web users. For each date, count the unique users who had at least one session on a 'mobile' or 'web' device. Show the date and the user count, chronologically.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests a JOIN across sessions and devices with date-level aggregation. The cross-platform requirement adds a device type filter to the join chain.

---

### Break down the requirements

#### Step 1: Join sessions to devices

Join `user_sessions` to `devices` on `device_id` to get `device_type`.

#### Step 2: Filter to mobile and web

`WHERE d.device_type IN ('mobile', 'web')` restricts to the target platforms.

#### Step 3: Aggregate per day

`GROUP BY session_start::DATE` with `COUNT(DISTINCT us.user_id)` for unique users.

---

### The solution

**Device-filtered daily user count**

```sql
SELECT
    us.session_start::DATE AS day,
    COUNT(DISTINCT us.user_id) AS unique_users
FROM user_sessions us
JOIN devices d ON us.device_id = d.device_id
WHERE d.device_type IN ('mobile', 'web')
GROUP BY us.session_start::DATE
ORDER BY day
```

> **Cost Analysis**
>
> Hash join of 50M sessions to 8M devices, filtered to two device types. The join reduces the working set significantly. COUNT(DISTINCT) per day operates on the filtered rows.

> **Interviewers Watch For**
>
> Whether the candidate joins on device_id (correct) vs user_id (incorrect for this schema). The device type information lives in the devices table, requiring a device-level join.

> **Common Pitfall**
>
> Joining on user_id would associate users with all their devices, not the specific device used in each session. The join must be on device_id to get the correct device type per session.

---

## Common follow-up questions

- How would you count users who used BOTH mobile and web on the same day? _(Tests HAVING COUNT(DISTINCT device_type) = 2 after grouping by user and day.)_
- What if device_type had 20 categories? _(The IN clause grows; tests whether to use a subquery or reference table.)_
- How would you trend this metric month-over-month? _(Change the GROUP BY to DATE_TRUNC('month', ...).)_

## 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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.