Count Article Types Viewed
Company: LinkedIn
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
##### Question
You are given article view events and article metadata.
**Table 1: `article_views`** — one row per article view event.
| Column | Type | Description |
| --- | --- | --- |
| `user_id` | INT | The user who viewed the article |
| `article_id` | INT | The article that was viewed |
| `view_date` | DATE | UTC calendar date of the view |
**Table 2: `articles`** — maps each article to its type.
| Column | Type | Description |
| --- | --- | --- |
| `article_id` | INT | Article identifier |
| `article_type` | VARCHAR | The category/type of the article |
**Relationship:** `article_views.article_id = articles.article_id`
**Assumptions**
- Each row in `article_views` represents one article view event.
- `view_date` is a calendar date; treat dates as UTC and assume no timezone conversion is needed.
- If a user views multiple articles of the same `article_type`, that type is counted **only once** for that user (distinct types).
Write SQL for the following:
1. **Daily distinct types per user.** For `2019-01-01`, return the number of distinct article types viewed by each user.
- Output columns: `user_id, num_article_types`
2. **Histogram across all dates.** Build a histogram of the number of distinct article types viewed per user across the full dataset (all available dates). For each possible value of `num_article_types`, return how many users viewed exactly that many distinct article types.
- Output columns: `num_article_types, num_users`
Quick Answer: Count Article Types Viewed 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.