[1148] Article Views I

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) on some date. 
Note that equal author_id and viewer_id indicate the same person.

 

Write an SQL query 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 query 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    |
+------+

 

문제 조건 

자신의 기사 중 하나 이상을 본 모든 작성자를 출력하시오.

id를 기준으로 오름차순 하시오 

 

정답 쿼리 

- 문제 조건은 author_id = viewer_id가 되어야 한다는 뜻으로 이해했습니다.

  이에, where 절에 author_id = viewer_id가 되는 것들만 필터링 하고 

  기사가 여러개 일 수 있으니, author_id를 unique하게 뽑아낸 후, id값을 기준으로 오름차순 함 

 

select distinct author_id as id 
from views
where author_id = viewer_id 
order by id asc