The Ultimate Guide to Cracking SQL Interviews (Data Analyst & Engineer)

Q: What are SQL Window Functions?

Window functions perform calculations across a set of table rows that are related to the current row, maintaining individual row details instead of collapsing them like a GROUP BY operation. They use the OVER() clause.

Q: How does DENSE_RANK() differ from RANK()?

RANK() assigns the same rank to identical values but skips subsequent ranks if there is a tie (e.g., 1, 1, 3). DENSE_RANK() also assigns the same rank to ties but never skips ranks (e.g., 1, 1, 2).

Q: What is a Common Table Expression (CTE) in SQL?

A CTE is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It is declared using the WITH clause and improves readability over nested subqueries.

### 🚀 Why SQL Competency Defines the Highest-Paid Tech Roles In major technical evaluation interviews at companies like Google, Netflix, and Meta, SQL is the ultimate filtering mechanism. Why? Because writing syntax isn't just about matching words; it is a direct measurement of your **algorithmic set-theory intelligence**. While generic developers write slow queries that crash under production scales, senior architects design highly optimized, index-friendly structures using window partitions, hierarchical CTEs, and mathematical intervals. This handbook represents **The Ultimate SQL Interview Guide**—a collection of the most critical concepts you need to master, complete with real-world context and interactive practice. --- ### 1. The Magic of Window Functions (The Ultimate Differentiator) If you only have time to master one advanced concept, make it **Window Functions**. If you can use these confidently, you immediately signal to the interviewer that you are not a beginner. #### The Human Explanation: Imagine you have a spreadsheet of sales. If you use a regular `GROUP BY`, you collapse all the rows into a summary (like total sales per month). You lose the individual transaction details. A Window Function is like having a magnifying glass. It lets you look at the total sales for the month *while still keeping* every single individual transaction row visible. #### The Classic Interview Question: *"Find the top 3 highest-earning employees for each department."* #### How to solve it like a pro: Instead of writing complex subqueries, use `DENSE_RANK()`. ```sql SELECT first_name, department_id, salary, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank FROM employees; ``` **How to explain it to the interviewer:** > *"I'm using `PARTITION BY department_id` to create a 'window' for each department. Then I'm ordering the salary from highest to lowest. `DENSE_RANK()` gives me the rank without skipping numbers if there's a tie."* 👉 **Practice this concept in our [Interactive Lessons](/lessons) section!** --- ### 2. CTEs (Common Table Expressions): Writing Code Humans Can Read In a high-pressure interview, it is very easy to write a query that looks like a bowl of spaghetti. Subqueries inside subqueries are hard to read and hard to debug. #### The Human Explanation: A CTE is like creating a temporary table that only exists for that specific query. It lets you break a massive, scary problem into small, bite-sized steps. #### The Classic Interview Question: *"Find employees whose salary is above the average salary of all employees."* #### How to solve it like a pro: Use a CTE to calculate the average first, then query against it. ```sql WITH AveragePay AS ( SELECT AVG(salary) as avg_salary FROM employees ) SELECT e.first_name, e.salary FROM employees e CROSS JOIN AveragePay ap WHERE e.salary > ap.avg_salary; ``` **How to explain it to the interviewer:** > *"I'm using a CTE here to make the code readable. First, I calculate the overall average. Then, I just filter for employees who beat that average. It’s much easier to read than nesting these inside each other."* --- ### 3. Joins: Beyond the Venn Diagram Trap Most people learn Joins using Venn diagrams. But in real life (and in interviews), data is messy. Tables have duplicate rows, and they have `NULL` values. Venn diagrams don't prepare you for that. #### The Human Explanation: A `LEFT JOIN` says: "Give me everything from the left table, and if there is a match in the right table, bring that along too. If not, just put a blank space (NULL)." #### The Interview Gotcha to Watch Out For: Interviewers love to ask what happens when you `LEFT JOIN` tables that have duplicate keys. *Answer: The rows multiply! If Table A has two 'IDs' of 1, and Table B has two 'IDs' of 1, a join will produce 4 rows. This can cause your sums and counts to return wildly incorrect numbers.* 👉 **Dive deeper into complex join scenarios in our [SQL Q&A Section](/qna).** --- ### 4. Aggregations and the `HAVING` Clause Trap This is one of the most common places where candidates trip up. They know how to filter data with `WHERE`, but they forget about `HAVING`. #### The Human Explanation: * `WHERE` filters individual rows **before** they are grouped together. * `HAVING` filters the grouped data **after** the aggregation happens. #### The Classic Interview Question: *"Find all departments that have a total payroll of more than $500,000."* #### How to solve it like a pro: ```sql SELECT department_id, SUM(salary) FROM employees GROUP BY department_id HAVING SUM(salary) > 500000; ``` --- ### 5. Real-World Case Studies: How Tech Giants Use SQL To truly stand out, you need to show you understand how SQL applies to real business problems. For example, at **ApexBank**, we use complex conditional aggregates to detect credit risk and revenue leakage. By running multi-column loan risk assessments, we can pinpoint high-interest delinquencies entirely using SQL. 👉 **Read the full [ApexBank Case Study](/blog/apexbank-credit-risk) to see this in action.** Similarly, e-commerce platforms like **ShopMart** use CTEs and Left Joins to track revenue drops and trace user-order cycles. 👉 **Check out the [ShopMart Revenue Leak Case Study](/blog/shopmart-revenue-leak).** --- ### Conclusion & Next Steps Mastering these concepts will give you a massive advantage in your next interview. But remember, reading is not enough. You need to write queries. 1. Practice window functions in the **[Playground](/playground)**. 2. Take a mock test in the **[Exams](/exams)** section. 3. Read more real-world examples in the **[Blog](/blog)**.