Difficulty: Easy | Acceptance: 76.60% | Paid: No Topics: Database
Table: Views +---------------+---------+ | Column Name | Type | +---------------+---------+ | article_id | int | | author_id | int | | viewer_id | int | | view_date | date | +---------------+---------+ There is no primary key for this table, it may have duplicate rows. Each row of this table indicates that some viewer viewed an article (written by some author) at some date. Note that author_id and viewer_id refer to the same person.
Write an SQL query to find all the authors that viewed their own articles. Return the result table sorted by id in ascending order. The query result format is in the following example.
- Examples
- Constraints
- Approach 1: Simple WHERE Filter
- Approach 2: Using GROUP BY
- Approach 3: Using Self JOIN
Examples
Example 1:
Input: Views table: +------------+-----------+-----------+------------+ | 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 | | 4 | 7 | 1 | 2019-07-22 | | 3 | 4 | 4 | 2019-07-21 | | 3 | 4 | 4 | 2019-07-21 | +------------+-----------+-----------+------------+
Output: +------+ | id | +------+ | 4 | | 7 | +------+
Explanation:
- The article with id 3 was viewed by author 4 (viewer_id = author_id).
- The article with id 2 was viewed by author 7 (viewer_id = author_id).
- No other author viewed their own article.
Constraints
The Views table may contain duplicate rows.
Approach 1: Simple WHERE Filter
Intuition Filter rows where author_id equals viewer_id, then use DISTINCT to remove duplicates and sort by id.
Steps
- Select distinct author_id from Views where author_id equals viewer_id
- Rename the column to id
- Order the results by id in ascending order
# Write your MySQL query statement below
query = "SELECT DISTINCT author_id AS id FROM Views WHERE author_id = viewer_id ORDER BY id"Complexity
- Time: O(n log n) where n is the number of rows in Views table (due to sorting)
- Space: O(k) where k is the number of unique authors who viewed their own articles
- Notes: Most straightforward and readable solution
Approach 2: Using GROUP BY
Intuition Use GROUP BY to eliminate duplicates instead of DISTINCT, filtering for self-views.
Steps
- Select author_id from Views where author_id equals viewer_id
- Group by author_id to remove duplicates
- Rename the column to id
- Order the results by id in ascending order
# Write your MySQL query statement below
query = "SELECT author_id AS id FROM Views WHERE author_id = viewer_id GROUP BY author_id ORDER BY author_id"Complexity
- Time: O(n log n) where n is the number of rows in Views table
- Space: O(k) where k is the number of unique authors who viewed their own articles
- Notes: GROUP BY can be more flexible if you need aggregate functions
Approach 3: Using Self JOIN
Intuition Join the table with itself on the condition that author_id equals viewer_id, demonstrating another SQL pattern.
Steps
- Join Views with itself where author_id equals viewer_id
- Select distinct author_id from the joined result
- Rename the column to id
- Order the results by id in ascending order
# Write your MySQL query statement below
query = "SELECT DISTINCT v1.author_id AS id FROM Views v1 INNER JOIN Views v2 ON v1.author_id = v2.viewer_id WHERE v1.author_id = v1.viewer_id ORDER BY v1.author_id"Complexity
- Time: O(n²) in worst case due to self JOIN
- Space: O(k) where k is the number of unique authors who viewed their own articles
- Notes: Less efficient than Approach 1, but demonstrates JOIN concept