📊 Data Analyst Interview Series — Part 3

Guys, let's continue our Data Analyst Interview Series.

Today, let's cover 10 important SQL interview questions that test your practical SQL knowledge. 👇

1️⃣ What is a subquery in SQL?

Sample Answer:

“A subquery is a query written inside another SQL query. It can be used to retrieve intermediate results that are then used by the outer query.

For example, to find employees whose salary is greater than the average salary:”

SELECT Employee_ID, Salary
FROM Employees
WHERE Salary > (
    SELECT AVG(Salary)
    FROM Employees
);

2️⃣ What is a CTE?

Sample Answer:

“CTE stands for Common Table Expression. It allows us to define a temporary named result set using the WITH clause, which can then be referenced within the main query.

CTEs make complex queries easier to read, maintain, and debug.”

WITH CustomerSales AS (
    SELECT Customer_ID,
           SUM(Sales) AS Total_Sales
    FROM Sales
    GROUP BY Customer_ID
)
SELECT *
FROM CustomerSales
WHERE Total_Sales > 100000;

3️⃣ What is a window function?

Sample Answer:

“A window function performs a calculation across a set of related rows while still retaining the individual rows in the result.

Unlike GROUP BY, it does not collapse multiple rows into a single row.

Common window functions include ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), and LEAD().”

4️⃣ What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?

Sample Answer:

“ROW_NUMBER() assigns a unique sequential number to every row.

RANK() assigns the same rank to tied values but leaves gaps after a tie.

DENSE_RANK() also assigns the same rank to tied values but does not leave gaps.”

Example:

Values: 100, 100, 90

ROW_NUMBER: 1, 2, 3

RANK: 1, 1, 3

DENSE_RANK: 1, 1, 2

5️⃣ How would you find the second-highest salary?

Sample Answer:

“One approach is to use DENSE_RANK(). This also handles duplicate salaries correctly.”

WITH RankedEmployees AS (
    SELECT Employee_ID,
           Salary,
           DENSE_RANK() OVER (ORDER BY Salary DESC) AS Salary_Rank
    FROM Employees
)
SELECT Employee_ID, Salary
FROM RankedEmployees
WHERE Salary_Rank = 2;

6️⃣ How would you find the top 3 salaries in each department?

Sample Answer:

“I would use a window function to rank employees within each department.”

WITH RankedEmployees AS (
    SELECT Employee_ID,
           Department,
           Salary,
           DENSE_RANK() OVER (
               PARTITION BY Department
               ORDER BY Salary DESC
           ) AS Salary_Rank
    FROM Employees
)
SELECT *
FROM RankedEmployees
WHERE Salary_Rank <= 3;

“The PARTITION BY ensures that ranking starts separately for each department.”

7️⃣ What is PARTITION BY in SQL?

Sample Answer:

“PARTITION BY divides the result set into groups for a window function without collapsing the rows.

For example, if I want to rank employees separately within each department, I can use PARTITION BY Department.”

SELECT Employee_ID,
       Department,
       Salary,
       RANK() OVER (
           PARTITION BY Department
           ORDER BY Salary DESC
       ) AS Salary_Rank
FROM Employees;

8️⃣ What are LAG() and LEAD() functions?

Sample Answer:

“LAG() allows me to access a value from a previous row, while LEAD() allows me to access a value from a following row.

They are particularly useful for comparing current values with previous or future values, such as month-over-month sales.”

SELECT Month,
       Sales,
       LAG(Sales) OVER (ORDER BY Month) AS Previous_Month_Sales
FROM Monthly_Sales;