eSkill SQL Assessment: Practice Query Reasoning and Check the Test Scope

An eSkill SQL assessment invitation does not identify one universal SQL test. Before memorizing syntax, establish which database, subjects, question formats, and timing rules your employer selected. Then practice predicting what a query returns—including the rows it accidentally removes or duplicates.

The most useful preparation combines two habits: read the assessment contract carefully, and check a query's row grain before trusting its totals. This guide uses an original customers–orders–refunds example to practice both. The exercises are independent preparation, not recalled eSkill questions or an employer's answer key.

What the official eSkill material establishes

Official provider facts: eSkill's candidate help page says the requesting organization determines question quantities and time limits, including limits on an assessment or individual questions. It directs candidates to their invitation and the organization for details. A time limit from another applicant's test is therefore not your test contract. eSkill: time limits

The public MS SQL Server – Development listing identifies nine subject areas, including joins, NULL handling, analytic and aggregate functions, grouping sets, recursive CTEs, pivoting, and transactions. That is useful coverage evidence, not proof that every invitation includes every area. Its current question-count field displays zero; do not interpret that catalog field as a promise that an employer's assessment has no questions. eSkill SQL Server listing

The separate MySQL listing currently shows 40 questions and selected-response formats. Its introductory description also mentions MySQL 4, so it should not establish the version of a modern employer's database. These details belong to that listing; they do not transfer automatically to a SQL Server or customized assessment. eSkill MySQL listing

Official customization context: employers can combine library questions, add their own content, and adjust length and difficulty. Preparation inference: the best study boundary comes from the invitation and role, with catalog topics as supporting clues. eSkill customization

Candidate-report boundary: we did not establish two independent, current-cycle reports for the same employer's SQL assessment. This article consequently makes no claims about a universal passing score, exact question sequence, proctoring setup, or what happens after submission.

Check five scope questions before studying

Ask the hiring contact for details that materially change preparation. A concise message is enough: “Could you confirm the SQL dialect/version, whether questions involve writing or interpreting queries, the included subjects, and the timing and permitted-resource rules?”

Use the answer to decide what to practice:

Scope detail	Why it changes preparation
SQL Server, MySQL, or another engine	Date functions, row limits, casting, and supported window syntax differ.
Selected-response versus query entry	Output prediction and syntax production need different rehearsals.
SQL-only versus mixed assessment	A library title does not guarantee that every section is SQL.
Overall versus individual-question timing	Your pacing and review opportunities depend on the actual instructions.
Permitted tools and navigation	Do not assume a local database, documentation, or returning to earlier answers is allowed.

If the employer cannot provide more detail, prioritize joins, aggregation, NULLs, and ordering, then add role-specific material from the job description. For a database-development role, transaction behavior deserves attention; for an analyst role, metric correctness may be more urgent. That prioritization is advice, not a prediction of the employer's selected questions.

Read the instructions before starting a timed section. A practice environment in which you can freely run queries is useful for learning; it does not establish what resources are permitted during the real assessment.

Original exercise: preserve customers with no paid orders

Here is the complete starting dataset. IDs are unique within their own tables. NULL means an amount is unknown, not zero.

Customers(customer_id, name)
1  Ana
2  Ben
3  Chen

Orders(order_id, customer_id, status, amount)
10  1  paid     100
11  1  pending   50
12  2  paid     NULL
13  2  paid     100

Task: return every customer and their number of paid orders, including customers with none. State the output before reading the query.

SELECT c.customer_id,
       COUNT(o.order_id) AS paid_orders
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 output is (1,1), (2,2), (3,0). Ben's unknown amount does not erase order 12; the requirement counts orders, not known monetary amounts. Chen survives because the paid-order predicate belongs to the matching condition.

Now move o.status = 'paid' into a WHERE clause. Chen's null-extended joined row fails that condition and disappears. This is the logical issue to explain, regardless of whether the database chooses a hash join or another physical operator. Microsoft's join documentation distinguishes outer-join matching from filters that can remove unmatched rows. Microsoft: joins

A common attempted repair adds OR o.order_id IS NULL to WHERE. Test it against a fourth customer who has only pending orders. Their left join finds a real pending row, so there is no null-extended row to preserve; the repair still removes them. Filtering eligible orders in ON handles that case directly.

This is a stronger practice answer than “LEFT JOIN keeps everything.” Explain which left-side rows must survive, what counts as an eligible match, and which predicate could remove them afterward.

Original exercise: distinguish three counts and one incomplete sum

Using the same filtered left join, predict these values for each customer:

