10 SQL Analytics Interview Questions and Answers

SQL Analytics is a cornerstone of data management and analysis, playing a crucial role in extracting actionable insights from large datasets. Its ability to query, manipulate, and analyze data efficiently makes it indispensable in various industries, from finance to healthcare. Mastery of SQL Analytics not only enhances your data handling capabilities but also significantly boosts your problem-solving skills.

This article offers a curated selection of SQL Analytics questions designed to help you prepare for technical interviews. By working through these questions, you will gain a deeper understanding of SQL concepts and techniques, positioning yourself as a strong candidate in the competitive job market.

SQL Analytics Interview Questions and Answers

1. Explain the concept of a JOIN and provide an example query using INNER JOIN.

A JOIN in SQL combines rows from two or more tables based on a related column. The INNER JOIN keyword selects records with matching values in both tables.

Example:

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

This query retrieves employee names and their department names by joining the employees and departments tables on the department_id column.

2. What is a subquery and how can it be used in a SELECT statement? Provide an example.

A subquery is a query embedded within another query, allowing for complex operations by breaking them into simpler, nested queries.

Example:

SELECT employee_id, employee_name
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM departments
    WHERE department_name = 'Sales'
);

Here, the subquery finds the department_id of the ‘Sales’ department, which the outer query uses to filter employees in that department.

3. Write a query to find the second highest salary from an employee table.

To find the second highest salary from an employee table:

SELECT MAX(salary) AS SecondHighestSalary
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

This query finds the maximum salary less than the highest salary, giving the second highest salary.

4. How do window functions differ from aggregate functions? Provide an example of a window function.

Window functions perform operations like ranking and cumulative sums over a specified range of rows, maintaining the original row structure.

Example:

SELECT 
    employee_id,
    department_id,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) AS avg_department_salary
FROM 
    employees;

This calculates the average salary within each department, adding an additional column for the average salary.

5. Write a query to calculate the cumulative sum of sales for each day in a sales table.

To calculate the cumulative sum of sales for each day:

SELECT 
    sales_date,
    sales_amount,
    SUM(sales_amount) OVER (ORDER BY sales_date) AS cumulative_sales
FROM 
    sales_table
ORDER BY 
    sales_date;

This query calculates the cumulative sum of sales up to the current row, ordered by sales_date.

6. What are Common Table Expressions (CTEs) and how do they differ from subqueries? Provide an example.

Common Table Expressions (CTEs) are temporary result sets referenced within a query, defined using the WITH clause. They improve readability and maintainability, especially for complex queries.

Example of a CTE:

WITH Sales_CTE AS (
    SELECT SalesPersonID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY SalesPersonID
)
SELECT SalesPersonID, TotalSales
FROM Sales_CTE
WHERE TotalSales > 10000;

7. How would you optimize a slow-running query? List some strategies.

To optimize a slow-running query, consider these strategies:

• Indexing: Create indexes on columns used in WHERE clauses, JOIN conditions, and ORDER BY clauses.
• Query Refactoring: Rewrite the query for efficiency, possibly using subqueries or CTEs.
• Use of EXPLAIN: Analyze the query execution plan to identify bottlenecks.
• Limit Data Retrieval: Retrieve only necessary columns and rows, avoiding SELECT *.
• Join Optimization: Ensure join conditions are indexed and use efficient join types.
• Partitioning: Partition large tables for better performance.
• Database Configuration: Adjust settings like buffer pool size and cache size.
• Materialized Views: Use them to store results of complex queries.
• Denormalization: Consider adding redundant data for improved performance.

8. Write a query to perform a recursive operation, such as calculating a factorial or traversing a hierarchy.

Recursive operations can be performed using CTEs, useful for hierarchical data traversal or iterative calculations.

Example of a recursive CTE to calculate a factorial:

WITH RECURSIVE FactorialCTE AS (
    SELECT 1 AS n, 1 AS factorial
    UNION ALL
    SELECT n + 1, factorial * (n + 1)
    FROM FactorialCTE
    WHERE n < 5
)
SELECT factorial
FROM FactorialCTE
WHERE n = 5;

9. How do you handle NULL values in SQL? Provide examples.

In SQL, handle NULL values using IS NULL, IS NOT NULL, COALESCE, and IFNULL functions.

Example:

SELECT * FROM employees WHERE department IS NULL;
SELECT * FROM employees WHERE department IS NOT NULL;
SELECT employee_name, COALESCE(department, 'Unknown') AS department FROM employees;
SELECT employee_name, IFNULL(department, 'Unknown') AS department FROM employees;

10. Describe the use of analytical functions like RANK(), DENSE_RANK(), and ROW_NUMBER(). Provide examples.

Analytical functions like RANK(), DENSE_RANK(), and ROW_NUMBER() assign ranks or numbers to rows within a partition based on a specified order.

Examples:

-- Sample data
CREATE TABLE Sales (
    SalesID INT,
    SalesPerson VARCHAR(50),
    SalesAmount DECIMAL(10, 2)
);

INSERT INTO Sales (SalesID, SalesPerson, SalesAmount) VALUES
(1, 'Alice', 500.00),
(2, 'Bob', 700.00),
(3, 'Alice', 700.00),
(4, 'Charlie', 500.00),
(5, 'Bob', 600.00);

-- RANK() example
SELECT SalesPerson, SalesAmount,
       RANK() OVER (ORDER BY SalesAmount DESC) AS Rank
FROM Sales;

-- DENSE_RANK() example
SELECT SalesPerson, SalesAmount,
       DENSE_RANK() OVER (ORDER BY SalesAmount DESC) AS DenseRank
FROM Sales;

-- ROW_NUMBER() example
SELECT SalesPerson, SalesAmount,
       ROW_NUMBER() OVER (ORDER BY SalesAmount DESC) AS RowNumber
FROM Sales;