Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();

version

PostgreSQL 16.6 (Debian 16.6-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

SELECT 1

CREATE TABLE views (
    article_id INT,
    author_id INT,
    viewer_id INT,
    view_date DATE
);

CREATE TABLE

INSERT INTO views (article_id, author_id, viewer_id, view_date)
VALUES
    (1, 10, 1, '2024-12-01'),
    (2, 11, 1, '2024-12-01'),
    (3, 12, 1, '2024-12-01'),
    (1, 10, 2, '2024-12-01'),
    (1, 10, 3, '2024-12-01'),
    (4, 13, 4, '2024-12-02'),
    (5, 14, 4, '2024-12-02'),
    (3, 12, 1, '2024-12-02'),
    (1, 10, 1, '2024-12-02'),
    (1, 10, 1, '2024-12-02');

INSERT 0 10

SELECT DISTINCT viewer_id
FROM views
GROUP BY viewer_id, view_date
HAVING COUNT(DISTINCT article_id) > 1
ORDER BY viewer_id;

viewer_id

1

4

SELECT 2

SELECT DISTINCT v1.viewer_id
FROM views v1
JOIN views v2
    ON v1.viewer_id = v2.viewer_id
    AND v1.view_date = v2.view_date
    AND v1.article_id <> v2.article_id
ORDER BY v1.viewer_id;

viewer_id

1

4

SELECT 2

SELECT DISTINCT v.viewer_id
FROM views v
WHERE (
    SELECT COUNT(DISTINCT article_id)
    FROM views
    WHERE viewer_id = v.viewer_id
    AND view_date = v.view_date
) > 1
ORDER BY v.viewer_id;

viewer_id

1

4

SELECT 2