Skip to content
>_sqlbuddy
easySELECTWHEREDISTINCTSorting

Article Views I

Find the authors who viewed their own articles at least once.

Start practice

Prompt

Article Views I

Prompt

Given the views table below (one row per view of an article), write a query that returns the id of every author who viewed at least one of their own articles — that is, rows where author_id = viewer_id.

  • Each qualifying author appears once (deduplicated).
  • Output a single column named id, ordered by `id` ascending.

Example

article_idauthor_idviewer_idview_date
1352024-01-01
2332024-01-02
3772024-01-03
4732024-01-04

Expected result:

id
3
7

Constraints

  • There is no primary key — the same (article, author, viewer) can repeat.

Tips

  • SELECT DISTINCT author_id AS id FROM views WHERE author_id = viewer_id ORDER BY id.

Expected concepts

  • DISTINCT
  • WHERE
  • ORDER BY

Schema

CREATE TABLE views (
    article_id INTEGER NOT NULL,
    author_id  INTEGER NOT NULL,
    viewer_id  INTEGER NOT NULL,
    view_date  DATE NOT NULL
);

Sample data

INSERT INTO views (article_id, author_id, viewer_id, view_date) VALUES
  (1, 3, 5, '2024-01-01'),
  (2, 3, 3, '2024-01-02'),
  (3, 7, 7, '2024-01-03'),
  (4, 7, 3, '2024-01-04'),
  (5, 9, 9, '2024-01-05'),
  (6, 9, 1, '2024-01-06');

Additional hidden fixtures are applied during validation to test edge cases.