Kings for a Day

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

L5

Problem

We run a batch-processing platform where jobs record how many rows they moved and the time they started. For each calendar day, find the job that moved the most rows, and when two jobs tie for a day's highest count, report all of them. Return the day, the job name, and its row count, earliest day first.

Every day, one job moves more rows than all the rest.

Tables

batch_jobs

Columns: job_id INTEGER, job_name TEXT, status TEXT, rows_done INTEGER, started TEXT, ended TEXT, priority INTEGER, retries INTEGER

Expected output

Worked solution and explanation

What this is really asking

This is a per-day leaderboard where the crown can be shared. Anyone can get each day's biggest count with a GROUP BY and MAX(rows_done), but the moment you aggregate, job_name is gone, and recovering it means a second pass over batch_jobs joined on the day and the count. The real trap is the shortcut in the other direction: number each day's rows and keep row one. It reads like the same answer, and on any day where two jobs share the top count it quietly crowns one and drops the other without an error. Score every job against its own day, keep everyone at the top, and ties come out for free.

Break down the requirements

Step 1: Bucket by date, ignore uncounted jobs

Cast started to a date so a job at 23:59 and one at 00:01 land in different buckets, and drop rows whose rows_done is null: a job that logged no count cannot be the day's biggest mover.

Step 2: Rank within each day

RANK() OVER (PARTITION BY DATE(started) ORDER BY rows_done DESC) restarts the numbering for every day. Jobs that share the day's top count all get rank 1, so a day can crown more than one winner, which is what the prompt asks for.

Step 3: Keep rnk = 1, earliest day first

Window functions cannot appear in WHERE, so the rank lives in a CTE. The outer query filters rnk = 1 and sorts by job_date.

The solution

WITH ranked AS (
  SELECT
    DATE(started) AS job_date,
    job_name,
    rows_done,
    RANK() OVER (
      PARTITION BY DATE(started)
      ORDER BY rows_done DESC
    ) AS rnk
  FROM batch_jobs
  WHERE rows_done IS NOT NULL
)
SELECT job_date, job_name, rows_done
FROM ranked
WHERE rnk = 1
ORDER BY job_date

Common follow-up questions

Common follow-up questions

• How would you collapse ties to a single winner instead? (Switch to `ROW_NUMBER` and add `job_id` ASC as a second sort key, so exactly one row per day survives and the choice is reproducible.)
• What if in-flight jobs should be excluded? (Filter `LOWER(status) = 'completed'` inside the CTE: the column holds both 'completed' and 'Completed'.)
• Return the smallest job per day too? (Add a second rank ordered by `rows_done` ascending and keep rows at rank 1 in either.)