Can You Crack This SQL Query in 30 Minutes?

A time-boxed SQL challenge: build the best data team you can on a fixed budget — using window functions.

By Santosh Joshi · Aug 11, 2026

Here's a SQL puzzle that looks friendly and turns out to be a genuine test of how well you think in windows. Grab a timer, give yourself 30 minutes, and try it before you scroll to the solution.

The challenge

A company just secured a $1,000,000 budget to build a data team. You're handed a candidate table — a mix of senior and junior data engineers with their salary expectations. Everyone at a given level is considered equally skilled, so any of them is fair to hire.

The hiring rules are simple, and greedy:

1. Hire as many seniors as possible first.
2. Spend whatever's left hiring as many juniors as possible.

Write one SQL query that returns the optimal list of hires.

[!TIP] The whole point is the attempt — try it for 30 minutes before reading on.

The data

CREATE TABLE IF NOT EXISTS candidate (
  candidate_id INT,
  experience   VARCHAR(50),
  salary       INT
);

INSERT INTO candidate (candidate_id, experience, salary) VALUES
(1,'Senior',200000),(2,'Senior',95000),(3,'Senior',110000),(4,'Senior',105000),
(5,'Senior',120000),(6,'Senior',185000),(7,'Senior',190000),(8,'Senior',115000),
(9,'Senior',180000),(10,'Senior',98000),(11,'Junior',70000),(12,'Junior',75000),
(13,'Junior',60000),(14,'Junior',61000),(15,'Junior',55000);

Work it out by hand first

The key insight: to maximize headcount at each level, spend on the cheapest candidates first.

Sort seniors by ascending salary and keep a running total until the next hire would break the budget. The cumulative salary reaches $823,000 at seven seniors; the eighth ($185,000) would push it to $1,008,000 — over budget — so you stop. That leaves $177,000.

Run the same logic for juniors against that $177,000: you can afford three (at $55k, $60k, and $61k), spending $176,000. Total spend: $999,000, with $1,000 to spare.

So the answer is 7 seniors + 3 juniors. The real question is how to express "cheapest-first, stop before the budget runs out" in SQL.

The approach: window functions

A running total is exactly what a windowed SUM(...) OVER (ORDER BY salary ...) gives you. Compute the cumulative salary per level, subtract it from the budget, and keep the rows where the remaining budget is still positive.

The solution

WITH senior_candidates AS (
  SELECT *,
         1000000 - SUM(salary) OVER win AS remaining_budget
  FROM   candidate
  WHERE  experience = 'Senior'
  WINDOW win AS (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
),
selected_seniors AS (
  SELECT * FROM senior_candidates WHERE remaining_budget > 0
),
combined AS (
  SELECT *,
         (SELECT MIN(remaining_budget) FROM selected_seniors)
           - SUM(salary) OVER win AS remaining_budget
  FROM   candidate
  WHERE  experience = 'Junior'
  WINDOW win AS (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

  UNION ALL
  SELECT * FROM selected_seniors
)
SELECT *
FROM   combined
WHERE  remaining_budget > 0
ORDER BY experience DESC, salary ASC;

The result

Seven seniors, three juniors, $999,000 spent.

How it works

1. Seniors, cheapest first. The first CTE orders seniors by ascending salary and computes 1,000,000 - running_total as remaining_budget. The frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is what turns SUM(salary) OVER win into a cumulative sum.
2. Keep the affordable ones. remaining_budget > 0 drops any senior whose salary tips the running total past the budget.
3. Carry the leftover to juniors. MIN(remaining_budget) over the selected seniors is precisely the money left after the last senior — $177,000. The junior CTE runs the same cumulative trick, subtracting from that leftover.
4. Combine and filter. UNION ALL stacks the selected seniors and juniors, and the final remaining_budget > 0 trims any junior that would overspend. ORDER BY experience DESC, salary ASC lists seniors first.

[!NOTE] Spending less per hire is what leaves room for more hires — that's why "cheapest first" maximizes headcount at each level.

Your turn

Window functions are one clean way to solve this, but they're not the only one — running totals via self-joins or procedural logic work too. How would you approach it?

This is exactly the kind of thinking data-engineering interviews and certification exams reward. If you want to sharpen it, put it to work with our Databricks certification practice tests.