Write SQL for content view metrics by device
Company: Newyorktimes
Role: Data Analyst
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
# Write SQL for content view metrics by device
You are a reporting analyst for a news subscription product. You have a fact table of page/content view events.
Assume the following table in Google BigQuery (Standard SQL):
`page_views` (one row per view event)
- `event_id` STRING (unique)
- `agent_id` STRING (an identifier for the reader/user agent)
- `device_type` STRING (one of: 'phone', 'tab', 'desktop')
- `content_id` STRING (nullable; may be NULL if not resolved)
- `url` STRING
- `content_type` STRING (e.g., 'article', 'video', 'interactive', etc.; may be NULL)
- `event_ts` TIMESTAMP (UTC)
Definitions:
- “Mobile” = device_type IN ('phone','tab')
- “Desktop” = device_type = 'desktop'
- A “unique content view” means counting DISTINCT `content_id` values (NULLs should not be counted as a distinct content).
Write SQL queries to answer each of the following:
1) Mobile vs Desktop unique contents viewed:
- Return two rows (mobile, desktop) with the number of unique contents viewed.
- Also include total view events for each device group so it’s clear how “# of unique contents viewed” differs from “# of views”.
- Output columns: `device_group`, `unique_contents_viewed`, `total_views`.
2) Peak usage hours by device group:
- For each device group (mobile, desktop), find the top 3 hours of day (0–23) with the most view events.
- Use `EXTRACT(HOUR FROM event_ts)`.
- Output columns: `device_group`, `hour_of_day`, `views`, `hour_rank`.
3) Content ID coverage:
- Compute (a) how many unique non-NULL contents were viewed and (b) how many view events have `content_id` IS NULL.
- Output columns: `unique_nonnull_contents_viewed`, `null_content_view_events`.
4) Distribution of first content type per agent per day:
- For each `agent_id` and calendar date (UTC), find that agent’s first view event of the day (earliest `event_ts`).
- Take the `content_type` of that first event and compute the overall distribution across all agent-days.
- Output columns: `content_type`, `agent_days`, `share_of_agent_days`.
Notes:
- If there are ties for the first event timestamp within an agent-day, break ties deterministically using `event_id` ascending.
- You may assume a large dataset; write SQL that is idiomatic for BigQuery.
### Constraints & Assumptions
- Preserve the scope, facts, inputs, and requested outputs from the prompt above.
- If the prompt leaves a detail unspecified, state a reasonable assumption before relying on it.
- Keep the answer interview-ready: concise enough to present, but concrete enough to implement or evaluate.
### Clarifying Questions to Ask
- Clarify SQL dialect or Python library versions, date/time semantics, duplicate handling, and null handling.
- Define the grain of each intermediate result before aggregating.
- State expected output columns and ordering explicitly.
### What a Strong Answer Covers
- A query or pandas plan that matches the requested output grain.
- Correct joins, filters, grouping, window functions, and treatment of NULLs or duplicates.
- A brief explanation of why the result is correct and how it handles edge cases.
- Performance notes, indexes/partitioning, and validation queries when relevant.
### Follow-up Questions
- How would you test the query on a tiny hand-built dataset?
- What changes if duplicate events or late-arriving data are present?
- Which indexes, clustering, or partitions would help at production scale?
Quick Answer: Write SQL for content view metrics by device evaluates SQL or pandas logic, joins, grouping, window functions, null handling, edge cases, and validation in a realistic interview setting. A strong answer states assumptions, handles edge cases, explains trade-offs, and shows how to validate the result clearly.