Candidate telemetry diagnostic, error autopsy, and step-by-step query construction walkthrough.
Live aggregated metrics across candidate sandbox attempts
48 solved
First attempt fail
Evaluated submissions
Median time to solve
Unlocked answer
Many tracks have NULL composers. Use COALESCE to provide a default value when the Composer is missing.
Write a query that displays track names with their composer, showing 'Unknown' when no composer is listed.
| Column | Type |
|---|---|
| TrackId | INTEGER (Primary Key) |
| Name | TEXT |
| AlbumId | INTEGER (Foreign Key → Album.AlbumId) |
| MediaTypeId | INTEGER (Foreign Key) |
| GenreId | INTEGER (Foreign Key → Genre.GenreId) |
| Composer | TEXT |
| Milliseconds | INTEGER |
| Bytes | INTEGER |
| UnitPrice | NUMERIC(10,2) |
COALESCE(column, default_value) returns the first non-NULL value.
name | composer --------------- For Those About To Rock (We Salute You) | Angus Young, Malcolm Young, Brian Johnson Balls to the Wall | Unknown Fast As a Shark | F. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman Restless and Wild | F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. Dirkscneider & W. Hoffman Princess of the Dawn | Deaffy & R.A. Smith-Diesel Put The Finger On You | Angus Young, Malcolm Young, Brian Johnson Let's Get It Up | Angus Young, Malcolm Young, Brian Johnson Inject The Venom | Angus Young, Malcolm Young, Brian Johnson ... (20 rows total)
Using `NOT IN (SELECT nullable_col FROM ...)` when the subquery contains NULLs. In SQL's three-valued logic, `x NOT IN (1, 2, NULL)` evaluates to `UNKNOWN` for all rows, causing the entire query to return zero results. Use `NOT EXISTS` or `WHERE nullable_col IS NOT NULL`.
Interviewers evaluate whether you are aware of SQL three-valued logic (TRUE, FALSE, UNKNOWN), safe NULL coercion with COALESCE, and anti-join optimization.
Construct the solution logically from first principles to avoid typical edge case pitfalls.
Identify columns with NULL values and choose safe operators (IS NULL, COALESCE, or NOT EXISTS).
WHERE column_name IS NULL OR COALESCE(amount, 0) > 0
Use LEFT JOIN ... WHERE right.id IS NULL or NOT EXISTS to find non-matching records without falling into the NOT IN trap.
LEFT JOIN related_table rt ON pt.id = rt.ref_id WHERE rt.ref_id IS NULL
Provide user-friendly fallbacks for empty fields using COALESCE(col, 'Unknown').
SELECT id, COALESCE(title, 'Untitled') AS title FROM table_name;
SELECT Name, COALESCE(Composer, 'Unknown') AS Composer FROM Track ORDER BY TrackId LIMIT 20;
Real code patterns candidates submit that fail the grading suite.
SELECT * FROM Customer WHERE Company = NULL;
Three recurring syntax and semantic traps relevant to this problem domain.
If any subquery row returns NULL, NOT IN evaluates to UNKNOWN and discards all records.
SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM inactive_list); -- ❌ Fails if NULL exists
SELECT * FROM customers c WHERE NOT EXISTS (SELECT 1 FROM inactive_list i WHERE i.customer_id = c.id); -- ✅ Safe
Writing `col = NULL` or `col != NULL` always returns UNKNOWN. Always use `IS NULL` or `IS NOT NULL`.
SELECT * FROM tracks WHERE composer = NULL; -- ❌ Returns 0 rows always
SELECT * FROM tracks WHERE composer IS NULL; -- ✅ Proper NULL check
NULL values in numerical addition yield NULL. Use COALESCE(col, 0) before performing arithmetic or string concatenation.
SELECT salary + bonus AS total_comp FROM staff; -- ❌ total_comp is NULL if bonus is NULL
SELECT salary + COALESCE(bonus, 0) AS total_comp FROM staff; -- ✅ Robust arithmetic
Launch our in-browser coding environment. Run queries, view execution plans, and get instant comparative diff grading with no setup.