Skip to main content
Problem 21

Design a Video View Counter

EASYDESIGN
Database / SQL+3

You're at a video platform like YouTube. Every time someone watches a video, the view count needs to go up.

The obvious answer is a views column on the videos table that you increment on each play. That works — until product comes back and asks: "How many views did this video get in the last hour?" A single counter can't answer that. You've lost the history.

Design the SQL schema. The judge will run your schema, insert videos and view events, and verify that both total counts and time-windowed counts work correctly.

Requirements
  • Create a videos table with at least id INTEGER PRIMARY KEY and title TEXT NOT NULL.
  • Create a video_views table with at least id INTEGER PRIMARY KEY, video_id INTEGER NOT NULL, and viewed_at INTEGER NOT NULL (unix timestamp in seconds).
  • video_views.video_id must declare REFERENCES videos(id) so foreign-key integrity is enforced when SQLite has PRAGMA foreign_keys = ON.
  • One row in video_views represents exactly one view event. Do not store totals in a counter column.
  • After inserting view events, SELECT COUNT(*) FROM video_views WHERE video_id = ? must return the total view count for that video.
  • Time-windowed queries like SELECT COUNT(*) FROM video_views WHERE video_id = ? AND viewed_at >= ? must return the count within the window.
Examples
Example 1
Input
INSERT INTO videos (id, title) VALUES (1, 'Cat compilation');
INSERT INTO video_views (video_id, viewed_at) VALUES (1, 1700000000), (1, 1700001000), (1, 1700002000);
SELECT COUNT(*) FROM video_views WHERE video_id = 1;
Output
3
Note

Total views: three events, three rows.

Example 2
Input
SELECT COUNT(*) FROM video_views WHERE video_id = 1 AND viewed_at >= 1700001500;
Output
1
Note

Time-windowed: only the third event (timestamp 1700002000) is in the window.

Constraints
  • Do not store a views counter column. Storage must be per-event.
  • The schema must execute with conn.executescript(...) — no DML, only DDL.
Follow-up

Per-event storage scales to analytics, but at 10M views/day a `COUNT(*)` query gets slow. How would you keep the event log AND give the homepage a fast lookup for total views?

Hints
Related Practice
Track
Backend Basics

Keep moving through related backend basics problems and build a stronger search-friendly practice loop around this topic.

View track →
Problem 3
Password Strength Tagger
easy
Problem 2
In-Memory Rate Limiter
medium
Problem 5
Config Merge with Deletions
medium
Console output will appear here...