[SQL] 1148. Article Views I (Easy)

select와 distinct, as, order by 를 사용하는 문제이다.

https://leetcode.com/problems/article-views-i/description/?envType=study-plan-v2&envId=top-sql-50

⬆️ 문제 전문 링크

 

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.
 

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.

 

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    |
+------+

 

 

Views 테이블에서 author_id 와 viewer_id 가 동일한 경우를 찾되, 

같은 id 값으로 귀결되면 한번만 뽑고, 뽑은 id 값은 id 라는 컬럼명으로 추출하고, 이를 id의 오름차순으로 정렬하는 문제로

각각 distinct (중복제거), as (별칭 사용), order by [컬럼] ASC 를 사용하여 해결할 수 있다.

 

풀이는 다음과 같다.

 

select distinct author_id as id
from Views v
where v.author_id = v.viewer_id 
order by id ASC;