Articles
Mercer Mettl SQL Assessment: Read the Schema and Verify DBLysis Test Cases
Prepare for a Mercer Mettl SQL assessment with DBLysis schema checks, Run Query versus Check Query, and original duplicate, NULL, and zero-order examples.
A SQL assessment query can run successfully and return a convincing table while violating the requested result grain. Duplicate rows inflate counts, a misplaced filter removes customers with no activity, and a NULL amount quietly changes what “revenue” means.
For a Mercer Mettl SQL assessment, practice reading the schema, state the required output, inspect your query's result, and verify the backend test cases separately. The official DBLysis walkthrough describes those distinct actions; treating them as one “run” step loses useful evidence.
This guide uses official provider documentation for DBLysis behavior. It does not have two independently verified same-cycle employer reports, so it makes no claim about your employer's exact questions, timer, cutoff score, or proctoring settings. The customer-order exercise is original practice, and the preparation workflow is labeled inference.
Start with SQL: Count Users With Zero or One Rides per City to practice preserving entities that have no matching activity.
What does DBLysis officially expose to test takers?
Official fact: Mercer Mettl's database-query environment walkthrough describes a query editor, database variant selection, View Schema, and a How to attempt walkthrough. It says the editor executes DQL queries. Run Query displays the result in Output; Check Query checks the response against backend test cases and displays their status in Test Cases. The provider's DBLysis product page describes evaluation across multiple datasets.
Preparation inference: Use the actual invitation and visible environment to confirm the available engine and rules. A provider supporting several engines does not establish which one an employer has configured for your assessment. Do not assume a local PostgreSQL function is accepted by a MySQL or SQL Server environment.
Before an authorized practice session, check date literals, identifier quoting, NULL handling, integer division, row limiting, and supported window functions. Choose syntax you can explain in the visible dialect. If you change engine, rerun the final query; do not assume an earlier result proves that the revised version works.
How do Run Query, Check Query, and completion differ?
Keep this preparation evidence table beside your practice notes. The first two actions come from the official walkthrough; final assessment completion depends on the invitation and its visible workflow.
| Evidence | What it answers | What it does not establish |
|---|---|---|
| Output after Run Query | Did this query execute, and what rows appeared? | Correctness across all backend datasets. |
| Test Cases after Check Query | What status did the backend checks return for this version? | An employer hiring outcome or universal pass score. |
| Final assessment completion state | Did the assessment's required final workflow finish? | That every SQL answer was correct. |
Execution success does not establish correctness or assessment completion.
That shortcut skips the result contract and backend checks. After each meaningful edit, confirm that the displayed evidence belongs to the current query. Then follow the assessment's own completion instructions. Do not invent a save button, autosave guarantee, or a completion screen that the official walkthrough does not document.
If a check fails, classify the symptom before changing everything: syntax or engine incompatibility, wrong columns, wrong cardinality, wrong values, or wrong ordering. A failed dataset is a clue about an assumption; it does not reveal the exact hidden rows.
Read the schema before choosing a join
The following original exercise uses two tables. It is not a reported Mettl question.
customers(customer_id, customer_name) has one row per customer, and customer_id is its unique key. orders(order_id, customer_id, ordered_at, status, amount) is a raw imported table. For this exercise, it may contain exact duplicate rows; conflicting versions of one order are outside the contract. ordered_at is a DATE, and amount is a decimal that may be NULL.
Return one row for every customer, with the number of distinct paid orders during January 2026 and their total amount. Count a paid order with an unknown amount as an order, but treat that NULL amount as zero only because this exercise explicitly requires it. Return customer_id, paid_orders, and paid_amount, ordered by customer ID ascending.
The output grain is one row per customer. The order grain is one logical order per order_id, after removing exact imported duplicates. Saying these two grains aloud prevents several plausible mistakes before you write SQL.
| customer_id | customer_name |
|---|---|
| 1 | Ada |
| 2 | Ben |
| 3 | Chen |
| 4 | Dina |
| order_id | customer_id | ordered_at | status | amount |
|---|---|---|---|---|
| 101 | 1 | 2026-01-05 | paid | 50 |
| 101 | 1 | 2026-01-05 | paid | 50 |
| 102 | 1 | 2026-01-06 | paid | NULL |
| 103 | 2 | 2026-01-10 | cancelled | 70 |
| 104 | 2 | 2025-12-31 | paid | 40 |
| 105 | 4 | 2026-02-01 | paid | NULL |
Ada has two logical qualifying orders and an exercise-defined amount of 50. Ben has no qualifying January paid order, Chen has no order rows, and Dina's paid order is outside January. All four customers must remain in the output.
Inference: In a real prompt, derive these assumptions from the statement and schema. Do not invent duplicates, assume a primary key that is not shown, or convert an unknown financial value into zero without a stated policy. If the prompt says orders are unique, the deduplication step below may be unnecessary.
Build and explain the correct query
This solution uses PostgreSQL-style date literals. Adapt it to the assessment's actual engine before checking it.
WITH clean_orders AS (
SELECT DISTINCT
order_id, customer_id, ordered_at, status, amount
FROM orders
), paid_january AS (
SELECT order_id, customer_id, amount
FROM clean_orders
WHERE status = 'paid'
AND ordered_at >= DATE '2026-01-01'
AND ordered_at < DATE '2026-02-01'
)
SELECT
c.customer_id,
COUNT(p.order_id) AS paid_orders,
COALESCE(SUM(COALESCE(p.amount, 0)), 0) AS paid_amount
FROM customers AS c
LEFT JOIN paid_january AS p
ON p.customer_id = c.customer_id
GROUP BY c.customer_id
ORDER BY c.customer_id;
| customer_id | paid_orders | paid_amount |
|---|---|---|
| 1 | 2 | 50 |
| 2 | 0 | 0 |
| 3 | 0 | 0 |
| 4 | 0 | 0 |
Explain each boundary. Deduplication happens before aggregation, so repeated imported rows cannot inflate either measure. Filtering produces qualifying orders before the left join. The customer table supplies the population to preserve. Counting the matched order identifier avoids counting a NULL-extended row as an order. The date interval includes January 1 and excludes February 1.
Official SQL fact: PostgreSQL documents that a left join preserves unmatched left-side rows by extending the right side with NULLs; filtering in WHERE after the join can remove those rows. See table expressions. This explains the exercise's join choice, rather than establishing a DBLysis-specific SQL rule.
The two COALESCE operations have separate purposes. The inner operation applies the exercise's unknown-amount policy. The outer operation turns the absence of an aggregate value into the requested displayed zero. Official SQL fact: PostgreSQL's aggregate-function documentation distinguishes counting rows from counting non-NULL expressions and explains empty-input aggregate results.
Which counterexamples reveal a wrong answer?
Change one clause or fixture row at a time so you can explain the cause, not just collect failing outputs.
| Query change or data variation | Result to inspect | What it exposes |
|---|---|---|
| Remove the exact-duplicate cleanup | Ada becomes 3 orders and amount 100. | Imported duplicate rows affect both measures. |
| Use COUNT(*) after the left join | Zero-order customers receive a count of 1. | A preserved output row is not a matched order. |
| Put paid/date filters in WHERE after joining raw orders | Ben, Chen, and Dina disappear. | Filtering the joined result defeats population preservation. |
| Count amount instead of order_id | Ada's NULL-amount order is omitted from the count. | An unknown amount does not mean the order does not exist. |
| Include February 1 in the date interval | Dina becomes a qualifying-order customer. | The requested period boundary changed. |
These expected results are original fixture checks, not descriptions of a vendor's hidden test cases. The point is to produce a small counterexample that refutes your own query's assumptions before the backend does.
If duplicates conflict instead of being identical, SELECT DISTINCT is not enough. Two rows for order 101 with different amounts both survive it. You would need a stated rule for the authoritative version, such as an update timestamp and deterministic tie handling. The current exercise intentionally excludes that problem; adding an arbitrary MAX amount would change the business meaning.
What should you check when the values look right?
Verify output shape as carefully as arithmetic. The prompt may require exact aliases, column order, one row per entity, or a specified ordering. Returning names instead of IDs can merge two different customers with the same name. An extra diagnostic column can violate the required response even when the main values are correct.
Official SQL fact: Without an explicit ordering, PostgreSQL does not promise the rows will appear in a particular sequence. See sorting rows. Inference: Use the order the prompt requests, and include a tie-breaker when the specification calls for deterministic selection.
Do not use a displayed grid's accidental order as evidence. Also inspect whether division occurs with integer operands, whether decimal precision is adequate, and whether a date filter is operating on DATE values or timestamps. The exercise avoids timezone conversion, but a real task may not.
When a query returns an empty result, distinguish a legitimate empty answer from a join-key or filter defect. Count the intended base population during practice, inspect the relevant key relationships, and test a known qualifying row. In the assessment, use only the permitted query tools; the official walkthrough's DQL-only boundary is not permission to modify the supplied tables.
How should you use practice time?
Preparation inference: Practice an entire response cycle rather than only writing SELECT statements. Read the output contract, inspect the schema, derive one expected row manually, write the simplest correct query, inspect Output, check Test Cases, and revisit the exact failed assumption after an edit.
If an invitation supplies a timer, allocate time according to that timer and task count. There is no verified universal DBLysis duration in the evidence used here. Leave enough time to recheck the final query and complete the visible assessment workflow; a locally memorized schedule should not override the actual invitation.
A concise explanation of this exercise could be: “I preserve customers, deduplicate the specified raw orders, restrict orders to the paid January population, and aggregate matched identifiers and amounts. I checked no-order customers, duplicates, a NULL amount, and the month boundary.” That explanation connects the query to requirements without claiming knowledge of hidden datasets.
As an original practice check, change one input property at a time and predict the affected output before running the query. Adding a second identical raw copy of order 101 should leave Ada's logical order count and revenue unchanged under this exercise's deduplication rule. Adding a cancelled January order for Chen should leave Chen at zero qualifying orders. Adding a new paid January order with a non-null amount for Ben should change only Ben's count and total. These checks test distinct assumptions. If several unrelated customer rows change, inspect the join keys and grouping grain before adjusting the aggregate expression. Keep these local expectations separate from DBLysis backend test-case results.