SQL from zero · Chapter 37 of 37
The interview
By the end of this chapter you can answer five classic SQL interview questions, each one several ideas from this course put together. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: The interview
- 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
Vikram skipped your interview when you joined, so here it is: five questions, the kind every data-engineering interview asks. Duplicate emails. The third-highest likes. Posting streaks. A median. Growth month over month. You have used every piece of SQL they need except two.
Each question is short and has a trap: emails in other capitals, ties in the likes, days that must be in a row, one viral post that pulls the average, a month with nobody in it. A quick answer that only happens to work on today's rows fails on the edge data, and in the interview. Proving the skill means getting the trap right and saying why.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1 · interview question
Interview: duplicate-emails.sql, emails with more than one account
Run setup.sql first. Find the emails that belong to more than one account when capitals are ignored (Anya@MingleSphere.io and anya@minglesphere.io are the same email). Every account counts, including members who left. email is the email in lower case; accounts is how many accounts use it.
Return two columns: email and accounts. Most accounts first; when two emails have the same number, sort them by email A to Z.
- Task 2 · interview question
Interview: third-highest.sql, the posts with the third-highest likes
Among the posts that are not deleted, find the third-highest likes_count, counting different values only: if the likes are 9, 9, 8, 7, the third-highest is 7. Return every post that is not deleted and has that many likes. Posts by members who left still count.
Return three columns: username (the post's author), content and likes_count. Any order.
- Task 3 · interview question
Interview: posting-streaks.sql, days in a row with a post
A day counts when MingleSphere had at least one post that is not deleted on it. A post's day is its created_at as a date (India time, the lab's clock). Find every streak of 2 or more days in a row; a streak can run across the end of a month or a year.
Return three columns: streak_start and streak_end (dates) and days (how many days it lasted). Oldest streak first.
- Task 4 · interview question
Interview: median-likes.sql, the median likes per type
For each post type, find the median likes_count of the posts that are not deleted. Use the continuous median (percentile_cont), so with an even number of posts it is halfway between the middle two.
Return two columns: type and median_likes. Any order.
- Task 5 · interview question
Interview: monthly-growth.sql, sign-ups and growth per month
Run setup.sql first. Count the accounts created each month, and the growth from the month before. Every account counts (members who left and the bot too). Every month from the first sign-up's month to the last one's is a row, even a month with no sign-ups (0). month is the month as text like 2026-01; new_members is the accounts created that month; growth_pct is 100 times (this month minus last month) divided by last month, rounded to 1 decimal, and NULL for the first month and after a month with 0 sign-ups.
Return three columns: month, new_members and growth_pct. Oldest month first.
What you will learn
- The median: percentile_cont(0.5) WITHIN GROUP (ORDER BY ...), the middle value that outliers do not pull.
- Gaps and islands: finding runs of consecutive days with row_number.
- Putting the patterns together: grouping by an expression, DISTINCT with OFFSET, generate_series with a LEFT JOIN, lag and NULLIF.
More in The interview
Build The interview 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.