Quick Overview

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.

Compute User Retention and Analyze Event Data

Company: Snapchat

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Onsite

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.

Overview: 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.

7-day cohort retention

## 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.

Tables

user_events(user_id INTEGER, event_time TIMESTAMP, event_type VARCHAR, page VARCHAR)

Hints

  1. Derive each user's first visit date with MIN(event_time)::date, then keep only users whose first visit equals 2023-04-01.
  2. 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.

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.

Tables

user_events(user_id INTEGER, event_time TIMESTAMP, event_type VARCHAR, page VARCHAR)

Hints

  1. Filter to event_type = 'purchase'.
  2. 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.

Tables

user_events(user_id INTEGER, event_time TIMESTAMP, event_type VARCHAR, page VARCHAR)

Hints

  1. First collapse the raw events into one row per (user_id, date) with a COUNT(*) of events on that day.
  2. Use PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY events_on_day) for the median in PostgreSQL.

Hourly Clicks per Page in SQL

### 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`.

Tables

user_events(user_id INTEGER, event_time TIMESTAMP, event_type VARCHAR, page VARCHAR)

Hints

  1. Use `generate_series` to create the 24 hourly buckets.
  2. Cross join the generated hours with the distinct pages so zero-click combinations are present.

Loading coding console...