Coding questions · Snapchat

Compute User Retention and Analyze Event Data

This question evaluates a candidate's ability to perform SQL and Python data manipulation for event analytics, including cohort retention, aggregation of distinct purchasing users, median-of-daily-events calculations, and hourly time-series grouping.

user_events

+---------+---------------------+------------+-------+
| user_id | event_time          | event_type | page  |
+---------+---------------------+------------+-------+
| 101     | 2023-04-01 10:00:00 | click      | home  |
| 101     | 2023-04-02 11:00:00 | purchase   | cart  |
| 102     | 2023-04-01 12:30:00 | click      | home  |
| 103     | 2023-04-03 09:15:00 | impression | deal  |
| 102     | 2023-04-04 14:20:00 | purchase   | cart  |
+---------+---------------------+------------+-------+

Scenario

Data team maintains a user_events fact table tracking every page view, click and purchase. Leadership wants ad-hoc SQL insights.

Question

Write a query to compute 7-day user retention (inclusive) for users who first visited on '2023-04-01'. Return the top 3 pages by distinct purchasing users last month. Find the median number of daily events per active user in April 2023. In Python, given a Pandas DataFrame identical to user_events, produce an hourly time-series of clicks per page for the past 24 hours.

Hints

Window functions, CTEs, DATE_DIFF and groupby should all be considered.

7-day cohort retention

You have a single table, user_events, that logs every user interaction on the product. Each user's first visit date is the date of their earliest event_time.

For the cohort of users whose first visit date is exactly 2023-04-01, compute the 7-day retention of that cohort.

A cohort user is considered retained if they generated at least one event on a day strictly after their first visit date and on or before first_visit_date + 6 days (i.e. within the 6-day window following the join day — the inclusive 7-day window excluding the join day itself).

Return exactly one row with the following columns, in this order:

cohort_date — the cohort's first-visit date (2023-04-01).
cohort_user_count — number of distinct users in the cohort.
retained_users_within_7d — number of distinct cohort users who were retained per the definition above.
retention_rate_7d — retained_users_within_7d / cohort_user_count, rounded to 4 decimal places.

Since the result is a single aggregate row, no ordering is required.

Table `user_events`

column	type	constraints
user_id	INTEGER	NOT NULL
event_time	TIMESTAMP	NOT NULL
event_type	VARCHAR	NOT NULL
page	VARCHAR	NOT NULL

Sample data:

event_time	event_type	page	user_id
2023-04-01 10:00:00	click	home	101
2023-04-02 11:00:00	purchase	cart	101
2023-04-01 12:30:00	click	home	102
2023-04-03 09:15:00	impression	deal	103
2023-04-04 14:20:00	purchase	cart	102
2023-04-01 08:00:00	click	home	104
2023-04-15 08:00:00	impression	deal	104

Expected output

A single row for the 2023-04-01 cohort: 3 users joined that day (101, 102, 104); 2 returned within the following 6 days (101 on 04-02, 102 on 04-04), while 104 only returned on 04-15 (outside the window), giving a retention rate of 0.6667.

cohort_date	cohort_user_count	retained_users_within_7d	retention_rate_7d
2023-04-01	3	2	0.6667

Hints

Derive each user's first visit date with MIN(event_time)::date, then keep only users whose first visit equals 2023-04-01.
In Postgres you can add days to a date directly: first_visit_date + 6. Require a return event strictly after day 0 and on/before that bound.
Use a LEFT JOIN from the cohort to the retained set so non-retained users still count toward the cohort size, and divide with ::numeric + NULLIF to avoid integer division and divide-by-zero.

Top pages by purchasing users

Return the top 3 pages by the number of distinct users who made a purchase event during April 2023. Treat April as the half-open timestamp range [2023-04-01, 2023-05-01). Return page and distinct_purchasing_users, ordered by purchasing users descending and page ascending.

Table `user_events`

column	type	constraints
user_id	INTEGER	NOT NULL
event_time	TIMESTAMP	NOT NULL
event_type	VARCHAR	NOT NULL
page	VARCHAR	NOT NULL

Sample data:

event_time	event_type	page	user_id
2023-04-01 10:00:00	click	home	101
2023-04-02 11:00:00	purchase	cart	101
2023-04-01 12:30:00	click	home	102
2023-04-03 09:15:00	impression	deal	103
2023-04-04 14:20:00	purchase	cart	102

Expected output

A ranked list (up to 3 rows) of pages by the number of distinct users who made a purchase on that page in April 2023.

distinct_purchasing_users	page
2	cart

Hints

Filter to event_type = 'purchase'.
Count distinct users per page within April 2023.

Median daily events per user

Median daily events per active user-day

You are given a single table, user_events, where each row records one event a user performed at a given timestamp.

An active user-day is a (user_id, calendar date) pair on which that user logged at least one event. For each active user-day, the number of events is the count of rows for that user on that date.

Restrict to April 2023 (events with event_time on a date from 2023-04-01 through 2023-04-30, inclusive). Across all active user-days in that window, compute the median of the per-user-day event counts. Use the continuous median (PERCENTILE_CONT(0.5)) so an even number of user-days averages the two middle values.

Return a single row with one column:

