Yearly Output

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

Problem

The editorial director is building the annual content report and wants to see publishing velocity broken down by year. For each calendar year, show the number of items published and the average duration in seconds. Exclude any items that have no publish date on file. Present the years in chronological order.

Publishing velocity for the board deck.

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

Why this problem exists in real interviews

This tests date extraction, NULL filtering, and multi-aggregate GROUP BY. Interviewers check whether you can derive a time dimension from a date column, exclude incomplete records, and compute multiple aggregates.

Break down the requirements

Step 1: Exclude items without a publish date

WHERE publish_date IS NOT NULL removes the 5% of items with no publish date.

Step 2: Extract year and aggregate

GROUP BY STRFTIME('%Y', publish_date) with COUNT(*) and AVG(duration_seconds) computes items published and average duration per year.

Step 3: Order chronologically

ORDER BY year presents the years from earliest to most recent.

The solution

SELECT
    STRFTIME('%Y', publish_date) AS year,
    COUNT(*) AS items_published,
    AVG(duration_seconds) AS avg_duration
FROM content_items
WHERE publish_date IS NOT NULL
GROUP BY STRFTIME('%Y', publish_date)
ORDER BY year

Common follow-up questions

• How would you show the year-over-year growth in items published? (Use LAG to access the previous year's count and compute the difference.)
• What if you needed to break this down by `content_type` as well? (Add `content_type` to the GROUP BY for a two-dimensional breakdown.)
• How would you handle items published on the same day with different durations? (The aggregation handles this naturally; each item is counted individually.)