Codility SQL Test: Query Tasks, Edge Cases, and Preparation

A Codility SQL test can punish a query that looks perfect on the visible rows. The usual failure is not obscure syntax. It is a silent contract mistake: the query returns the wrong grain, turns a LEFT JOIN into an inner join, counts duplicated entities, mishandles NULL, or assumes the sample contains every important case.

The most useful preparation method is therefore to treat each task as a compact data contract. Translate the prompt into one output row, one set of inclusion rules, one definition for duplicates, and one ordering rule. Then build adversarial examples before submitting. For hands-on practice, start with Write conditional aggregates with CASE WHEN.

Codility SQL Test: The Direct Answer

Codility's official Task Library says its SQL tasks assess SQL skills, support PostgreSQL and SQLite, and are scored for correctness. Its candidate-interface documentation says candidates can take a demo before starting, run tests repeatedly, and add custom test cases. Those are platform facts. They do not establish your employer's task count, time limit, difficulty, permitted resources, or scoring threshold; the employer configures the assessment.

In practice, use the first minutes to identify the dialect and output contract. Write the simplest correct query in stages, inspect intermediate grains, and spend the final portion on counterexamples rather than cosmetic refactoring. Sample output is evidence that one fixture passed, not proof that the query matches the full contract.

What Official Documentation Confirms—and What It Does Not

Official facts: Codility describes SQL as a correctness-scored task type and lists PostgreSQL and SQLite among supported technologies. The current technology page exposes specific runtime versions, but candidates should follow the version shown in their own task because the platform documentation can change. The interface also supports custom tests. That makes deliberate edge-case testing part of the available workflow, not an optional afterthought.

Candidate reports, treated as anecdotes: one 2021 Reddit poster described a SQL submission that matched the sample but passed only three of fourteen correctness checks; the same person later said a corrected query passed after repeated attempts. A 2026 Citi assessment thread contains reports of mixed SQL, Python, PySpark, and multiple-choice content. These accounts are self-reported, unverified, employer-specific, and from different cycles. They support the warning that configurations and hidden cases vary. They do not justify a universal Codility SQL format.

Editorial inference: because the official scoring criterion is correctness and the interface allows custom cases, the highest-value preparation is to expose false assumptions quickly. The rest of this article turns that inference into a repeatable method.

Convert the Prompt into a Query Contract

Before touching SQL, write five lines:

Contract item	Question to answer	Typical silent failure
Output grain	What does one row represent?	grouping by an extra column creates duplicates
Population	Which base rows must remain?	a filter on the nullable side drops zero-result entities
Identity	What counts once?	joining two detail tables multiplies facts
Time boundary	Which timestamps are included?	BETWEEN double-counts a midnight boundary
Tie/order rule	Are ties included, and is output order required?	ROW_NUMBER() silently breaks a requested tie

Suppose the prompt asks for one row per customer, including customers with no completed orders, with the completed total in January. The correct base grain is customer. That suggests starting from customers, filtering completed January orders in the join condition or a pre-aggregated CTE, and applying COALESCE only to the final numeric total.

If you instead start from orders, customers with no orders cannot appear. If you use a LEFT JOIN but put o.status = 'completed' in WHERE, unmatched rows become ineligible. If you join orders directly to order_items and payments, the many-to-many fanout can inflate totals. All three queries can look plausible against a friendly sample.

Use a Staged Query, Not One Clever Expression

A reliable assessment query makes each grain visible. For the customer example:

WITH completed_orders AS (
  SELECT
    customer_id,
    order_id,
    order_total
  FROM orders
  WHERE status = 'completed'
    AND created_at >= TIMESTAMP '2026-01-01 00:00:00'
    AND created_at <  TIMESTAMP '2026-02-01 00:00:00'
),
customer_totals AS (
  SELECT
    customer_id,
    COUNT(*) AS completed_order_count,
    SUM(order_total) AS completed_amount
  FROM completed_orders
  GROUP BY customer_id
)
SELECT
  c.customer_id,
  c.customer_name,
  COALESCE(t.completed_order_count, 0) AS completed_order_count,
  COALESCE(t.completed_amount, 0) AS completed_amount
FROM customers AS c
LEFT JOIN customer_totals AS t
  ON t.customer_id = c.customer_id
ORDER BY completed_amount DESC, c.customer_id;

This is not presented as a Codility task or answer. It is an editorial practice fixture. Its value is structural: the first CTE defines eligible facts, the second proves one row per customer ID, and the outer query preserves the complete customer population. You can run SELECT customer_id, COUNT(*) ... HAVING COUNT(*) > 1 against the intermediate result to test the grain directly.

Build the Hidden-Test Matrix Yourself

The supporting visual below organizes the cases worth trying. You do not need a large dataset; six to ten rows can disprove most bad assumptions.

Empty and zero-result cases

Test an empty fact table, an entity with no matching fact, and a filter interval with no rows. Decide whether the expected result is no rows or rows containing zero. SUM over no inputs returns NULL in many common situations, while business output may require zero. Apply COALESCE only when the prompt defines that conversion.

Duplicates and join fanout

