SQL Interview Questions That Trip Up Every Junior Analyst

Most data analyst interviews are 60% SQL, 30% storytelling, 10% Python. If your SQL is shaky, no number of dashboards in your portfolio will save you. Here are the questions interviewers actually ask.

The window function set

Be fluent with ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), and partitioned aggregates. Sample question: “Find the second highest salary per department.” The answer should be one query with DENSE_RANK over a partition.

The self-join question

Given an employees table with a manager_id column, find every employee who earns more than their manager. Self-joins are simple in theory and consistently fumbled in interviews.

The aggregation traps

• COUNT(*) vs COUNT(column) — the second skips NULLs.
• SUM with CASE WHEN — conditional aggregation, far cleaner than subqueries.
• HAVING vs WHERE — filtering before vs after aggregation.

The date math question

“Find users who signed up in March and were still active 30 days later.” Date arithmetic, joins, and the discipline to filter by signup date and then check activity within a window — this combination separates juniors from mids.

How to prepare in two weeks

Solve every question in the StrataScratch or DataLemur free tier. Then write your own variations. Doing 100 questions teaches you patterns; doing 30 and then explaining each out loud teaches you SQL.