Builds for External Playtesters

A game studio shares test builds with an external playtest group. By studio convention, only builds with an odd build_id go out externally, and a build whose status is 'blocked' is never shared.

Return build_id, codename and stability_score for every odd-numbered build that is not blocked. Sort by stability_score from highest to lowest; if two builds have the same score, put the smaller build_id first. If no build qualifies, return no rows.

Tables

game_builds — One row per compiled build of the game.

Example

game_builds

Expected output

Return the rows in exactly the order described. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Select & Filter · problem 2 of 70 · SQLite

The idea

Two row-level conditions decide eligibility. Oddness is a remainder check: build_id % 2 = 1. The status rule is a simple inequality, status <> 'blocked', which keeps both passed and flaky builds. Both must hold, so they are joined with AND.

The ordering has two keys. stability_score DESC puts the most stable builds first, and build_id ASC settles ties so that the output order is fully determined. Without a tie-breaker, two builds with the same score could legally come back in either order, and an exact-order check would be flaky.

The alternative tests the lowest bit with (build_id & 1) = 1, another way to spot odd integers, and lists the allowed statuses with IN instead of excluding the forbidden one. The IN version is only equivalent while the status list stays closed; the <> form automatically lets new non-blocked statuses through.

Solution

SELECT build_id,
       codename,
       stability_score
FROM game_builds
WHERE build_id % 2 = 1
  AND status <> 'blocked'
ORDER BY stability_score DESC,
         build_id ASC;

Another way

SELECT build_id,
       codename,
       stability_score
FROM game_builds
WHERE (build_id & 1) = 1
  AND status IN ('passed', 'flaky')
ORDER BY stability_score DESC,
         build_id;

Why common answers fail

Checks for a remainder of 0, which selects even ids instead of odd ones.

SELECT build_id, codename, stability_score FROM game_builds WHERE build_id % 2 = 0 AND status <> 'blocked' ORDER BY stability_score DESC, build_id;

Sorts in ascending order, but the task wants the most stable builds first.

SELECT build_id, codename, stability_score FROM game_builds WHERE build_id % 2 = 1 AND status <> 'blocked' ORDER BY stability_score, build_id;

Keeps only 'passed' builds, so 'flaky' builds, which are not blocked, are wrongly excluded.

SELECT build_id, codename, stability_score FROM game_builds WHERE build_id % 2 = 1 AND status = 'passed' ORDER BY stability_score DESC, build_id;

Breaks score ties with the larger build_id first instead of the smaller.

SELECT build_id, codename, stability_score FROM game_builds WHERE build_id % 2 = 1 AND status <> 'blocked' ORDER BY stability_score DESC, build_id DESC;

What the interviewer is checking

A quick test of the modulo operator, combining filters, and writing a deterministic multi-key sort.

Mistakes to avoid

• Using % 2 = 0, which finds even numbers.
• Forgetting DESC, or not adding a tie-breaker.
• Excluding 'flaky' builds by testing status = 'passed'.

Follow-ups to expect

• Return only the top 3 eligible builds. (LIMIT 3)
• Alternate builds between two tester groups. (CASE WHEN build_id % 2 = 1 THEN 'A' ELSE 'B' END)
• Rank eligible builds, giving tied scores the same rank. (RANK() OVER (ORDER BY stability_score DESC))

In real work

Sharding, A/B splits and round-robin assignment often use id modulo arithmetic, combined with a status filter and a ranked list.

Practice next