SQL from zero · Chapter 14 of 37
Questions inside questions
By the end of this chapter you can put a whole query inside another one, to compare with a number you first have to work out, or to keep the rows that do (or do not) have a match somewhere else. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Joins
- 5 tasks
- SQL
- In the all labs plan
The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.
Why you need this
Rohan has three questions, and each one hides a second question inside. Which posts did better than average? First you need the average. Who wrote about chai? First you need the chai posts. Who has never commented? First you need everyone who has.
With what you know, you would run the inner question, copy its answer (2.68 likes) and paste it into a second query. That breaks the day the data changes, because the pasted number does not. And joins make you think in pairs, when all you want to ask is "is there any?".
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
above-average.sql: posts liked more than average
List the posts that are not deleted and have more likes (likes_count) than the average post. Work out the average over the posts that are not deleted, too. Posts by members who left count like any other.
Return three columns: username (the author's), content and likes_count. Any order.
- Task 2
chai-posters.sql: members who wrote about chai
List the members who wrote at least one post tagged chai that is not deleted. Each member appears once, even if they wrote several chai posts.
Return one column: username. Any order.
- Task 3
never-commented.sql: members who never commented
List the members who have never written a comment, on any post. Members who left are included like anyone else.
Return one column: username. Any order.
- Task 4
latest-post.sql: each member's latest post
For each member, show their latest post: the one with the highest created_at among their posts that are not deleted. If a member's newest post is deleted, show their newest one that is not. Members with no such posts are left out; members who left count like anyone else.
Return three columns: username, content and created_at. Any order.
- Task 5 · interview question
Interview: no-replies.sql, comments nobody replied to
List the comments that nobody has replied to: no other comment has their id as its parent_id. A reply that nobody answered counts too. Rohan's query in the file uses NOT IN and returns no rows at all; write one that works.
Return two columns: content and created_at. Any order.
What you will learn
- A subquery: a whole SELECT in brackets, used where a value or a list would go.
- Correlated subqueries: an inner query that looks at the outer row, and runs once for each one.
- IN, EXISTS and NOT EXISTS: is this value in that list, is there at least one row, is there none.
- The NOT IN trap: one NULL in the list, and nothing comes back.
More in Joins
Build "Questions inside questions" yourself
MingleSphereQL, a social app, needs answers from its data every day. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.