Postgres in depth · Chapter 65 of 65
The interview round
By the end of this chapter you can answer the SQL questions interviews ask most, and the harder kind: questions about Postgres itself, answered with a query instead of a guess. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Extensions and graduation
- 5 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.
Why you need this
LCO is hiring a second database engineer, and Priya asks you to help run the interviews. You get to see the questions from the other side: what a good answer looks like, and where good candidates still slip. Every question in this round is one you can answer now. The difference between a pass and a fail is rarely the syntax; it is the ties, the NULLs, the empty months, and the habit of checking what the database actually did.
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: second-priciest.sql, the second-highest price per category
For each category, find the published courses (directly in that category) with its second-highest price: if several courses share the highest price, the second-highest is the next lower price, and several courses may share that one too. A category whose published courses all cost the same has no row.
Return three columns: category (its name), title and price. Order by category, then title.
- Task 2 · interview question
Interview: top-spenders.sql, the top two per city
For each city, the two students (not deleted, with a city) who spent the most on paid orders: the sum of the total of their orders with the status paid. A student with no paid order does not count. When students spend the same, the one whose name comes first alphabetically ranks higher; a city keeps exactly two rows, or fewer if it has fewer such students.
Return three columns: city, name and spent. Order by city, then spent from most, then name.
- Task 3 · interview question
Interview: growth.sql, month over month
For every month from January to September 2026, LCO's revenue (the sum of the total of the orders with the status paid, by the month of paid_at in LCO's time zone) and its change from the month before. A month with no paid orders shows revenue 0; January's change is empty.
Return three columns: month (the first day of the month, as a date), revenue and change. Order by month.
- Task 4 · interview question
Interview: which-index.sql, ask the planner
The table lesson_events (the lesson shows how to make it) has three indexes. Answer with a query: which index does the planner choose for SELECT * FROM lesson_events WHERE student_id = 42 AND happened_at >= '2026-04-01'?
Return one column, index_name: the name of the index in that query's plan.
Careful: the Check's copy gives the three indexes other names, so a name typed by hand is wrong there. Ask Postgres.
- Task 5 · interview question
Interview: row-versions.sql, what an UPDATE leaves behind
Show what three updates do to a table's first page. Create the table page_views (page text, the primary key; views integer, required), insert the row home with 0 views, then add 1 to its views three times, with three separate updates.
Then return one row per tuple on page 0 of page_views: lp (the line pointer), t_ctid and replaced (true when the tuple's t_xmax is not 0). Order by lp.
What you will learn
- How to answer: questions to ask before writing, and how to build the query in steps.
- Ranking per group: row_number, rank and dense_rank, and which question each answers.
- Month over month: lag, and the missing-month trap.
- Internals by query: asking the planner from SQL with EXPLAIN (FORMAT JSON), and reading row versions with pageinspect.
More in Extensions and graduation
Build The interview round yourself
LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.