1148.article-views-i.sql

#
# @lc app=leetcode id=1148 lang=mysql
#
# [1148] Article Views I
#
# Difficulty: Easy
# Tags: Database
# URL: https://leetcode.com/problems/article-views-i/
#

#
# --- Problem Description ---
#
# 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    |
# +------+
#

#
# --- Community Solutions ---
#
# https://leetcode.com/problems/article-views-i/solutions
#
# @lc code=start

# SQL / 基础查询：筛自读记录 + 去重排序
#
# 这题其实在做什么：
#   Views 表记录谁看了哪篇文章。找出看过自己文章的作者 id。
#   如果同一个作者多次看自己的文章，只输出一次。
#
# 识别信号：
#   - 同一行里比较 author_id 和 viewer_id → 单表 WHERE。
#   - “viewed at least one of their own articles” → author_id = viewer_id。
#   - “return sorted by id” + “每个作者一次” → DISTINCT + ORDER BY。
#
# 核心思路：
#   先筛出自读记录：`author_id = viewer_id`。
#   然后把 author_id 改名成 id 输出，并用 DISTINCT 去重。
#   最后按 id 升序排列。
#
# 查询形状：
#   SELECT DISTINCT author_id AS id
#   FROM Views
#   WHERE author_id = viewer_id
#   ORDER BY id
#
# Join / Filter / Grouping：
#   - Join：不需要，作者和浏览者都在同一行。
#   - Filter：同一行内两个字段相等。
#   - Grouping：可用 DISTINCT 去重；不需要 GROUP BY 统计。
#
# 边界条件 / 易错点：
#   - 不去重会把同一个作者重复输出多次。
#   - 输出列名必须是 id，不是 author_id。
#   - 排序按输出 id 升序；不要按 article_id 或 view_date。
#
# 落码步骤 / TODO：
#   1. WHERE author_id = viewer_id。
#   2. SELECT DISTINCT author_id AS id。
#   3. ORDER BY id。
#
# 复杂度：
#   逻辑上扫描 Views，筛选后去重排序；真实成本取决于数据量和索引。
#
# 一句模板：
#   同一行字段互相比较用 WHERE；“每人一次”用 DISTINCT。

# DISTINCT 去重，保证每个作者只出现一次
SELECT DISTINCT author_id AS id
FROM Views
# 同一行里作者 == 浏览者，说明作者看了自己的文章
WHERE author_id = viewer_id
ORDER BY id;

# @lc code=end