Articles
CodeSignal SQL Questions: Database Tasks, Output Checks, and Preparation
Prepare for CodeSignal SQL questions with a worked database task, latest-state logic, null handling, test cases, and checks for the exact expected output.
A CodeSignal SQL question can look finished when the query runs and returns plausible numbers. The harder check is whether those numbers answer the exact question: the right entities, the right moment in time, the right treatment of missing data, and the requested output columns.
Write the result contract before the query, inspect intermediate rows, and create small counterexamples that expose a wrong interpretation. Syntax practice matters, but it cannot repair an incorrect definition of “latest” or “active.”
This guide covers SQL/database tasks. For the broader mix of file analysis and assessment preparation, see our CodeSignal Data Analytics Assessment guide.
Evidence boundary: CodeSignal documentation establishes the platform capabilities described below. No candidate report is used to claim a universal question set, score cutoff, or time limit. Sources were checked on September 15, 2026.
Identify the database task and its environment
Official platform facts: CodeSignal supports SQL/database questions with environment-specific configuration, input and expected-output test cases, and a configurable number of visible tests. Its author documentation lists MySQL, PostgreSQL, and Microsoft SQL environments. Those are platform capabilities; they do not mean every invitation offers all three. SQL/database question documentation.
For certified assessments, CodeSignal specifically lists those three database environments under the Data Analytics Assessment. Do not transfer the General Coding Assessment's programming-language list or format to a database question. Check what your assigned task actually provides. Certified assessment environments.
Before practicing, distinguish a query over existing tables from a task that asks you to load or transform a file. Identify whether the editor expects a query, a function, or another supplied interface. Read the schema, constraints, and examples together. A sample row does not establish that a column is unique or non-null unless the contract says so.
CodeSignal's README documentation describes sections for question type, language information, and verdicts. Use that information to interpret your environment instead of guessing from an unrelated screenshot or tutorial. Using the assessment README.
Write an output contract before writing SQL
A useful contract answers six questions in plain language. What does one output row represent? Which entities must appear? Which source records are eligible? How are ties resolved? How do missing values behave? Which columns and ordering must the result have?
For example, “count delayed shipments by hub” is incomplete. It might mean shipments that were ever delayed, shipments whose latest scan is delayed, or shipments delayed at a historical cutoff. The same database can produce three different correct answers to those three questions.
Separate the population from the measurement. “Every hub, including empty hubs” defines the population. “Shipments whose latest eligible scan says delayed” defines a measurement. Applying the measurement condition as a final filter can accidentally remove the population you were asked to retain.
Record the requested aliases and column order as part of this contract. Do not depend on a grader tolerating different labels, extra diagnostic columns, or an arbitrary row order. Equally, do not claim every CodeSignal configuration compares results identically: follow the task's stated output requirements and use the displayed verdict details when investigating a mismatch.
Worked exercise: shipment status at a cutoff
This original practice problem is deliberately small enough to solve by hand. Assume three tables:
hubs(hub): one row per unique, non-null hub name.shipments(shipment_id, hub): one row per shipment, assigned to exactly one listed hub.scans(scan_id, shipment_id, scan_minute, status): uniquely identified scans; the other fields are non-null, and status is eitherokordelayed.
Minutes are integer offsets from a fixed starting point, so the exercise isolates query logic from timestamp parsing. Use scans at or before minute 60. For tied minutes within a shipment, the higher scan ID wins. A shipment without an eligible scan has unknown status.
Return every hub, alphabetically, with columns hub, shipments, delayed, and unknown. An empty hub gets three zero counts. The input records are:
hubs: East, North, West
shipments: (1, West), (2, West), (3, East), (4, East)
scan_id shipment_id scan_minute status
11 1 10 delayed
12 1 20 ok
21 2 30 ok
22 2 30 delayed
30 3 50 ok
31 3 61 delayed
Work out the answer before reading the query. Shipment 1 recovered, so it is currently okay. Shipment 2 is delayed because scan 22 wins the tie. Shipment 3 is okay at the cutoff; its later delay is outside the snapshot. Shipment 4 is unknown. North has no shipments.
The exact ordered result is:
hub shipments delayed unknown
East 2 0 1
North 0 0 0
West 2 1 0
That result already gives you several checks. Shipment counts sum to four. Exactly one shipment is delayed and one is unknown. Three hub rows must remain. A query that returns two rows is wrong even if its West count is correct.
Filter eligible events, choose the latest, then aggregate
The query follows the contract in stages. First restrict the scans to the cutoff. Next rank each shipment's eligible scans. Finally join the selected state to the complete hub population and count.
WITH ranked AS (
SELECT shipment_id, status,
ROW_NUMBER() OVER (
PARTITION BY shipment_id
ORDER BY scan_minute DESC, scan_id DESC
) AS rn
FROM scans
WHERE scan_minute <= 60
)
SELECT h.hub AS hub,
COUNT(s.shipment_id) AS shipments,
SUM(CASE WHEN r.status = 'delayed'
THEN 1 ELSE 0 END) AS delayed,
SUM(CASE WHEN s.shipment_id IS NOT NULL
AND r.shipment_id IS NULL
THEN 1 ELSE 0 END) AS unknown
FROM hubs AS h
LEFT JOIN shipments AS s ON s.hub = h.hub
LEFT JOIN ranked AS r
ON r.shipment_id = s.shipment_id
AND r.rn = 1
GROUP BY h.hub
ORDER BY h.hub ASC;
Verification note: this query and the counterexamples below were executed locally in SQLite 3.51.0. That verifies the exercise's results, not execution inside CodeSignal or compatibility with every assigned database version. Re-run practice in your intended environment and check its supported syntax.
Documented SQL behavior: PostgreSQL explains that a window function sees rows remaining after the query's filtering, and that tied rows can receive unspecified ROW_NUMBER ordering. Microsoft's SQL Server reference also emphasizes the need for unique ordering combinations when repeatable row selection matters. Here, the explicit unique scan ID completes the ordering. PostgreSQL window functions, SQL Server ROW_NUMBER.
The tie rule is part of this exercise, not a universal business rule. In a real task, a higher ID might not represent the authoritative event. Use the rule supplied by the prompt; ask for clarification when a live interviewer leaves it unspecified.
Why plausible queries return the wrong answer
Filtering to status = 'delayed' inside the ranking stage answers a different question: what was the latest delayed scan? It removes shipment 1's recovery before the ranking can see it. West then has two delayed shipments instead of one.
Applying the cutoff only after ranking all events creates another error. Shipment 3's minute-61 scan can become its top row, displacing the minute-50 scan that should define the snapshot. Discarding that top row does not automatically restore the older eligible row. Eligibility must be applied before selection for this contract.
Moving r.rn = 1 from the join condition to a final WHERE clause removes unmatched rows. Shipment 4 and the empty hub can disappear because their joined rank is null. Official PostgreSQL documentation illustrates how restrictions in ON and WHERE can produce different outer-join results. Table expressions and outer joins.
COUNT(*) creates a subtler mistake: the empty hub still produces a null-extended row after the left join. Counting the non-null shipment key correctly produces zero. The unknown-status expression also checks that a shipment exists, so North's empty placeholder does not become an unknown shipment.
Finally, a broad DISTINCT is not a repair for incorrect grain. Joining every historical scan and then counting can duplicate shipments. Removing visually identical result rows does not reconstruct one state per shipment. Fix the selection stage and verify its key before aggregation.
Build counterexamples that diagnose one assumption
Start with the tiny fixture, then change one property at a time. The point is to make a particular wrong query fail for an understandable reason.
| Test change | Expected effect |
|---|---|
| Add an older delay | Current status stays unchanged. |
| Add a scan at minute 60 | The boundary scan is eligible. |
| Add a scan at minute 61 | The snapshot stays unchanged. |
| Reverse scan insertion order | The explicit tie rule still wins. |
| Remove every scan | Existing shipments become unknown. |
| Remove every shipment | All hubs remain with zero counts. |
Check intermediate results as well as totals. The selected-state stage should have no more than one row per shipment. The final result should have exactly one row per hub. Compare keys and counts before worrying about display formatting.
For unordered results, compare multisets rather than sets when duplicate multiplicity matters. For an ordered contract such as this one, compare the ordered rows. A set comparison can hide duplicate rows; an exact string comparison can confuse cosmetic formatting with a value error. Pick a comparison that matches the contract you are trying to validate.
When working with monetary values or ratios, also inspect the declared numeric types, rounding rule, and behavior of an empty denominator. This exercise uses integer counts so those decisions are unnecessary. In another prompt they can be central; do not carry an accidental integer-division assumption into a percentage calculation.
Practice five different failure modes
| Practice question | What to check next |
|---|---|
| Find Payroll Bookings in the Latest Active Month by Country | Time scope |
| Compute each member’s current notification status | Latest state |
| Compare SQL counts, windows, and NULL semantics | Null behavior |
| Write conditional aggregates with CASE WHEN | Count rules |
| Debug row loss after SQL joins | Missing rows |
After each exercise, write a one-sentence explanation of your first incorrect assumption. “I forgot SQL syntax” is usually too broad. “I selected the newest event before restricting the snapshot” identifies a repeatable reasoning error and suggests the next counterexample.
Keep your diagnostic queries separate from the final answer. A temporary query that shows ranks, IDs, and timestamps helps you understand a failure, but those extra columns may violate the requested output. Before submitting, return to the smallest query that produces exactly the specified relation.
Rehearse the complete platform workflow
Official practice guidance: CodeSignal offers practice question formats to help candidates become familiar with its IDE. Its documentation says practice activity and performance are not shown to companies, and describes a “Get familiar with the platform” option associated with a pending invitation. This is a way to inspect the relevant format, not a guarantee that practice questions match the assessment. Practice content overview.
Use a practice session to locate the schema, environment information, test output, and submission controls. Follow the rules shown in your actual invitation. Do not substitute a remembered time limit or permitted-tool policy from a different assessment.
Before finishing a database task, check the entity population, latest-state rule, null policy, column names, ordering, and final submitted code. If a visible test fails, classify the mismatch first: missing rows, extra rows, wrong values, syntax, or output shape. That classification is more useful than changing several clauses at once.
For continued practice, choose a focused exercise from the SQL and data-manipulation question collection. Aim to explain why each output row exists and which smallest test would prove your query wrong.