47. Pass Rate Per Quiz
MediumAggregationratio of subgroup to totalconditional aggregationAVGROUND
A ratio of a subgroup to the group total is just (count of matching rows) / (count of all rows) per group. A neat shortcut: AVG(condition) already gives that fraction, because the condition is 1 when true and 0 when false.
Task: for each quiz, return the fraction of attempts that passed (score >= 50).
Return columns quiz, pass_rate where pass_rate = ROUND(passing_attempts * 1.0 / total_attempts, 2) (round to 2 decimals, as a fraction between 0 and 1). Order by pass_rate descending, then quiz ascending.
Tables
Loading schema…
Expected outputrows must come back in this order
Computing expected output…
This is the result for the example data above. On Submit your query is graded against this example plus 4 hidden edge cases — 5test cases in all. A sloppy query that only fits the example won't pass.
SQL· SQLite · runs in your browser
⌘/Ctrl+↵ run · ⇧⌘/Ctrl+↵ submit
Run a query to see its output here.