30-Day Page View Counts

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Meta

Problem

Give us each user's total page views in page_views for 2025 up to and including December 28, counting every view, repeat hits on the same page included. We close the snapshot on that final day, so a visit at any hour of December 28 belongs in it.

Thirty days of engagement. Quick snapshot.

Tables

page_views

Columns: view_id INTEGER, page_url TEXT, user_id INTEGER, referrer TEXT, dur_ms INTEGER, device TEXT, viewed_at TEXT

Expected output

Worked solution and explanation

What this really is

This is a boundary question dressed up as an engagement report. The count per user is the easy part; anyone writes GROUP BY user_id and COUNT(*). What separates candidates is the edge of the window: viewed_at is a timestamp, not a date, and the snapshot is closed on a calendar day. Write viewed_at BETWEEN '2025-01-01' AND '2025-12-28' and the upper bound quietly becomes midnight, so every view after 00:00:00 on December 28 falls out. The one day the report is built around becomes the one day it gets wrong.

Range on the raw timestamp

viewed_at BETWEEN '2025-01-01' AND '2025-12-28' reads as inclusive but stops at 2025-12-28 00:00:00. A view at 09:15 on the 28th is excluded and every user active that day comes up short.

Range on the calendar day

DATE(viewed_at) BETWEEN '2025-01-01' AND '2025-12-28' compares days to days. Any hour of December 28 maps to the date December 28 and is counted.

Step 1: Pin both ends to whole days

The snapshot opens on January 1 and closes at the end of December 28. Say both bounds as days before you write a literal; the start is harmless at midnight, the end is not.

Step 2: Compare on DATE(viewed_at)

Truncating the timestamp to its day makes both bounds of BETWEEN mean whole days, which is exactly what "up to and including December 28" means to the business asking.

Step 3: Count every row per user_id

COUNT(*) counts each view. A repeat hit on the same page is still a view, so COUNT(DISTINCT page_url) answers a different question and understates the heavy users. Grouping on user_id also keeps the anonymous rows together as their own line.

SELECT user_id, COUNT(*) AS total_views
FROM page_views
WHERE DATE(viewed_at) BETWEEN '2025-01-01' AND '2025-12-28'
GROUP BY user_id

Common follow-up questions

• How would you write this so the planner can prune partitions on `viewed_at`? (Tests the half-open range as the prune-friendly equivalent of `DATE(viewed_at) BETWEEN`.)
• If `viewed_at` is stored in UTC but the business day is Pacific time, what changes? (Tests whether the candidate sees that the day boundary itself depends on the time zone.)
• How would you turn this into a rolling 30-day count for every day of the month? (Tests moving from a single fixed window to a windowed aggregate per day.)