DISTINCT Versus GROUP BY

SQLDISTINCT vs GROUP BYMidSeniorUpdated Nov 10, 2026

Return each unique (user_id, page) pair along with the number of views for that pair. Order by views desc.

Schema

CREATE TABLE page_views (
  user_id BIGINT NOT NULL,
  page    TEXT   NOT NULL,
  viewed_at TIMESTAMPTZ NOT NULL
);

Drill This: Your Query First

Answer, Then Reveal

Write the query before you look at the answer.

Skip to Reading

The Correct Query

Option (b)

SELECT user_id, page, COUNT(*) AS views
FROM page_views
GROUP BY user_id, page
ORDER BY views DESC;

GROUP BY user_id, page collapses to unique pairs and lets COUNT(*) count rows per pair. (a) is invalid in Postgres: COUNT(*) without GROUP BY collapses the whole table to one row, and DISTINCT cannot 'undo' that. (c) groups by user_id only, which means page is not a valid bare column. (d) counts distinct timestamps and is missing the GROUP BY entirely.

The Options That Fail

The explanation above says why each of these breaks.

Option (a)

SELECT DISTINCT user_id, page, COUNT(*) AS views
FROM page_views
ORDER BY views DESC;

Option (c)

SELECT user_id, page, COUNT(*) AS views
FROM page_views
GROUP BY user_id
ORDER BY views DESC;

Option (d)

SELECT user_id, page, COUNT(DISTINCT viewed_at) AS views
FROM page_views
ORDER BY views DESC;