SQL interview with PairPad
The pad opens with an employees and departments schema, nine employees with managers and salaries, two answered questions and two for you to write, each with its expected answer in a comment. Run executes everything in an in-browser SQLite and shows each result grid; Share turns it into a room for a mock interview.
- Press Run. SQLite builds the two tables and prints Q1 (150000, because Ada Park and Lin Moreau tie and ties count once) and Q2 (the top earner in each department).
- Write Q3 under its comment: join employees to itself on manager_id and compare salaries. Run again; the new grid should list Maya Ruiz and Ella Brooks.
- Write Q4 to find departments with no employees, and check that it returns Legal. Try it with LEFT JOIN … IS NULL and with NOT EXISTS.
- For a mock interview, press Share and send the link to your partner. Open interviewer.md for the follow-up questions; the candidate types in employees.sql while you watch the cursor.
- Swap roles: one of you writes a new question with its expected answer as a comment, the other answers it.
What to know
Most SQL screening questions come from the same small set of patterns, and this schema is built to exercise them: aggregates with a subquery (second-highest salary), ranking within groups (top earner per department, with a tie on purpose), a self-join across manager_id, an anti-join for the department with no staff, and dates stored as ISO text for questions about tenure or hires per year. Writing the expected answer next to each question lets you check yourself without an answer key.
The tie in Engineering is deliberate. Ada Park and Lin Moreau both earn 150000, so "second-highest salary" is 150000 with MAX(salary) WHERE salary < MAX, and with DENSE_RANK() = 2; ROW_NUMBER would quietly pick one of them, and OFFSET 1 without DISTINCT would return 150000 for the wrong reason. Interviewers ask about ties and NULLs because they separate a query that works on the sample from one that is correct. Sam Austin, the CEO, has a NULL manager_id, so an inner self-join drops that row by design.
The engine is SQLite (sql.js) in your browser. Window functions, CTEs, CASE, COALESCE, date() and strftime() work, which covers almost every interview question. If your interview will be in Postgres or MySQL, the syntax differences you are likely to meet are LIMIT/OFFSET versus TOP or FETCH FIRST, string concatenation with || and date functions; the logic of the answers is the same.
Each Run starts from an empty database and runs the whole file, so you can break the data freely: add an employee with a NULL salary or a second CEO and see which of your queries still return the right answer. For real interviews, Pro ($29 once) adds a scorecard and notes only the interviewer can read, plus a keystroke replay of how the candidate built each query.
Updated · Cosmovex
Questions
What is the expected answer for the second-highest salary?
150000. Two engineers share that salary, and the question asks for the second-highest distinct value. MAX with a subquery, or DENSE_RANK() = 2, both return 150000.
Why does the self-join skip the CEO?
Sam Austin has no manager (manager_id is NULL), so an inner join on manager_id finds no matching row. That is the right result for "earns more than their manager"; use a LEFT JOIN if the question asks to list everyone.
Is this MySQL or Postgres?
Neither: it is SQLite running in the browser. The joins, aggregates, CTEs and window functions used in interview questions behave the same; check function names such as date handling if you are preparing for a specific engine.
Can I add my own questions?
Yes. Add rows or tables at the top and new queries at the bottom; every Run rebuilds the database from the file. Keep the expected answer in a comment so a partner can check it.
Can an interviewer use this with a candidate?
Yes. Press Share, send the link and the candidate joins without an account. With Pro you get notes and a scorecard that only the room owner can read, and replay of every keystroke afterwards.
The free plan covers everything on this page. PairPad Pro ($29, paid once) is described on the PairPad page.
