Assign Deterministic Modulo Shards
Calculate shard_id as the remainder of record_id divided by four.
- CASE expressions
- Numeric functions
- Sorting
Exercise brief
Understand the request
Distributed ingestion engineer A parallel backfill assigns each record to one of four repeatable worker shards.
A parallel backfill assigns each record to one of four repeatable worker shards. Calculate shard_id as the remainder of record_id divided by four.
Return
- Return record_id and shard_id.
- Order by record_id ascending.
Constraints
- Use modulo rather than a hard-coded CASE expression.
- Shard IDs must stay in the range 0 through 3.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
function_cases
record_idINTEGERfirst_nameTEXTlast_nameTEXTlabelTEXTemailTEXTbackup_emailTEXTphoneTEXTraw_unitsTEXTraw_quantityTEXTraw_statusTEXTactual_valueREALtarget_valueREALcompleted_unitsREALelapsed_hoursREAL
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Modulo returns the remainder after integer division.
Hint 2
Dividing by four produces only remainders 0, 1, 2, and 3.
Hint 3
Use record_id % 4 or MOD(record_id, 4), depending on the engine.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT record_id, record_id % 4 AS shard_id FROM function_cases ORDER BY record_id;Why this works
Modulo is useful beyond even/odd checks: stable remainder buckets support parallel extraction, partition routing, and deterministic samples.
Success check
Every record is assigned deterministically to the correct one of four shards.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| record_id | shard_id |
|---|---|
| 101 | 1 |
| 102 | 2 |
| 103 | 3 |
| 104 | 0 |
| 105 | 1 |
| 106 | 2 |
| 107 | 3 |
| 108 | 0 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
SELECT Statements
Select columns, filter rows, remove duplicates, and order query results.
CASE Statements & Conditional Logic
Build NULL-aware classifications, precedence-safe decisions, flags, scores, and guarded calculations with portable CASE expressions.
Date Operations & Time-Based Analytics
Practice date arithmetic, safe timestamp ranges, calendar bucketing, dense time series, rolling windows, growth, and cohort analysis.
Open the interactive workspace and practice across SQL topics.