SQL Quest › SQL Interview Questions › Subqueries & CTEs
Top Failure Mode by Quality
Per quality grade, show the count of each failure mode. Use a CTE to JOIN products + failure_events, then aggregate. Show type, twf, hdf, pwf, osf, rnf, total. Order by total descending.
Solve it in the browser editor →
Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect". Free accounts get 10 free challenge solves, and this can be one of them.
Schema
products
| udi | product_id | type | tool_wear_min |
|---|---|---|---|
| 5640 | L52819 | L | 180 |
| 2171 | M17030 | M | 10 |
| 228 | L47407 | L | 168 |
failure_events
| udi | machine_failure | twf | hdf | pwf | osf | rnf |
|---|---|---|---|---|---|---|
| 5640 | 0 | 0 | 0 | 0 | 0 | 1 |
| 2171 | 0 | 0 | 0 | 0 | 0 | 0 |
| 228 | 0 | 0 | 0 | 0 | 0 | 0 |
Expected output: Which modes dominate which grades
Hint
Concepts
SELECT CTE JOIN GROUP BY
Practise the topic: SQL practice questions · CTE practice · JOIN practice · GROUP BY exercises
Read the concept: What is a CTE?
Related questions
Where would this cost you points in an interview?
Ten questions, no signup: a Skillmap across nine SQL skills and the one to fix first.
Take the readiness test