SELECT c.customer_id,
       COUNT(*) AS joined_rows,
       COUNT(o.order_id) AS paid_orders,
       COUNT(o.amount) AS known_amounts,
       SUM(o.amount) AS known_amount_sum
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;

Results are:

customer  joined_rows  paid_orders  known_amounts  known_amount_sum
1         1            1            1              100
2         2            2            1              100
3         1            0            0              NULL

Official SQL semantics: COUNT(*) includes joined rows; COUNT(expression) counts non-NULL values. That is why Chen has one joined row but zero matched order IDs. Microsoft: COUNT

Reasoning from this dataset: Ben's sum of known amounts is 100, but it is not a complete paid-revenue total. One paid amount is missing. Label the result appropriately or return a missing-amount count alongside it.

COALESCE(SUM(o.amount), 0) can express zero for a customer with no paid orders under an agreed reporting contract. It does not make Ben's missing amount known. Likewise, replacing every unknown amount with zero hides a data-quality issue unless zero is explicitly the business rule.

Practice answering two separate questions: “What will this SQL return?” and “Does that returned value satisfy the metric definition?” An assessment option can be syntactically valid and still answer the wrong business question.

Original exercise: fix refund fan-out without erasing real orders

Add this refund table:

Refunds(refund_id, order_id, refund_amount)
201  10  20
202  10  10

Task: calculate net amount for paid orders whose amount is known. Report orders with unknown paid amounts separately; do not silently include them as zero revenue.

A direct join from orders to refunds produces two copies of order 10. Summing o.amount across those rows gives 200 before subtracting the 30 refunded. The correct net for that order is 70, not 170.

Pre-aggregate refunds to one row per order:

WITH refund_totals AS (
  SELECT order_id, SUM(refund_amount) AS refunded
  FROM Refunds
  GROUP BY order_id
)
SELECT o.customer_id,
       SUM(o.amount - COALESCE(r.refunded, 0)) AS known_net
FROM Orders AS o
LEFT JOIN refund_totals AS r ON r.order_id = o.order_id
WHERE o.status = 'paid'
  AND o.amount IS NOT NULL
GROUP BY o.customer_id
ORDER BY o.customer_id;

This returns (1,70), (2,100). Chen does not appear because this query starts from eligible orders. If the requirement demands every customer, attach the resulting customer-level totals to Customers with a left join and specify how missing totals should display.

Do not patch the original join with SUM(DISTINCT o.amount). Equal amounts can belong to different legitimate orders. If a customer buys twice for 100, the correct gross amount is 200; distinct amounts collapse it to 100. Deduplication must follow entity identity and the requested grain, not coincidental equality of a measure.

For each query, say: “Refunds are reduced to order grain; orders are then aggregated to customer grain.” That sentence reveals whether your SQL preserves the quantities it claims to measure.

Original exercise: choose a latest order deterministically

Add a created_at column. Suppose Ben's orders 12 and 13 have the same timestamp. You need exactly one latest paid order per customer.

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

The secondary ordering chooses order 13 for Ben under this exercise's rule. It makes the choice reproducible when timestamps tie and order IDs are unique; it does not prove that larger IDs always reflect business recency.

Official SQL fact: SQL Server's ROW_NUMBER documentation describes the ordering conditions needed for repeatable assignment. A timestamp alone does not settle a tie. Microsoft: ROW_NUMBER

If the task instead requests every order tied for the latest timestamp, selecting one row is wrong. Consider a rank over the timestamp alone or joining back to the maximum timestamp. First settle the output contract: one winner or all tied winners.

We executed these portable query patterns against the original fixture in SQLite as a reasoning check. This is not a SQL Server/MySQL compatibility test or execution in eSkill. Use the confirmed target database to rehearse dialect-specific syntax.

Turn practice into a test-ready reasoning routine

Before answering, identify the requested columns and grain. Trace the join on a tiny example, apply filters, then check aggregation and ordering. For selected-response questions, use a counterexample to eliminate options; for query entry, make the assumptions explicit when the response format allows it.

If an invitation confirms additional SQL Server development topics, extend practice with transaction boundaries, grouping sets, or recursive termination. Do not spread limited preparation time across advanced features solely because one catalog lists them.

Your goal is to explain why the answer is correct under the stated scope, rather than recognize a query that merely looks familiar.

Sources and further reading

• eSkill candidate help: time limits
• eSkill MS SQL Server – Development catalog
• eSkill MySQL catalog
• eSkill assessment customization
• Microsoft SQL Server: joins
• Microsoft SQL Server: COUNT
• Microsoft SQL Server: ROW_NUMBER