Coding questions · LinkedIn
Device Status without Timestamps
This question evaluates data manipulation and behavioral analytics competencies, emphasizing aggregation, deduplication, distinct counts, and date-based filtering to measure author and viewer activity.
Table: article_views
article_id INT author_id INT viewer_id INT view_dt DATE
Count authors who have never viewed any of their own articles.
On 2024‑11‑11, how many members viewed more than one distinct article?
Authors never self-viewed
Using the article_views table, count the number of distinct authors who have at least one article view record but have never viewed any of their own articles (i.e., there is no row where viewer_id = author_id for that author). Return a single row with this count.
Table article_views
| column | type | constraints |
|---|---|---|
| article_id | INT | |
| author_id | INT | |
| viewer_id | INT | |
| view_dt | DATE |
Sample data:
| article_id | author_id | view_dt | viewer_id |
|---|---|---|---|
| 1 | 10 | 2024-11-11 | 20 |
| 1 | 10 | 2024-11-10 | 10 |
| 2 | 20 | 2024-11-11 | 20 |
| 3 | 30 | 2024-11-11 | 40 |
| 3 | 30 | 2024-11-11 | 50 |
| 4 | 40 | 2024-11-09 | 40 |
| 5 | 50 | 2024-11-11 | 60 |
| 6 | 50 | 2024-11-11 | 60 |
| 6 | 50 | 2024-11-11 | 70 |
Expected output
Single-row result with the count of authors who appear in article_views but never have a self-view (no row where viewer_id = author_id). For the sample data, authors 30 and 50 never self-viewed, so the count is 2.
| authors_never_self_viewed |
|---|
| 2 |
Hints
- Start from the set of distinct author_id values in article_views.
- Use NOT EXISTS to exclude authors who have any row where viewer_id equals author_id.
Multi-article viewers on specific date
Using the article_views table, on the date 2024-11-11, count how many distinct viewers (viewer_id) viewed more than one distinct article. Return a single row with this count.
Table article_views
| column | type | constraints |
|---|---|---|
| article_id | INT | |
| author_id | INT | |
| viewer_id | INT | |
| view_dt | DATE |
Sample data:
| article_id | author_id | view_dt | viewer_id |
|---|---|---|---|
| 1 | 10 | 2024-11-11 | 20 |
| 1 | 10 | 2024-11-10 | 10 |
| 2 | 20 | 2024-11-11 | 20 |
| 3 | 30 | 2024-11-11 | 40 |
| 3 | 30 | 2024-11-11 | 50 |
| 4 | 40 | 2024-11-09 | 40 |
| 5 | 50 | 2024-11-11 | 60 |
| 6 | 50 | 2024-11-11 | 60 |
| 6 | 50 | 2024-11-11 | 70 |
Expected output
Single-row result with the number of viewers who, on 2024-11-11, viewed more than one distinct article. In the sample data, viewer 20 viewed articles 1 and 2, and viewer 60 viewed articles 5 and 6 on that date, so the count is 2.
| members_multi_viewed_on_2024_11_11 |
|---|
| 2 |
Hints
- Filter article_views to rows where view_dt = DATE '2024-11-11' before aggregating.
- Group by viewer_id and use HAVING COUNT(DISTINCT article_id) > 1 to keep only multi-article viewers.