Heavy Hitters

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L3

Problem

The eng director wants to know which repositories are absorbing the most new code. In repo_commits, total the lines added per repo, then return the repos whose total beats the average lines added per repo, along with that total and how many commits produced it, biggest first.

Some repos never sleep.

Tables

repo_commits

Columns: commit_id INTEGER, repo_name TEXT, author TEXT, message TEXT, added INTEGER, removed INTEGER, commit_at TEXT

Expected output

Worked solution and explanation

What this problem is really testing

This is an aggregate over an aggregate wearing a code-review costume. The director wants repos whose total lines added beats the average total across repos, and the whole game is that 'average total across repos' is a mean of per-repo sums, one level up from the raw commit rows. Anyone can group and sum. The tell is whether you compute the benchmark against the grouped totals or fumble it against the raw rows.

Building it up

Step 1: Collapse to per-repo totals

Group repo_commits by repo_name, emitting SUM(added) as the per-repo total and COUNT(*) as the commit count behind it. Name it in a CTE (repo_volume) because you are about to use the total twice: once for the output rows and once to compute the bar they must clear.

Step 2: Average the totals, not the rows

The threshold is (SELECT AVG(lines_added) FROM repo_volume): the mean of the per-repo totals, computed over the CTE, not the raw table. This scalar subquery is the whole reason you materialized the totals first.

Step 3: Filter strictly above, heaviest first

Keep repos with lines_added > (that average). Strict >, so a repo sitting exactly on the average drops out, then ORDER BY lines_added DESC so the heaviest repo leads.

The solution

WITH repo_volume AS (
    SELECT repo_name,
           SUM(added) AS lines_added,
           COUNT(*) AS commit_count
    FROM repo_commits
    GROUP BY repo_name
)
SELECT repo_name, lines_added, commit_count
FROM repo_volume
WHERE lines_added > (SELECT AVG(lines_added) FROM repo_volume)
ORDER BY lines_added DESC

Common follow-up questions

• How would you show each repo's total next to the overall average in the same result? (Tests `AVG() OVER ()` as a window function to avoid a second scan of the CTE.)
• What changes if the director wants repos above the median total instead of the mean? (Tests a manual median via row numbering, since SQLite has no `PERCENTILE_CONT`.)
• How would you add each heavy hitter's percentage of all lines added? (Tests `SUM() OVER ()` to express each repo's share of the grand total.)