# What Fills the Feed > Every slot in the catalog is a bet. See which format keeps winning it. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem Before allocating next quarter's production budget, the editorial team wants to see how the catalog breaks down by content type. Produce the standings by number of items, most to least. ## Worked solution and explanation ### What this is really asking This is a per-type row count wearing a popularity ranking's clothes. Collapse 4M rows to one line per content_type, count the items, order highest first. The trap is what you count: duration_seconds is null for every article, so COUNT(duration_seconds) silently zeroes them out and hands the leaderboard to whoever fills that column. Count rows, not an attribute that happens to be null. --- ### Break down the requirements #### Step 1: Aggregate to the right grain One row per content_type, and the tally is COUNT(*). content_id is the primary key, so COUNT(content_id) matches too, but COUNT(*) reads as the plain intent: every item counts. Reach for COUNT(duration_seconds) or COUNT(creator_id) and you undercount any type with nulls in that column. #### Step 2: Order the standings ORDER BY cnt DESC puts the busiest formats on top. Return both the type and its count so editorial sees the gap between first and last, not just the winner. --- ### The solution **CONTENT TYPE STANDINGS BY VOLUME** ```sql SELECT content_type, COUNT(*) AS cnt FROM content_items GROUP BY content_type ORDER BY cnt DESC ``` **COUNT(*)** Counts every row in the group. Articles have null duration but are still real items, so they land at their true count of 5. **COUNT(duration_seconds)** Counts only rows where duration_seconds is non-null. All five articles have null duration, so this reports 0 for articles and quietly drops them out of the standings. > **Common Pitfall** > > Selecting a nullable attribute instead of the row count. duration_seconds is null for every article and creator_id is null for one livestream. COUNT() skips nulls, so the leaderboard shifts under you with no error raised. Count rows with COUNT(*) unless you specifically want non-null coverage. > **Cost Analysis** > > A 4M row scan is unavoidable without a precomputed rollup, but content_type is low cardinality (about 10 values), so the hash aggregate stays tiny and fits in memory. A covering index on content_type lets the planner count from the narrow index instead of touching the wide heap rows. > **Interviewers Watch For** > > Whether you notice the null column before you write COUNT of it. The strong candidate glances at the sample, sees duration_seconds empty on articles, and reaches for COUNT(*) on purpose. The tell is naming why, not just typing the right thing. --- ### COMMON FOLLOW-UP QUESTIONS ## Common follow-up questions - Now add a column for how many items of each type actually have a duration recorded. _(Pushes toward COUNT(*) FILTER or a CASE-based conditional count, and tests whether they keep the null handling straight.)_ - Restrict the standings to items published in the last 12 months. _(Adds a publish_date predicate and opens whether recent volume diverges from lifetime catalog volume.)_ - How would you serve this to a dashboard that refreshes every 30 seconds? _(Pushes toward a materialized rollup or counter table; scanning 4M rows on every dashboard load is wasteful.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/what_fills_the_feed) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.