Articles

HackerRank SQL Assessment: Practice Joins, Windows, and Exact Output Checks

Prepare for a HackerRank SQL assessment with joins, window functions, engine checks, and exact output tests for missing rows, ties, and decimal formats.

A HackerRank SQL assessment can reject a query that looks convincing at first glance. The totals may seem reasonable while a join removes a customer, a ranking returns an arbitrary tied row, or the final columns appear in the wrong order. Prepare to prove both the query's meaning and its output contract. Memorizing more syntax will not fix an incorrect assumption about what one result row represents.

This guide combines official platform documentation with an original SQL exercise and a repeatable checking method.

What a HackerRank SQL assessment actually checks

Official platform facts: HackerRank's candidate documentation describes database questions as SQL queries against supplied tables. Candidates select an available database, run their query, inspect the result, and submit. It emphasizes exact output formatting, including numerical representation and case. HackerRank's database-question guide is the relevant reference for this workflow.

The employer-facing documentation adds an important detail: question authors choose the permitted databases and can configure whether row order matters. It describes automatic database-question scoring as full credit for a matching result or zero for a mismatch, with manual score adjustment available to reviewers. That is not a promise of partial credit or an employer's hiring cutoff. Database question setup and scoring explains those controls.

Preparation inference: treat every prompt as a contract you must extract before coding. Identify the entities, eligible records, requested columns, grouping level, tie rule, ordering, and formatting. If the statement does not specify ordering, do not invent a business rule merely to reproduce the visual arrangement of a sample.

This article covers database-query preparation. A notebook task that reads CSV files and produces an artifact is a different workflow; see the separate HackerRank data science assessment guide.

Choose the engine before choosing the syntax

Official environment snapshot, checked September 15, 2026: HackerRank's execution-environment page lists MySQL 8.0.33, PostgreSQL 14.3, and Microsoft SQL Server 2022 among its database environments. These are documented platform versions, not a guarantee that every question enables each option. Check the actual dropdown and environment details for your assessment. HackerRank execution environment.

This matters when your memorized answer uses LIMIT, TOP, date arithmetic, string concatenation, or a vendor-specific function. Translating those details under time pressure can consume more effort than solving the underlying problem. Choose one available dialect that you know, and practice its date boundaries, null handling, and decimal behavior before the assessment.

The original exercises below use broadly familiar SQL constructs. Their relational results were checked locally with SQLite; that does not verify HackerRank's database version, permissions, or output serializer. Use your invitation's sample environment to check those platform details when available. HackerRank documents where candidates can inspect supported languages and execution information.

Read the schema as a set of promises

Before joining tables, say what makes a row unique. A customer table might have one row per customer; an orders table has one row per order; a promotions table can have several rows per order. That level of detail is the table's grain. It determines whether summing after a join remains meaningful.

Here is an original preparation fixture, not a reported assessment question. Customers are Ada, Bo, and Cy. The task is: return every customer, their number of paid orders, and paid revenue, ordered by customer ID. An absent paid order contributes a count of zero and revenue of zero. All amounts in this fixture are non-null integers.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY, name TEXT
);
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER, status TEXT,
  amount INTEGER, ordered_at TEXT
);
INSERT INTO customers VALUES (1,'Ada'),(2,'Bo'),(3,'Cy');
INSERT INTO orders VALUES
  (11,1,'paid',20,'2026-09-01 09:00:00'),
  (12,1,'paid',20,'2026-09-01 09:00:00'),
  (21,2,'paid',9,'2026-09-01 10:00:00'),
  (31,3,'cancelled',50,'2026-09-01 11:00:00');

Write the expected result before the query: Ada has two paid orders totaling 40; Bo has one totaling 9; Cy has none. This small prediction gives you an independent reference. Running a complicated query and then accepting whatever it returns does not test the business rule.

Join practice: preserve customers without paid orders

The paid-order condition belongs in the join for this task:

SELECT c.customer_id,
       COUNT(o.order_id) AS paid_orders,
       COALESCE(SUM(o.amount), 0) AS revenue
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.status = 'paid'
GROUP BY c.customer_id
ORDER BY c.customer_id;

The ordered result is (1, 2, 40), (2, 1, 9), (3, 0, 0). Notice that the prompt requests customer IDs, not names, so adding names would change the output contract.

Move o.status = 'paid' into WHERE and Cy disappears. After the outer join produces Cy's unmatched row, the filter rejects it. PostgreSQL's official table-expression documentation explains why filtering before and after an outer join has different effects.

