Can I borrow your notes?
- O(2ⁿ) · Legendary
- Full plan
- SQL
- subqueries
- joins
- grouping
Problem
Before exams, it pays to study with someone who takes everything you take: their notes cover every one of your courses. If they take others on top of that, no harm done.
For each student, show their name and the name of each classmate whose notes cover everything they take, in that order. Henry takes both of Lucas's courses and one more: Henry's notes cover Lucas, but Lucas's fall short for Henry.
Taking a course means being enrolled, with a grade or without one. Nobody borrows their own notes, and someone who takes no courses doesn't need anyone's. Sort by the first name and, within each one, by the second.
What it has to return
These rows, in this order
| student | notes_from |
|---|---|
| Emma | Julia |
| Emma | Leo |
| Julia | Emma |
| Julia | Leo |
| Leo | Emma |
| Leo | Julia |
| Lucas | Henry |
This is what comes out with the data in the practice database this challenge uses. The column names don’t count, so name yours however you like: what counts is how many there are and the order they come in. Besides this one, the challenge has hidden tests with other data that are revealed when you submit your solution.
You start with this
SQL
SELECT a.student_id AS who,
b.student_id AS other
FROM grades a
JOIN grades b
ON a.course_id = b.course_idIt opens in your browser, with the editor and the tests. This challenge is part of the full plan; the O(1) and O(log n) ones are free.
More O(2ⁿ) challenges
- Frank against everyoneSQL · recursion · joins
- Not one more, not one lessSQL · window functions · operations
- The chain of commandSQL · recursion · strings
- The emptiest shelfSQL · window functions · dates · sets
- The salary in the middleSQL · window functions · grouping
- The store on a streakSQL · window functions · dates