Katkat
Back

Count Users Whose First Daily Event Is App Launch

SnapchatData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

Write a PostgreSQL query to identify each user's first event per calendar day and aggregate the distinct user count by operating system and device when that first event is an app launch. Tests window functions, tie-breaking logic, and multi-dimensional grouping.

Problem Statement

You are given an event telemetry table for a mobile application. For each calendar day, a user can trigger multiple events across different devices.

Write a PostgreSQL query to find the number of distinct users whose first event of the day was an 'app_launch'. Report the results grouped by the event date, operating system (os), and device_model associated with that initial event.

Table Schema: user_events

Column NameData TypeDescription
event_idBIGINTUnique identifier for the event
user_idINTUnique identifier for the user
event_nameVARCHARName of the event (e.g., 'app_launch', 'send_chat', 'open_camera')
event_timestampTIMESTAMPTimestamp when the event occurred
osVARCHAROperating system (e.g., 'iOS', 'Android')
device_modelVARCHARHardware model (e.g., 'iPhone 15', 'Pixel 8')

Requirements

  1. A calendar day is defined by the date portion of event_timestamp (event_timestamp::DATE).
  2. A user's "first event of the day" is the event with the earliest event_timestamp for that user on that calendar day. If multiple events share the exact same earliest timestamp for a user on that day, break the tie deterministically using the smallest event_id.
  3. Only include days, operating systems, and device models where there is at least one qualifying user.
  4. Output columns: event_date, os, device_model, user_count.
  5. Sort output by event_date ASC, os ASC, device_model ASC.

Example

Input (user_events)

+----------+---------+-------------+---------------------+---------+--------------+
| event_id | user_id | event_name  | event_timestamp     | os      | device_model |
+----------+---------+-------------+---------------------+---------+--------------+
| 101      | 1       | app_launch  | 2024-03-01 08:00:00 | iOS     | iPhone 15    |
| 102      | 1       | send_chat   | 2024-03-01 08:05:00 | iOS     | iPhone 15    |
| 103      | 2       | open_camera | 2024-03-01 09:00:00 | Android | Pixel 8      |
| 104      | 2       | app_launch  | 2024-03-01 09:10:00 | Android | Pixel 8      |
| 105      | 3       | app_launch  | 2024-03-01 07:30:00 | iOS     | iPhone 15    |
| 106      | 3       | app_launch  | 2024-03-02 10:00:00 | iOS     | iPhone 15    |
+----------+---------+-------------+---------------------+---------+--------------+

Output

+------------+-----+--------------+------------+
| event_date | os  | device_model | user_count |
+------------+-----+--------------+------------+
| 2024-03-01 | iOS | iPhone 15    | 2          |
| 2024-03-02 | iOS | iPhone 15    | 1          |
+------------+-----+--------------+------------+

Explanation:

  • On 2024-03-01:
    • User 1's first event is app_launch (iOS, iPhone 15).
    • User 2's first event is open_camera (excluded).
    • User 3's first event is app_launch (iOS, iPhone 15).
    • Distinct users with app_launch as their first event on iPhone 15: 2 (User 1 and User 3).
  • On 2024-03-02:
    • User 3's first event is app_launch (iOS, iPhone 15).
    • Count: 1.

Hints

Hint 1

Use ROW_NUMBER() partitioned by user_id and the casted calendar date, ordered by event_timestamp and event_id.

Hint 2

Filter for rows where the row number is 1 and the event_name equals 'app_launch' before performing the final aggregation.

Hint 3

Group by the date, os, and device_model columns, and compute COUNT(DISTINCT user_id) or COUNT(user_id).

Follow-up questions

  • How would you optimize this query if `user_events` contains billions of rows partitioned daily?
  • If a user can use both an iPhone and an Android on the same day, how would you ensure the first event across all devices is chosen versus the first event per device?
  • How does PostgreSQL's `DISTINCT ON (user_id, event_date)` compare in performance and syntax to `ROW_NUMBER()` for this specific use case?