Description
Write a SQL query to find all authors that viewed at least one of their own articles. Return distinct author_id, ordered ascending.
Table:
views| Column | Type |
|---|---|
| article_id | INT |
| author_id | INT |
| viewer_id | INT |
| view_date | TEXT |
Examples
Input:
CREATE TABLE views (article_id INT, author_id INT, viewer_id INT, view_date TEXT); INSERT INTO views VALUES (1, 3, 5, '2024-01-01'), (1, 3, 6, '2024-01-02'), (2, 7, 7, '2024-01-03'), (3, 4, 4, '2024-01-04');Output:
4
7Explanation:
Author 7 viewed their own article (article 2). Author 4 viewed their own article (article 3). Author 3 never viewed their own articles. Results sorted ascending.
Input:
CREATE TABLE views (article_id INT, author_id INT, viewer_id INT, view_date TEXT); INSERT INTO views VALUES (1, 2, 2, '2024-01-05'), (2, 2, 2, '2024-01-06'), (3, 5, 8, '2024-01-07'), (4, 9, 9, '2024-01-08'), (5, 9, 12, '2024-01-09');Output:
2
9Explanation:
Author 2 viewed their own articles twice (articles 1 and 2), author 5 did not view their own article (viewer was 8), and author 9 viewed their own article once (article 4). Only authors 2 and 9 meet the criteria, returned in ascending order.
Input:
CREATE TABLE views (article_id INT, author_id INT, viewer_id INT, view_date TEXT); INSERT INTO views VALUES (10, 1, 3, '2024-02-01'), (11, 6, 7, '2024-02-02'), (12, 8, 8, '2024-02-03'), (13, 15, 20, '2024-02-04'), (14, 25, 30, '2024-02-05');Output:
8Explanation:
Only author 8 viewed their own article (article 12). All other authors had different viewer_ids than their author_ids, so they don't qualify. The result contains only one author_id: 8.
Constraints
- •
author_id = viewer_id