Compute article-type diversity per user and histogram
Company: LinkedIn
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
You track article views and article metadata.
### Tables
`article_views`
- `user_id` INT
- `article_id` INT
- `view_date` DATE
`articles`
- `article_id` INT (PK)
- `article_type` VARCHAR
### Tasks
1) For each user, compute the **number of distinct article types** they viewed on **2019-01-01**.
- Output columns: `user_id`, `num_article_types`.
2) Build a **histogram** of `num_article_types` on **2019-01-01** (i.e., how many users viewed 1 type, 2 types, etc.).
- Output columns: `num_article_types`, `num_users`.
### Notes
- If a user viewed multiple articles of the same type, it counts once.
- Users with zero views on 2019-01-01 can be excluded unless otherwise specified.
Overview: This question evaluates data manipulation and aggregation skills, including joining event logs with metadata, deduplicating by categorical attribute, date-based filtering, and computing per-user distinct counts.
On 2019-01-01, compute for each user the number of distinct article types they viewed, and then produce a histogram showing how many users viewed 1 type, 2 types, etc.
Tables
article_views(user_id INTEGER, article_id INTEGER, view_date DATE)
articles(article_id INTEGER, article_type VARCHAR)
Hints
- Join article_views to articles to access article_type.
- Filter rows to the target date: 2019-01-01.