Skip to content
/Chapter 17 · Guided Playground Missions
Lesson 17.6·website_analytics
Lesson 17.6

SQL Playground Mission: Pageview Deduplication Audit

Compare row counts with distinct session counts so repeated pageviews do not 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)
Schema · Website AnalyticsTable · pageviews4 columns · 75 rows
Table · pageviews

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

4 columns · 75 rows
pageview_id intsession_id intpath textview_order int
Your task

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

SQL Workbench
query.sqlwebsite_analytics · SQL engine loading
⌘↵ to run
·
Expected answer
  • Columns: path, pageviews, sessions, repeat_views.
  • Rows: only paths where pageviews exceed distinct sessions.
  • The homepage has 26 pageviews from 22 sessions, so it has 4 repeat views.
Next · 17.7 Campaign revenue report →