Article Views I
Linked In, Meta
Problem Statement
Content platforms often track user interactions to distinguish between organic reach and self-engagement.
Write an SQL query to find all the authors that viewed at least one of their own articles.
Rules:
- A self-view occurs when author_id and viewer_id are the same for a specific view event.
- Return the result table containing the id of the authors, sorted in ascending order.
- Each author_id should appear only once in the final output, even if they viewed multiple articles of theirs.
Tests your understanding of
Basic SQL, Filtering, Comparison and Deduplication
Input Tables
views
| article_id(INTEGER) | author_id(INTEGER) | viewer_id(INTEGER) | view_date(DATE) |
|---|---|---|---|
| 1 | 3 | 5 | 2024-08-01 |
| 1 | 3 | 6 | 2024-08-02 |
| 2 | 7 | 7 | 2024-08-01 |
| 2 | 7 | 6 | 2024-08-02 |
| 4 | 7 | 1 | 2024-07-22 |
| 3 | 4 | 4 | 2024-07-21 |
| 3 | 4 | 4 | 2024-07-21 |
Expected Output
| id(INTEGER) |
|---|
| 4 |
| 7 |
Tags
EasyBasic SQLFilteringComparisonDeduplication
5-10 min
78%