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".
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
WITH joined AS (SELECT p.type, f.* FROM products p JOIN failure_events f ON p.udi = f.udi). Then GROUP BY type with SUM per flag.
Concepts
SELECT CTE JOIN GROUP BY
Practise the topic: SQL practice questions · CTE practice · JOIN practice · GROUP BY exercises
Related questions
Department Roster with GROUP_CONCATMedium · FreeConsistent Director AnalysisMedium · FreeBelow Department AverageMedium · FreeHighest Total Salary Budget DepartmentMedium · FreeFare Imputation AnalysisMedium · FreeUNION ALL Dedup: Cross-Dataset SearchMedium · Free
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