There is a second trap: COUNT(*) would count the unmatched placeholder row. COUNT(o.order_id) counts only non-null order IDs. Similarly, an empty sum needs an explicit replacement if the requested answer is zero. These aggregate behaviors are documented in PostgreSQL's aggregate reference.

During practice, make the fixture harder by adding a fourth customer with no order rows at all. Cy already tests a customer whose orders exist but are ineligible. Those are different paths through the query, even though both should produce zero here.

Join practice: catch a total inflated by fanout

Now add a promotions table with two rows for order 11 and one for order 12. A direct join from Ada's orders to promotions repeats order 11. The resulting paid revenue becomes 60, although Ada bought only 40 of goods.

This is a grain error: the intermediate result represents order-promotion pairs. It cannot safely sum an order-level amount without accounting for that repetition. If the task only asks whether an order has a promotion, use an existence condition. If it needs promotion statistics, aggregate promotions to one row per order before joining.

Do not repair this with SUM(DISTINCT amount). Ada has two legitimate orders worth 20 each; distinct amounts collapse them into 20. Distinct values are not distinct business events.

To locate the error, group the intermediate join by order_id and count rows. More than one row per order may be intentional, but it tells you exactly where an order-level sum becomes unsafe. Compare the expected number of entities before comparing the final money total.

Window practice: latest row, tied ranks, and running totals

Suppose the next task asks for one latest paid order per customer, resolving equal timestamps with the larger order ID. Use a window to select the winner, then order the final result separately:

WITH ranked AS (
  SELECT customer_id, order_id,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY ordered_at DESC, order_id DESC
         ) AS rn
  FROM orders
  WHERE status = 'paid'
)
SELECT customer_id, order_id
FROM ranked
WHERE rn = 1
ORDER BY customer_id;

The answer is (1, 12) and (2, 21). Cy is absent because this task asks for paid orders rather than every customer. A correct answer to the earlier task would be wrong here: the required population changed.

If the task instead requests every order tied for the latest timestamp, rank by timestamp alone and retain rank one. Adding order_id to that ranking would destroy the timestamp tie you were asked to preserve. ROW_NUMBER, RANK, and DENSE_RANK express different policies; their differences are defined in PostgreSQL's window-function reference.

For a running sum, inspect the frame as well as the ordering. In the fixture, Ada's equal timestamps can cause a default peer-inclusive frame to show 40 on both rows. An explicitly ordered row-by-row accumulation should instead show 20 and then 40:

SUM(amount) OVER (
  PARTITION BY customer_id
  ORDER BY ordered_at, order_id
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

That specification is a policy choice, not a universally better default. Explain whether the requested total advances by individual order or includes all orders at an equal timestamp together. The official window-function tutorial illustrates this distinction.

Check exact output in a separate pass

Once the query's meaning is correct, inspect its presentation. Preparation recommendation: use the following checks as a final pass, with the prompt deciding the required behavior.

Result rule	Failure example
Column order	Values in reversed columns
Row count	One order repeated
Tie order	Equal scores lack a tie-break
Null values	No rows treated as zero
Number format	9 printed as 9.0
Text match	Wrong case or added words

Window ordering does not guarantee final output ordering. PostgreSQL documents that rows have no guaranteed order without an explicit outer ordering operation. When the prompt requires sorted results, put that rule in the final ORDER BY. Sorting rows.

Keep numeric computation separate from display formatting. For example, dividing two integers can produce a different result from decimal division in some dialects. Rounding a number and rendering it with exactly two decimal places are also separate operations. Avoid turning numbers into strings early, which can change later comparisons and sorting.

Remove exploratory result-producing statements from the final answer unless the task requires them. They helped you inspect intermediate data; they are not automatically part of the requested result.

What candidate reports can and cannot tell you

Candidate-reported, unverified: a commenter in an Accenture data engineering assessment discussion described SQL ranking alongside other technical topics. Another author, discussing a Snowflake-related assessment, reported a failed result despite believing the visible output matched.

These are independent personal accounts, not a verified common assessment format. Neither establishes a universal question count, passing score, or cause of failure. The second report does not include enough diagnostic evidence to conclude that the grader was defective.

Preparation inference: use reports to identify questions worth investigating, then use official documentation and controlled examples to investigate them. If you encounter a problem, preserve the permitted error details and contact the appropriate support channel; do not assume that changing random formatting will repair a logically incorrect query.

Sources and Further Reading

Research checked September 15, 2026. Platform settings vary by question and employer. Candidate event dates were not independently verified. Exercises are original local preparation examples.