📊 Data Analyst Interview Series — Part 4

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

Today, let's cover 10 practical SQL questions that are commonly asked in Data Analyst interviews. 👇

1️⃣ How do you find the highest salary in each department?

Sample Answer:

"I would use a window function such as DENSE_RANK() and partition the data by 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 Employee_ID, Department, Salary
FROM RankedEmployees
WHERE Salary_Rank = 1;

2️⃣ How do you find customers who have never placed an order?

Sample Answer:

"I would use a LEFT JOIN between the Customers and Orders tables and then filter for customers where no matching order exists."

SELECT c.Customer_ID,
       c.Customer_Name
FROM Customers c
LEFT JOIN Orders o
    ON c.Customer_ID = o.Customer_ID
WHERE o.Customer_ID IS NULL;

3️⃣ How do you find the total sales for each customer?

Sample Answer:

"I would group the sales data by Customer_ID and use SUM() to calculate the total sales."

SELECT Customer_ID,
       SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Customer_ID;

4️⃣ How do you find the top 5 customers by sales?

Sample Answer:

"I would aggregate sales by customer, sort the result in descending order, and then return the top five customers."

SELECT Customer_ID,
       SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Customer_ID
ORDER BY Total_Sales DESC
LIMIT 5;

"The exact syntax for limiting rows can vary depending on the database, such as TOP in SQL Server."

5️⃣ How do you calculate the average order value?

Sample Answer:

"Average Order Value can be calculated by dividing total sales by the number of orders. If each row represents one order, AVG() can also be used directly on the order amount."

SELECT AVG(Order_Amount) AS Average_Order_Value
FROM Orders;

6️⃣ How would you identify customers who placed more than 5 orders?

Sample Answer:

"I would group the orders by Customer_ID and use HAVING to filter customers whose order count is greater than five."

SELECT Customer_ID,
       COUNT(**) AS Order_Count
FROM Orders
GROUP BY Customer_ID
HAVING COUNT(**) > 5;

7️⃣ How do you find records from the last 30 days?

Sample Answer:

"I would compare the date column with the current date and subtract 30 days. The exact syntax depends on the database."

For example:

SELECT **
FROM Orders
WHERE Order_Date >= CURRENT_DATE - INTERVAL '30' DAY;

8️⃣ How do you find the total sales by month?

Sample Answer:

"I would extract the month from the order date, group the data by month, and calculate the total sales."

SELECT
    DATE_TRUNC('month', Order_Date) AS Month,
    SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY DATE_TRUNC('month', Order_Date)
ORDER BY Month;

9️⃣ How do you find employees whose salary is above their department's average salary?

Sample Answer:

"I would calculate the average salary for each department and compare each employee's salary with that department-level average. A CTE makes this easier to read."

WITH DepartmentAverage AS (
    SELECT Department,
           AVG(Salary) AS Avg_Salary
    FROM Employees
    GROUP BY Department
)
SELECT e.Employee_ID,
       e.Department,
       e.Salary
FROM Employees e
JOIN DepartmentAverage d
    ON e.Department = d.Department
WHERE e.Salary > d.Avg_Salary;