SQL Query for Test Data | Dataford Interview Questions

Can you write a SQL query to extract specific test data and explain the expected output?

For this exercise, return completed Lead tests for Water samples collected during January 2025. Include the requested result classification and exclude records outside the date range or matching criteria.

Output

One row per qualifying test result with accession_code, collection_date, test_name, result_value, result_unit, and assessment.
Classify values above 0.010 as Above action level, values at or below 0.010 as Within action level, and NULL values as Not available.
Order by collection_date, then accession_code.

Schema

test_samples

Column	Type	Description
sample_idPK	INT	Unique laboratory sample identifier
accession_code	VARCHAR(20)	External sample accession code
client_name	VARCHAR(100)	Client associated with the sample
collected_at	TIMESTAMP	Timestamp when the sample was collected
matrix_type	VARCHAR(30)	Sample matrix, such as Water or Soil

test_results

Column	Type	Description
result_idPK	INT	Unique test result identifier
sample_id	INT	Sample associated with the test result
test_name	VARCHAR(50)	Analyte or laboratory test name
result_value	NUMERIC(10,4)	Measured test value
result_unit	VARCHAR(20)	Unit for the measured value
status	VARCHAR(20)	Processing status of the result
reported_at	TIMESTAMP	Timestamp when the result was reported

Your question is SQL Query for Test Data. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem