Lesson 17.6

SQL Playground Mission: Pageview Deduplication Audit

Compare row counts with distinct session counts so repeated pageviews don't masquerade as unique reach.

Raw event rows and unique reach are different metrics. A page can have 26 pageview rows while fewer sessions actually saw it, because some sessions viewed the same path more than once.

This audit makes the difference visible. For each path, compare total pageviews with distinct sessions, then keep only paths where repeated views exist.

Pattern

select entity_key,
       count(*) as raw_rows,
       count(distinct user_or_session_id) as unique_entities,
       count(*) - count(distinct user_or_session_id) as repeat_rows
from event_table
group by entity_key
having count(*) > count(distinct user_or_session_id)

Preparing for an interview? The deduplication interview questions cover audits and keeper rows, with model answers to compare against.

Schema · Website Analytics Table · pageviews 4 columns · 75 rows

Table · pageviews

One row per page viewed within a session, in view_order.

4 columns · 75 rows

pageview_id int session_id int path text view_order int

Your task

For each path with repeated pageviews, return path, pageviews, distinct sessions, and repeat_views. Sort by repeat_views descending, then path.