Self-Viewed Article Authors Report

Easy

An analytics table records article view activity in this schema: views columns: article_id: article identifier author_id: account that wrote the article viewer_id: account that viewed the article view_date: date of the view event Data notes: The table may contain duplicate rows. Multiple views of the same article can appear. A row where author_id equals viewer_id means the author viewed their own article. Task: Return every author who has at least one self-view row. Each qualifying author should appear once. Return a single output column named id. Sort results 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  |
| 1          | 3         | 6         | 2019-08-02  |
| 2          | 7         | 7         | 2019-08-01  |
| 2          | 7         | 6         | 2019-08-02  |
| 3          | 4         | 4         | 2019-07-21  |
| 3          | 4         | 4         | 2019-07-21  |

Output

[
  {"id":4},
  {"id":7}
]

Explanation

Authors 4 and 7 each have at least one row where author_id == viewer_id. Duplicate self-view rows for author 4 still contribute only one output row.

Example 2

Input

views:
| article_id | author_id | viewer_id | view_date   |
|------------|-----------|-----------|-------------|
| 10         | 2         | 8         | 2020-01-01  |
| 11         | 2         | 9         | 2020-01-02  |
| 12         | 5         | 6         | 2020-01-03  |

Output

[]

Explanation

No row has author_id equal to viewer_id, so no author qualifies.

Example 3

Input

views:
| article_id | author_id | viewer_id | view_date   |
|------------|-----------|-----------|-------------|
| 20         | 9         | 9         | 2021-05-10  |
| 21         | 9         | 9         | 2021-05-11  |
| 22         | 9         | 3         | 2021-05-12  |
| 23         | 2         | 2         | 2021-05-12  |

Output

[
  {"id":2},
  {"id":9}
]

Explanation

Both authors 2 and 9 have at least one self-view. Multiple self-view rows for the same author do not create duplicates in output.

Constraints

1 <= number of rows in views <= 100,000
1 <= article_id, author_id, viewer_id <= 1,000,000,000
view_date is a valid date in YYYY-MM-DD format
Duplicate rows may exist in views
Output must contain exactly one column named id
Output must list distinct qualifying authors in ascending id order
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with one column: id

Loading problem...

Preparing your editor. You can read the problem while it loads.

Self-Viewed Article Authors Report

Easy

An analytics table records article view activity in this schema: views columns: article_id: article identifier author_id: account that wrote the article viewer_id: account that viewed the article view_date: date of the view event Data notes: The table may contain duplicate rows. Multiple views of the same article can appear. A row where author_id equals viewer_id means the author viewed their own article. Task: Return every author who has at least one self-view row. Each qualifying author should appear once. Return a single output column named id. Sort results 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  |
| 1          | 3         | 6         | 2019-08-02  |
| 2          | 7         | 7         | 2019-08-01  |
| 2          | 7         | 6         | 2019-08-02  |
| 3          | 4         | 4         | 2019-07-21  |
| 3          | 4         | 4         | 2019-07-21  |

Output

[
  {"id":4},
  {"id":7}
]

Explanation

Authors 4 and 7 each have at least one row where author_id == viewer_id. Duplicate self-view rows for author 4 still contribute only one output row.

Example 2

Input

views:
| article_id | author_id | viewer_id | view_date   |
|------------|-----------|-----------|-------------|
| 10         | 2         | 8         | 2020-01-01  |
| 11         | 2         | 9         | 2020-01-02  |
| 12         | 5         | 6         | 2020-01-03  |

Output

[]

Explanation

No row has author_id equal to viewer_id, so no author qualifies.

Example 3

Input

views:
| article_id | author_id | viewer_id | view_date   |
|------------|-----------|-----------|-------------|
| 20         | 9         | 9         | 2021-05-10  |
| 21         | 9         | 9         | 2021-05-11  |
| 22         | 9         | 3         | 2021-05-12  |
| 23         | 2         | 2         | 2021-05-12  |

Output

[
  {"id":2},
  {"id":9}
]

Explanation

Both authors 2 and 9 have at least one self-view. Multiple self-view rows for the same author do not create duplicates in output.

Constraints

1 <= number of rows in views <= 100,000
1 <= article_id, author_id, viewer_id <= 1,000,000,000
view_date is a valid date in YYYY-MM-DD format
Duplicate rows may exist in views
Output must contain exactly one column named id
Output must list distinct qualifying authors in ascending id order
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with one column: id