Article Views I - Problem
Given a table Views that tracks article views, find all authors who viewed at least one of their own articles.
Table: Views
| Column Name | Type |
|---|---|
| article_id | int |
| author_id | int |
| viewer_id | int |
| view_date | date |
Key Points:
- No primary key exists, duplicate rows are possible
- Each row represents a viewer looking at an article on a specific date
- When
author_id = viewer_id, the author viewed their own article - Return results sorted by
idin ascending order
Table Schema
Views
| Column Name | Type | Description |
|---|---|---|
article_id
|
int | ID of the article being viewed |
author_id
|
int | ID of the article's author |
viewer_id
|
int | ID of the person viewing the article |
view_date
|
date | Date when the article was viewed |
Note: No primary key exists, duplicate rows are possible. When author_id = viewer_id, the author viewed their own article.
Input & Output
Example 1 — Mixed Self and Other Views
Input Table:
| article_id | author_id | viewer_id | view_date |
|---|---|---|---|
| 1 | 1 | 1 | 2019-08-01 |
| 2 | 1 | 2 | 2019-08-01 |
| 1 | 4 | 1 | 2019-07-22 |
| 3 | 4 | 4 | 2019-07-21 |
| 3 | 4 | 4 | 2019-07-21 |
Output:
| id |
|---|
| 1 |
| 4 |
💡 Note:
Author 1 viewed their own article (article_id=1) and author 4 viewed their own article (article_id=3). The duplicate row for author 4 is handled by DISTINCT. Author 1 also had someone else view their article, but that doesn't affect the result.
Example 2 — No Self Views
Input Table:
| article_id | author_id | viewer_id | view_date |
|---|---|---|---|
| 1 | 1 | 2 | 2019-08-01 |
| 2 | 2 | 1 | 2019-08-02 |
Output:
| id |
|---|
💡 Note:
No authors viewed their own articles. Author 1's article was viewed by author 2, and author 2's article was viewed by author 1, but neither viewed their own work.
Example 3 — Single Self View
Input Table:
| article_id | author_id | viewer_id | view_date |
|---|---|---|---|
| 5 | 3 | 3 | 2019-08-01 |
Output:
| id |
|---|
| 3 |
💡 Note:
Author 3 viewed their own article (article_id=5), so they appear in the result.
Constraints
-
1 ≤ article_id, author_id, viewer_id ≤ 1000 -
view_dateis a valid date - The table may contain duplicate rows
Visualization
Tap to expand
Understanding the Visualization
1
Input
Views table with all article view records
2
Filter
WHERE author_id = viewer_id
3
Output
Unique author IDs sorted ascending
Key Takeaway
🎯 Key Insight: Use equality comparison to detect self-viewing patterns in relational data
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code