The Publishing Audit

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

Netflix

Problem

The content analytics team is auditing the 2026 publishing pipeline. Pull all fields for content items published during that year.

Published years ago. Still generating views?

Tables

content_items

Columns: content_id INTEGER, title TEXT, content_type TEXT, duration_seconds INTEGER, creator_id INTEGER, publish_date TEXT

Expected output

Worked solution and explanation

What this is really asking

This is a calendar-year boundary problem wearing an audit costume. Everyone writes a filter on publish_date. What separates candidates is whether that predicate covers the whole year, '2026-01-01' through '2026-12-31' with both ends included, on the bare column, and whether they leave every other field alone. Read 'during that year' as one date and you get a single day's publishes. Add a tidy-looking guard on duration_seconds and the 10 items with no duration drop out of an audit that needed all 61.

Break down the requirements

Step 1: Return every field

SELECT * is the right call here, not a lazy one. The audit wants the raw row shape. Rows with an empty duration_seconds or creator_id are still 2026 publishes, so nothing but the date decides inclusion.

Step 2: Bound the year on the bare column

Use January 1 through December 31, both ends included. publish_date holds ISO 'YYYY-MM-DD' text, so the range compares correctly, and the 12 undated items fall out on their own because a NULL never satisfies a range.

The solution

SELECT *
FROM content_items
WHERE publish_date BETWEEN '2026-01-01' AND '2026-12-31'

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• How would the query change if `publish_date` were a TIMESTAMP? (Use a half-open `>= '2026-01-01' AND < '2027-01-01'` so afternoon rows on December 31 survive.)
• What index makes this fast? (A B-tree on `publish_date`. If audits always pull one year, partitioning by year is cleaner.)
• How would you handle multi-year audits? (Parameterize the bounds and widen the range, adding `EXTRACT(YEAR FROM publish_date)` to the projection if reviewers need the year as its own column.)