Coding questions · The New York Times
Write SQL for content-view analytics
This question evaluates proficiency in SQL-based data manipulation and analytics, including aggregation, distinct counting, NULL handling, timestamp/hour extraction, device grouping, and row-level ordering, and is situated in the Data Manipulation (SQL/Python) domain.
Context
You work with page-view event logs and need to compute several engagement/usage summaries.
Assume a single table:
page_views
| column | type | description |
|---|---|---|
view_id | STRING/INT | unique event id (PK) |
agent_id | STRING/INT | identifier for the viewer ("agent") |
content_id | STRING/INT | identifier of the content item (nullable) |
url | STRING | URL viewed |
content_type | STRING | e.g., article, video, podcast, etc. |
device_type | STRING | one of phone, tab, desktop |
viewed_at | TIMESTAMP | event timestamp |
Assumptions:
- Treat mobile as
device_type IN ('phone','tab'), and desktop asdevice_type = 'desktop'. - Unless otherwise specified, interpret timestamps in UTC.
Tasks (write SQL)
1) Unique contents viewed by device group
Compute, for each device group (mobile vs desktop):
unique_contents_viewed: number of distinctcontent_idvalues viewed (ignore NULLs)total_views: total number of page-view events
Return columns: device_group, unique_contents_viewed, total_views.
Additionally (in 1–2 sentences), explain whether unique_contents_viewed and total_views will generally be equal, and why.
2) Peak usage hours by device type
For each device type (phone, tab, desktop), find the top 3 hours of day (0–23) with the highest number of views.
Return columns: device_type, hour_of_day, views.
Notes:
- Use an hour-extraction function on
viewed_at. - Break ties by choosing the smaller
hour_of_dayfirst.
3) Unique content count and NULL content count
Compute:
unique_contents_viewed: number of distinct non-NULLcontent_idvalues that were viewednull_content_events: number of events wherecontent_idIS NULL
Return columns: unique_contents_viewed, null_content_events.
4) Distribution of first-seen content type per agent per day
For each agent_id and each calendar date (derived from viewed_at), identify the first page view of that day (earliest viewed_at; if ties, use smallest view_id). Take that event’s content_type.
Then compute the distribution of these first-seen content_type values across all agent-days.
Return columns:
event_datecontent_typeagent_days(count of agent-days whose first view had thatcontent_type)share(agent_days divided by total agent-days for that date)
Unique content viewed on Mobile vs Desktop
You are given a table of content view events. Treat device_type IN ('phone','tablet') as 'mobile' and device_type = 'desktop' as 'desktop'.
Return one row per device_group with:
- the number of UNIQUE contents viewed (COUNT(DISTINCT content_id), ignoring NULL content_id automatically), and
- the total number of view events.
This helps illustrate how "unique contents viewed" differs from "number of views".
Table content_views
| column | type | constraints |
|---|---|---|
| view_id | INT | PRIMARY KEY, NOT NULL |
| agent_id | INT | NOT NULL |
| device_type | VARCHAR(10) | NOT NULL |
| content_id | INT | |
| url | VARCHAR(100) | NOT NULL |
| content_type | VARCHAR(20) | NOT NULL |
| viewed_at | TIMESTAMP | NOT NULL |
Sample data:
| agent_id | content_id | content_type | device_type | url | view_id | viewed_at |
|---|---|---|---|---|---|---|
| 101 | 1 | News | phone | /news/1 | 1 | 2025-05-30 08:05:00 |
| 101 | 2 | Sports | desktop | /sports/2 | 2 | 2025-05-30 08:20:00 |
| 101 | 1 | News | phone | /news/1 | 3 | 2025-05-30 09:00:00 |
| 101 | 3 | Opinion | tablet | /opinion/3 | 4 | 2025-05-30 18:10:00 |
| 101 | NULL | Live | desktop | /live | 5 | 2025-05-31 07:50:00 |
| 101 | 4 | News | desktop | /news/4 | 6 | 2025-05-31 08:10:00 |
| 101 | 5 | Arts | phone | /arts/5 | 7 | 2025-05-31 20:00:00 |
| 102 | 2 | Sports | tablet | /sports/2 | 8 | 2025-05-30 08:15:00 |
| 102 | 6 | News | desktop | /news/6 | 9 | 2025-05-30 10:05:00 |
| 102 | 6 | News | desktop | /news/6 | 10 | 2025-05-30 10:20:00 |
| 102 | NULL | Unknown | phone | /unknown | 11 | 2025-05-30 22:00:00 |
| 102 | 7 | News | phone | /news/7 | 12 | 2025-05-31 08:00:00 |
| 102 | 8 | Opinion | phone | /opinion/8 | 13 | 2025-05-31 08:05:00 |
| 102 | 2 | Sports | desktop | /sports/2 | 14 | 2025-05-31 12:00:00 |
| 103 | 9 | News | desktop | /news/9 | 15 | 2025-05-30 18:00:00 |
| 103 | NULL | Unknown | desktop | /404 | 16 | 2025-05-30 18:30:00 |
| 103 | 3 | Opinion | tablet | /opinion/3 | 17 | 2025-05-31 07:55:00 |
| 103 | 3 | Opinion | tablet | /opinion/3 | 18 | 2025-05-31 08:55:00 |
| 103 | 10 | Arts | desktop | /arts/10 | 19 | 2025-05-31 18:10:00 |
Expected output
One row per device_group ('mobile', 'desktop') showing unique content count (distinct content_id) and total view events.
| device_group | total_views | unique_contents |
|---|---|---|
| desktop | 9 | 5 |
| mobile | 10 | 6 |
Hints
- Use CASE WHEN to map phone+tablet into a single 'mobile' group.
- COUNT(DISTINCT content_id) ignores NULL by default; COUNT(*) counts all events.
Peak usage hour by device group
Using the same content view events table, find the peak usage hour for each device group.
Rules:
- device_type IN ('phone','tablet') => device_group = 'mobile'
- device_type = 'desktop' => device_group = 'desktop'
- Extract the hour from viewed_at.
Return the hour(s) with the maximum number of view events per device_group. If there is a tie, return all tied hours.
Table content_views
| column | type | constraints |
|---|---|---|
| view_id | INT | PRIMARY KEY, NOT NULL |
| agent_id | INT | NOT NULL |
| device_type | VARCHAR(10) | NOT NULL |
| content_id | INT | |
| url | VARCHAR(100) | NOT NULL |
| content_type | VARCHAR(20) | NOT NULL |
| viewed_at | TIMESTAMP | NOT NULL |
Sample data:
| agent_id | content_id | content_type | device_type | url | view_id | viewed_at |
|---|---|---|---|---|---|---|
| 101 | 1 | News | phone | /news/1 | 1 | 2025-05-30 08:05:00 |
| 101 | 2 | Sports | desktop | /sports/2 | 2 | 2025-05-30 08:20:00 |
| 101 | 1 | News | phone | /news/1 | 3 | 2025-05-30 09:00:00 |
| 101 | 3 | Opinion | tablet | /opinion/3 | 4 | 2025-05-30 18:10:00 |
| 101 | NULL | Live | desktop | /live | 5 | 2025-05-31 07:50:00 |
| 101 | 4 | News | desktop | /news/4 | 6 | 2025-05-31 08:10:00 |
| 101 | 5 | Arts | phone | /arts/5 | 7 | 2025-05-31 20:00:00 |
| 102 | 2 | Sports | tablet | /sports/2 | 8 | 2025-05-30 08:15:00 |
| 102 | 6 | News | desktop | /news/6 | 9 | 2025-05-30 10:05:00 |
| 102 | 6 | News | desktop | /news/6 | 10 | 2025-05-30 10:20:00 |
| 102 | NULL | Unknown | phone | /unknown | 11 | 2025-05-30 22:00:00 |
| 102 | 7 | News | phone | /news/7 | 12 | 2025-05-31 08:00:00 |
| 102 | 8 | Opinion | phone | /opinion/8 | 13 | 2025-05-31 08:05:00 |
| 102 | 2 | Sports | desktop | /sports/2 | 14 | 2025-05-31 12:00:00 |
| 103 | 9 | News | desktop | /news/9 | 15 | 2025-05-30 18:00:00 |
| 103 | NULL | Unknown | desktop | /404 | 16 | 2025-05-30 18:30:00 |
| 103 | 3 | Opinion | tablet | /opinion/3 | 17 | 2025-05-31 07:55:00 |
| 103 | 3 | Opinion | tablet | /opinion/3 | 18 | 2025-05-31 08:55:00 |
| 103 | 10 | Arts | desktop | /arts/10 | 19 | 2025-05-31 18:10:00 |
Expected output
One or more rows per device_group listing the hour(s) with the maximum number of views and the corresponding count.
| device_group | view_count | view_hour |
|---|---|---|
| desktop | 3 | 18 |
| mobile | 5 | 8 |
Hints
- Aggregate counts by device_group and extracted hour first.
- Use DENSE_RANK (or MAX() + join) to keep all ties for the peak.
Count unique contents viewed and NULL content_id events
Using the content view events table, compute:
- the number of unique contents viewed (distinct non-NULL content_id), and
- the number of view events where content_id is NULL.
Return a single row with these two metrics.
Table content_views
| column | type | constraints |
|---|---|---|
| view_id | INT | PRIMARY KEY, NOT NULL |
| agent_id | INT | NOT NULL |
| device_type | VARCHAR(10) | NOT NULL |
| content_id | INT | |
| url | VARCHAR(100) | NOT NULL |
| content_type | VARCHAR(20) | NOT NULL |
| viewed_at | TIMESTAMP | NOT NULL |
Sample data:
| agent_id | content_id | content_type | device_type | url | view_id | viewed_at |
|---|---|---|---|---|---|---|
| 101 | 1 | News | phone | /news/1 | 1 | 2025-05-30 08:05:00 |
| 101 | 2 | Sports | desktop | /sports/2 | 2 | 2025-05-30 08:20:00 |
| 101 | 1 | News | phone | /news/1 | 3 | 2025-05-30 09:00:00 |
| 101 | 3 | Opinion | tablet | /opinion/3 | 4 | 2025-05-30 18:10:00 |
| 101 | NULL | Live | desktop | /live | 5 | 2025-05-31 07:50:00 |
| 101 | 4 | News | desktop | /news/4 | 6 | 2025-05-31 08:10:00 |
| 101 | 5 | Arts | phone | /arts/5 | 7 | 2025-05-31 20:00:00 |
| 102 | 2 | Sports | tablet | /sports/2 | 8 | 2025-05-30 08:15:00 |
| 102 | 6 | News | desktop | /news/6 | 9 | 2025-05-30 10:05:00 |
| 102 | 6 | News | desktop | /news/6 | 10 | 2025-05-30 10:20:00 |
| 102 | NULL | Unknown | phone | /unknown | 11 | 2025-05-30 22:00:00 |
| 102 | 7 | News | phone | /news/7 | 12 | 2025-05-31 08:00:00 |
| 102 | 8 | Opinion | phone | /opinion/8 | 13 | 2025-05-31 08:05:00 |
| 102 | 2 | Sports | desktop | /sports/2 | 14 | 2025-05-31 12:00:00 |
| 103 | 9 | News | desktop | /news/9 | 15 | 2025-05-30 18:00:00 |
| 103 | NULL | Unknown | desktop | /404 | 16 | 2025-05-30 18:30:00 |
| 103 | 3 | Opinion | tablet | /opinion/3 | 17 | 2025-05-31 07:55:00 |
| 103 | 3 | Opinion | tablet | /opinion/3 | 18 | 2025-05-31 08:55:00 |
| 103 | 10 | Arts | desktop | /arts/10 | 19 | 2025-05-31 18:10:00 |
Expected output
A single row with two columns: distinct non-NULL content_id count and the number of rows with NULL content_id.
| null_content_views | unique_contents |
|---|---|
| 3 | 10 |
Hints
- COUNT(DISTINCT ...) will ignore NULLs, but SUM(CASE WHEN ... IS NULL ...) will count them.
- Return a single row (no GROUP BY needed).