Add two detail rows per order and two status rows per customer. Ask whether identity is order_id, (customer_id, day), or every physical row. DISTINCT at the final line can hide a broken join without repairing an inflated sum. Pre-aggregate each many-side to the required grain before combining it.

NULL and three-valued logic

Test a nullable amount, category, foreign key, and timestamp. COUNT(*) counts rows; COUNT(column) ignores null values; NOT IN can behave unexpectedly when its subquery contains NULL. Use IS NULL, EXISTS, or NOT EXISTS when they express the requirement more directly, and state how unknown values should be treated.

Dates, timestamps, and time zones

Prefer half-open intervals such as created_at >= start_ts AND created_at < end_ts. They compose cleanly across adjacent periods. Confirm whether a “day” is UTC, a named business time zone, or a date already stored by the source. Casting a timestamp to a date before defining the time zone can assign an event to the wrong day.

Ties, ranking, and deterministic output

Choose ROW_NUMBER, RANK, or DENSE_RANK from the requested semantics. If all tied items at third place must remain, RANK() <= 3 may be appropriate; ROW_NUMBER() <= 3 returns exactly three rows and arbitrarily excludes a tie unless its ordering fully resolves it. Add a stable secondary sort when the expected output specifies ordering.

Integer arithmetic and dialect differences

Test a ratio such as 1 / 2, division by zero, case-insensitive matching, date arithmetic, and boolean aggregation. PostgreSQL and SQLite overlap substantially but are not identical. Avoid importing MySQL or SQL Server functions from muscle memory. Check the task's selected technology and prefer standard expressions when the prompt does not demand a dialect-specific feature.

A Time-Boxed Assessment Workflow

For a 45-minute SQL task, a practical allocation is:

1. Minutes 0–5: read the schema, output columns, examples, dialect, and ordering. Write the output grain and time interval in a comment.
2. Minutes 5–15: produce the smallest correct base query. Run it on the sample and inspect raw intermediate rows.
3. Minutes 15–25: add grouping, windowing, or ranking one stage at a time. Verify row counts after every join.
4. Minutes 25–35: create custom cases for empty input, duplicates, NULL, boundary timestamps, ties, and zero denominators.
5. Minutes 35–42: remove accidental columns, confirm aliases and exact ordering, and check that every requirement maps to a clause.
6. Minutes 42–45: run all tests once more and submit the clearer correct version.

Adjust this allocation to the timer shown in your invitation. It is a preparation model, not a Codility rule. If there are multiple tasks, triage them first and reserve submission time for each; do not assume one difficult query deserves the entire test.

How to Debug a Sample-Passing Query

When the visible output matches but you distrust the query, avoid random rewrites. Compare actual row counts with expected cardinality at each step:

SELECT COUNT(*) AS rows,
       COUNT(DISTINCT order_id) AS orders,
       COUNT(DISTINCT customer_id) AS customers
FROM joined_data;

Then isolate one suspicious entity and display the join keys. If totals are multiplied, fix the earliest grain violation. If entities disappear, inspect join direction and predicates. If only boundary rows differ, print the uncast timestamps. If ranking differs, show the raw metric and every ordering key.

Performance matters after correctness. Remove unnecessary sorts, avoid wrapping indexed filter columns in functions when a range predicate works, and reduce facts before wide joins. Still, do not claim an index exists unless the schema says so. In an assessment, explain a potential index separately from the submitted query.

Prepare Without Memorizing Proprietary Tasks

Trying to memorize platform questions is fragile and can cross an employer's assessment rules. Build five small fixtures instead: aggregation with missing groups, many-to-many fanout, latest row per entity, ranked groups with ties, and time-window metrics. Solve each in both PostgreSQL-style and portable SQL, then list what changes in SQLite.

Use the official demo if it is available from your invitation. It reduces interface overhead without starting the real test, according to Codility's candidate-interface page. During the actual assessment, follow the stated policy on documentation, external tools, and AI. The ethical goal is to demonstrate your own reasoning under the employer's rules.

Final Preparation Checklist

Before submitting, say the contract aloud: “one row per ___; includes ___; excludes ___; duplicates mean ___; the time interval is ___; ties are handled by ___.” Confirm that every non-aggregated selected column belongs in the grouping logic, every nullable column has intentional behavior, every join preserves or removes rows deliberately, and the requested column order matches exactly.

The strongest Codility SQL preparation is not a bag of tricks. It is a habit of disproving your own query. Official documentation tells you the available task family and interface capabilities. Candidate reports show why visible examples can create false confidence, but they remain anecdotal. Your defensible conclusion is narrower: define the data contract, expose the dangerous edge cases, and submit only after your own tiny fixtures fail to break it.

Sources and Further Reading

• Codility Task Library: SQL task type and scoring
• Codility Candidate Interface
• Codility supported technologies
• Candidate report: sample passed but hidden correctness checks failed
• Candidate reports: 2026 Citi Codility assessment configurations

Research note: official pages and linked candidate reports were checked on September 14, 2026. Official sources establish only the cited Codility capabilities. Candidate reports are anecdotal and employer-specific. The task strategy, examples, and time allocation are editorial inference.