# Kings for a Day

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

Canonical URL: <https://datadriven.io/problems/kings_for_a_day>

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.

## Worked solution and explanation

### What this is really asking

This is a top-1-per-group problem wearing a daily-leaderboard costume. The skill it probes: pull the single biggest row per day without throwing away the columns that identify it. Anyone can get the winning row count with a GROUP BY and a MAX. The trap is that the moment you aggregate, `job_name` is gone, and joining it back reopens the tie problem you were trying to avoid. Rank inside a window instead, then keep the top of each day, and ties fall out for free.

---

### Break down the requirements

#### Step 1: Bucket by date, ignore uncounted jobs

Cast `started` to a date so 23:59 and 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). Ties at the max all get rank 1, so a day can crown more than one winner, which is exactly what the prompt asks for.

#### Step 3: Keep rnk = 1

Window functions cannot live in WHERE, so filter rnk = 1 in the outer query and ORDER BY job_date.

---

### The solution

**Top job per day**

```sql
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
```

> **Null counts don't win**
>
> A job with a null `rows_done` never recorded how many rows it moved, so it cannot be the day's biggest mover. Filter `rows_done IS NOT NULL` inside the CTE. Leave it in and a day whose only job logged nothing still crowns a winner, reported with a null count.

> **Cost Analysis**
>
> One pass over 800k rows plus a partitioned sort. An index on (started, rows_done DESC) lets the planner stream partitions. ROW_NUMBER is cheaper but silently drops tied winners, which the prompt explicitly wants kept.

> **Interviewers Watch For**
>
> Choice of RANK vs ROW_NUMBER vs DENSE_RANK, casting `started` to a date, and knowing the CTE is required because window functions cannot appear in WHERE. Picking ROW_NUMBER here loses the tied jobs the day is supposed to crown.

> **Common Pitfall**
>
> GROUP BY DATE(started) with MAX(rows_done) gives the right number but loses `job_name`. Joining back to recover the name reintroduces the tie problem the window already solved.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- How would you collapse ties to a single winner instead? _(Add a secondary key (job_id ASC) and switch to ROW_NUMBER so exactly one row per day survives.)_
- What if in-flight jobs should be excluded? _(Filter `status = 'completed'` inside the CTE so unfinished rows cannot win the day.)_
- Return the smallest job per day too? _(Add a second RANK with ORDER BY rows_done ASC and filter on either rank outside.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/kings_for_a_day)
- [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.