EasyBackendDesign

Design a Video View Counter

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...

Start problem Browse all problems

What you will practice

SQLDatabase / SQLSchema DesignEvent LogTime-Windowed Queries

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.

Starter files

schema.sqlEditable starter

What the judge checks

Runs in the python environment with the python-pytest runner.
Uses a 3000ms judge budget.
Behavior rules include: Schema Executes, Videos Table Exists, Video Views Table Exists, Per Event Storage Required.

Constraints

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

Example behavior

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

Total views: three events, three rows.

Input

SELECT COUNT(*) FROM video_views WHERE video_id = 1 AND viewed_at >= 1700001500;

Output

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

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?