Same-Day Multi-Article Viewers

Medium

A content platform stores article view events in a single fact table. Table: views article_id: identifier of the viewed article author_id: identifier of the article author viewer_id: identifier of the viewer view_date: date when the view occurred (YYYY-MM-DD) Data notes: The table may contain duplicate rows. A viewer can read multiple articles on the same day. A viewer can also read the same article repeatedly on the same day. Task: Identify viewers who viewed more than one distinct article on at least one date. Distinctness is on article_id within each (viewer_id, view_date) group. Return each qualifying viewer exactly once. Output requirements: Return one column named id (id = viewer_id) Sort by id in ascending order Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

views:
| article_id | author_id | viewer_id | view_date   |
|------------|-----------|-----------|-------------|
| 1          | 3         | 5         | 2019-08-01  |
| 3          | 4         | 5         | 2019-08-01  |
| 1          | 3         | 6         | 2019-08-02  |
| 2          | 7         | 7         | 2019-08-01  |
| 2          | 7         | 6         | 2019-08-02  |
| 4          | 7         | 1         | 2019-07-22  |
| 3          | 4         | 4         | 2019-07-21  |
| 3          | 4         | 4         | 2019-07-21  |

Output

[
  {"id":5},
  {"id":6}
]

Explanation

Viewer 5 has two distinct articles on 2019-08-01. Viewer 6 has two distinct articles on 2019-08-02. Viewer 4 has duplicate rows for one article only and does not qualify.

Example 2

Input

views:
| article_id | author_id | viewer_id | view_date   |
|------------|-----------|-----------|-------------|
| 10         | 10        | 10        | 2024-01-01  |
| 10         | 10        | 10        | 2024-01-01  |
| 11         | 11        | 10        | 2024-01-02  |

Output

[]

Explanation

The viewer reads one distinct article per day, so no viewer qualifies.

Example 3

Input

views:
| article_id | author_id | viewer_id | view_date   |
|------------|-----------|-----------|-------------|
| 21         | 8         | 30        | 2024-03-15  |
| 22         | 9         | 30        | 2024-03-15  |
| 23         | 8         | 30        | 2024-03-15  |
| 24         | 1         | 31        | 2024-03-15  |
| 25         | 1         | 31        | 2024-03-16  |

Output

[
  {"id":30}
]

Explanation

Viewer 30 has three distinct articles on one day and qualifies. Viewer 31 spreads activity across days and does not satisfy same-day multi-article rule.

Constraints

0 <= number of rows in views <= 200000
1 <= article_id, author_id, viewer_id <= 1000000000
view_date is a valid date in YYYY-MM-DD format
Duplicate rows may exist
A qualifying group requires more than one distinct article_id for the same viewer_id and view_date
Return each qualifying viewer once
Output must be sorted by id ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with one column: id

views: | article_id | author_id | viewer_id | view_date | |------------|-----------|-----------|-------------| | 1 | 3 | 5 | 2019-08-01 | | 3 | 4 | 5 | 2019-08-01 | | 1 | 3 | 6 | 2019-08-02 | | 2 | 7 | 7 | 2019-08-01 | | 2 | 7 | 6 | 2019-08-02 | | 4 | 7 | 1 | 2019-07-22 | | 3 | 4 | 4 | 2019-07-21 | | 3 | 4 | 4 | 2019-07-21 |

views: | article_id | author_id | viewer_id | view_date | |------------|-----------|-----------|-------------| | 10 | 10 | 10 | 2024-01-01 | | 10 | 10 | 10 | 2024-01-01 | | 11 | 11 | 10 | 2024-01-02 |

views: | article_id | author_id | viewer_id | view_date | |------------|-----------|-----------|-------------| | 21 | 8 | 30 | 2024-03-15 | | 22 | 9 | 30 | 2024-03-15 | | 23 | 8 | 30 | 2024-03-15 | | 24 | 1 | 31 | 2024-03-15 | | 25 | 1 | 31 | 2024-03-16 |