median_daily_events_per_active_user — the median number of events per active user-day in April 2023.

Table `user_events`

column	type	constraints
user_id	INTEGER	NOT NULL
event_time	TIMESTAMP	NOT NULL
event_type	VARCHAR	NOT NULL
page	VARCHAR	NOT NULL

Sample data:

event_time	event_type	page	user_id
2023-04-01 10:00:00	click	home	101
2023-04-01 11:00:00	purchase	cart	101
2023-04-02 09:00:00	click	home	101
2023-04-01 12:30:00	click	home	102
2023-04-03 09:15:00	impression	deal	103
2023-04-04 14:20:00	purchase	cart	102
2023-04-04 15:00:00	click	home	102
2023-05-01 09:15:00	impression	deal	103

Expected output

A single-row result with the median number of events per active user-day in April 2023.

median_daily_events_per_active_user
1

Hints

First collapse the raw events into one row per (user_id, date) with a COUNT(*) of events on that day.
Use PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY events_on_day) for the median in PostgreSQL.
Filter on (event_time)::date BETWEEN the April bounds so events outside the month are excluded.

Hourly Clicks per Page in SQL

Using the user_events table, return an hourly time series of click counts per page for the 24 hourly buckets from 2025-05-31 01:00:00 through 2025-06-01 00:00:00 inclusive.

The output must include every combination of hour bucket and page found in user_events, even when that page has zero clicks in that hour. Count only rows where event_type = 'click'. Assign each event to an hour with date_trunc('hour', event_time).

Return exactly these columns:

hour_start: the hourly bucket timestamp
page: the page name
clicks: the number of click events for that page in that hour

Sort the result by hour_start, then page.

Table `user_events`

column	type	constraints
user_id	INTEGER	NOT NULL
event_time	TIMESTAMP	NOT NULL
event_type	VARCHAR	NOT NULL
page	VARCHAR	NOT NULL

Sample data:

event_time	event_type	page	user_id
2025-05-31 00:30:00	click	home	101
2025-05-31 01:15:00	click	home	101
2025-05-31 01:45:00	click	home	102
2025-05-31 01:20:00	purchase	cart	103
2025-05-31 02:05:00	click	cart	104
2025-05-31 03:10:00	impression	home	105
2025-06-01 00:00:00	click	home	107
2025-06-01 01:05:00	click	cart	108

Expected output

Hourly click counts for each page across the 24 hourly buckets from 2025-05-31 01:00:00 through 2025-06-01 00:00:00 inclusive.

clicks	hour_start	page
0	2025-05-31T01:00:00	cart
2	2025-05-31T01:00:00	home
1	2025-05-31T02:00:00	cart
0	2025-05-31T02:00:00	home
0	2025-05-31T03:00:00	cart
0	2025-05-31T03:00:00	home
0	2025-05-31T04:00:00	cart
0	2025-05-31T04:00:00	home
0	2025-05-31T05:00:00	cart
0	2025-05-31T05:00:00	home
0	2025-05-31T06:00:00	cart
0	2025-05-31T06:00:00	home
0	2025-05-31T07:00:00	cart
0	2025-05-31T07:00:00	home
0	2025-05-31T08:00:00	cart
0	2025-05-31T08:00:00	home
0	2025-05-31T09:00:00	cart
0	2025-05-31T09:00:00	home
0	2025-05-31T10:00:00	cart
0	2025-05-31T10:00:00	home
0	2025-05-31T11:00:00	cart
0	2025-05-31T11:00:00	home
0	2025-05-31T12:00:00	cart
0	2025-05-31T12:00:00	home
0	2025-05-31T13:00:00	cart
0	2025-05-31T13:00:00	home
0	2025-05-31T14:00:00	cart
0	2025-05-31T14:00:00	home
0	2025-05-31T15:00:00	cart
0	2025-05-31T15:00:00	home
0	2025-05-31T16:00:00	cart
0	2025-05-31T16:00:00	home
0	2025-05-31T17:00:00	cart
0	2025-05-31T17:00:00	home
0	2025-05-31T18:00:00	cart
0	2025-05-31T18:00:00	home
0	2025-05-31T19:00:00	cart
0	2025-05-31T19:00:00	home
0	2025-05-31T20:00:00	cart
0	2025-05-31T20:00:00	home
0	2025-05-31T21:00:00	cart
0	2025-05-31T21:00:00	home
0	2025-05-31T22:00:00	cart
0	2025-05-31T22:00:00	home
0	2025-05-31T23:00:00	cart
0	2025-05-31T23:00:00	home
0	2025-06-01T00:00:00	cart
1	2025-06-01T00:00:00	home

Hints

Use generate_series to create the 24 hourly buckets.
Cross join the generated hours with the distinct pages so zero-click combinations are present.
Aggregate only event_type = 'click' rows after truncating event_time to the hour.

Scenario

Question

Hints

7-day cohort retention

Table user_events

Expected output

Hints

Top pages by purchasing users

Table user_events

Expected output

Hints

Median daily events per user

Median daily events per active user-day

Table user_events

Expected output

Hints

Hourly Clicks per Page in SQL

Table user_events

Expected output

Hints

Table `user_events`

Table `user_events`

Table `user_events`

Table `user_events`