Coding questions · LinkedIn
Compute article-type diversity per user and histogram
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.
You track article views and article metadata.
Tables
article_views
user_idINTarticle_idINTview_dateDATE
articles
article_idINT (PK)article_typeVARCHAR
Tasks
- For each user, compute the number of distinct article types they viewed on 2019-01-01.
- Output columns:
user_id,num_article_types.
- Build a histogram of
num_article_typeson 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.
Compute article-type diversity per user and histogram
Table article_views
| column | type | constraints |
|---|---|---|
| user_id | INTEGER | |
| article_id | INTEGER | |
| view_date | DATE |
Sample data:
| article_id | user_id | view_date |
|---|---|---|
| 101 | 1 | 2019-01-01 |
| 102 | 1 | 2019-01-01 |
| 103 | 1 | 2019-01-01 |
| 101 | 2 | 2019-01-01 |
| 103 | 2 | 2019-01-01 |
| 104 | 3 | 2019-01-01 |
Table articles
| column | type | constraints |
|---|---|---|
| article_id | INTEGER | PRIMARY KEY |
| article_type | VARCHAR |
Sample data:
| article_id | article_type |
|---|---|
| 101 | news |
| 102 | blog |
| 103 | news |
| 104 | review |
Expected output
Histogram of distinct article types viewed per user on 2019-01-01. Each row shows a distinct count of article types and how many users had that count.
| num_article_types | num_users |
|---|---|
| 1 | 2 |
| 2 | 1 |
Hints
- Join article_views to articles to access article_type.
- Filter rows to the target date: 2019-01-01.
- First aggregate by user to count DISTINCT article_type, then aggregate again to build the histogram.