50. Favorite Genre Per User (Mode)
HardAggregationmodemost-frequent valueCOUNTwindow functionsGROUP BY
The mode is the most frequent value in a group. There is no built-in MODE() in SQLite, so you build it: count occurrences of each value within each group, then keep the value with the highest count per group.
Task: for each user, return the genre they watched most often. The data is designed so each user has a single strict winner (no ties).
Return columns user, genre. Order by user 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.