LeetCode 1148 - Article Views I¶

Database Language: PostgreSQL

Difficulty:

Problem Description¶

Input¶

Table: Views¶

Column Name	Type
article_id	int
author_id	int
viewer_id	int
view_date	date

There is no primary key (column with unique values) for this table, the table may have duplicate rows.

Each row of this table indicates that some viewer viewed an article (written by some author) on some date.

Note that equal author_id and viewer_id indicate the same person.

Requirement¶

Write a solution to find all the authors that viewed at least one of their own articles.

Return the result table sorted by id in ascending order.

The result format is in the following example.

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

SQL Schema¶

CREATE TABLE IF NOT EXISTS Views (article_id int, author_id int, viewer_id int, view_date date);

TRUNCATE TABLE Views;

INSERT INTO Views (article_id, author_id, viewer_id, view_date)
VALUES ('1', '3', '5', '2019-08-01');

INSERT INTO Views (article_id, author_id, viewer_id, view_date)
VALUES ('1', '3', '6', '2019-08-02');

INSERT INTO Views (article_id, author_id, viewer_id, view_date)
VALUES ('2', '7', '7', '2019-08-01');

INSERT INTO Views (article_id, author_id, viewer_id, view_date)
VALUES ('2', '7', '6', '2019-08-02');

INSERT INTO Views (article_id, author_id, viewer_id, view_date)
VALUES ('4', '7', '1', '2019-07-22');

INSERT INTO Views (article_id, author_id, viewer_id, view_date)
VALUES ('3', '4', '4', '2019-07-21');

INSERT INTO Views (article_id, author_id, viewer_id, view_date)
VALUES ('3', '4', '4', '2019-07-21');

Solutions¶

Solution 1 - Using DISTINCT Clause¶

Based on the description of the input table as well as the description of the requirements, the query should return the id of the authors from the Views table where the author_id is the same as the viewer_id and return the result in ascending order by the id of the author. From this requirement, the query will be as follows (an alias of id is assigned to the author_id as this is what the output requires):

SELECT author_id AS id
FROM Views
WHERE author_id = viewer_id
ORDER BY id

The output of this query is as follows:

id
4
4
7

As can be seen from this output, author #4 is returned twice because there are 2 rows in the Views table where author #4 read the same article but the required output only wants each author once even if the author read multiple articles that the author wrote. To remove duplicates, the DISTINCT clause will be added to the query:

# Final Solution Query
SELECT DISTINCT author_id AS id
FROM Views
WHERE author_id = viewer_id
ORDER BY id

Here's the query execution plan generated by PostgreSQL for this query:

Unique  (cost=33.27..33.31 rows=9 width=4)
  ->  Sort  (cost=33.27..33.29 rows=9 width=4)
        Sort Key: author_id
        ->  Seq Scan on views  (cost=0.00..33.12 rows=9 width=4)
              Filter: (author_id = viewer_id)

And here's the fastest time for this query:

Runtime: 348ms
Beats: 88.69% as of August 6, 2024

Solution 2 - Using GROUP BY Clause¶

Instead of using the DISTINCT clause, another way of removing duplicates from the output is with the use of GROUP BY. Here's how the query will look like using the GROUP BY instead of the DISTINCT clause:

# Final Solution Query
SELECT author_id AS id
FROM Views
WHERE author_id = viewer_id
GROUP BY author_id
ORDER BY author_id

Here's the query execution plan generated by PostgreSQL for this query:

Group  (cost=33.27..33.31 rows=9 width=4)
  Group Key: author_id
  ->  Sort  (cost=33.27..33.29 rows=9 width=4)
        Sort Key: author_id
        ->  Seq Scan on views  (cost=0.00..33.12 rows=9 width=4)
              Filter: (author_id = viewer_id)

And here's the fastest runtime for the query:

Runtime: 348ms
Beats: 88.69% as of August 6, 2024

Solution Runtime Comparison¶

Here's the comparison of the fastest runtime for each of the solutions.

Solution #	Runtime	Beats
1 - Using DISTINCT	348ms	88.69%
2 - Using GROUP BY	348ms	88.69%