What SQL Topics Should I Know for a Data Analyst Interview?

The Complete SQL Topic Checklist

1. SELECT Fundamentals

• SELECT, FROM, WHERE, ORDER BY, LIMIT
• DISTINCT — removing duplicate rows
• Aliasing columns and tables with AS
• CASE WHEN statements for conditional logic

2. Aggregations and Grouping

Aggregation questions are the most common entry-level SQL interview topic. You must be able to:

• Use COUNT(), SUM(), AVG(), MIN(), MAX()
• GROUP BY to aggregate across categories
• HAVING to filter aggregated results (not WHERE)
• Combine GROUP BY with JOINs across multiple tables

Interview tip: Know the difference between WHERE and HAVING. WHERE filters rows before grouping. HAVING filters after grouping. This comes up in almost every interview.

3. JOINs

Every data analyst interview includes at least one JOIN question. Know all four:

• INNER JOIN — returns only matching rows in both tables
• LEFT JOIN — returns all rows from the left table, NULLs for non-matches on right
• RIGHT JOIN — opposite of LEFT JOIN
• FULL OUTER JOIN — all rows from both tables
• SELF JOIN — joining a table to itself (used in hierarchy or comparison problems)

4. Window Functions (Most Important)

Window functions are tested in nearly every mid-to-senior analyst interview. This is the topic most candidates are weakest on — making it your biggest opportunity.

• ROW_NUMBER() — assigns a unique sequential number to each row
• RANK() — same rank for ties, skips numbers after a tie
• DENSE_RANK() — same rank for ties, does not skip numbers
• LAG() and LEAD() — access values from previous or next rows
• SUM() OVER, AVG() OVER — running totals and moving averages
• PARTITION BY — restart window calculations per group

5. CTEs (Common Table Expressions)

CTEs are the professional way to write complex multi-step queries. Interviewers expect you to use them instead of nested subqueries for anything with more than two steps.

• Basic WITH clause syntax
• Chaining multiple CTEs in one query
• Recursive CTEs for hierarchical data (senior level only)

6. Subqueries

• Scalar subqueries in SELECT clause
• Correlated subqueries in WHERE clause
• IN, EXISTS, NOT EXISTS

7. NULL Handling

• IS NULL, IS NOT NULL
• COALESCE() — return first non-NULL value
• NULLIF() — return NULL if two values are equal

8. Date and String Functions

These vary by SQL dialect but commonly tested ones include:

• DATE_TRUNC(), EXTRACT(), DATEDIFF()
• CONCAT(), UPPER(), LOWER(), TRIM(), SUBSTRING()
• Converting between data types with CAST()

What Topics Are Rarely Tested?

Save time by skipping these for now — they almost never appear in data analyst interviews:

• Stored procedures and triggers
• Query optimisation and indexes (sometimes asked at senior level)
• Database design and normalisation theory
• Complex recursive CTEs (only tested for senior roles)

Frequently Asked Questions

Which SQL topics are most important for a data analyst interview? Window functions, JOINs, and aggregations are the top three. Window functions in particular are tested in nearly every mid-level interview.

Do I need to know advanced SQL for an entry-level analyst role? No. Entry-level interviews focus on JOINs, GROUP BY, and basic filtering. Window functions become expected at mid-level and above.

Which SQL dialect should I learn — PostgreSQL, MySQL, or SQL Server? PostgreSQL is the safest choice. It is the most commonly tested dialect in analyst interviews and used widely at tech companies.

How do I know if I am ready for SQL interview questions? You are ready when you can solve medium-difficulty problems on TheQueryLab within 20 minutes without hints, and clearly explain your approach out loud.