SQL Query Challenges
Introduction
SQL (Structured Query Language) is a fundamental skill for many technical roles, especially those involving data analysis, backend development, or database management. During technical interviews, candidates are often tested on their ability to write efficient SQL queries to solve various data problems.
This guide will walk you through common SQL query challenges you might encounter in interviews, from basic select statements to complex joins and aggregate functions. Each section will build upon the previous one, helping you develop a comprehensive understanding of SQL query patterns.
Basic SQL Query Structure
Let's start with the fundamental structure of an SQL query:
SELECT column1, column2, ...
FROM table_name
WHERE condition
GROUP BY column1, column2, ...
HAVING condition
ORDER BY column1, column2, ... [ASC|DESC]
LIMIT number;
Each part serves a specific purpose:
SELECT: Specifies which columns to retrieveFROM: Indicates which table(s) to queryWHERE: Filters rows based on conditionsGROUP BY: Groups rows with the same valuesHAVING: Filters groups based on conditionsORDER BY: Sorts the result setLIMIT: Restricts the number of rows returned
Challenge 1: Basic Filtering and Sorting
Problem: Retrieve the names and salaries of employees who earn more than $70,000, sorted by salary in descending order.
Table Structure:
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(100),
salary DECIMAL(10, 2),
hire_date DATE
);
Solution:
SELECT name, salary
FROM employees
WHERE salary > 70000
ORDER BY salary DESC;
Output Example:
name | salary
--------------+----------
Sarah Johnson | 95000.00
Michael Smith | 85000.00
Robert Brown | 78000.00
Jennifer Lee | 72500.00
Challenge 2: Aggregation Functions
Problem: Find the average salary by department, but only include departments with more than 5 employees.
Solution:
SELECT
department,
ROUND(AVG(salary), 2) AS average_salary,
COUNT(*) AS employee_count
FROM
employees
GROUP BY
department
HAVING
COUNT(*) > 5
ORDER BY
average_salary DESC;
Output Example:
department | average_salary | employee_count
--------------+----------------+----------------
Engineering | 78350.25 | 12
Marketing | 67840.50 | 8
Finance | 65420.75 | 7
Challenge 3: Working with JOINs
Joins are critical for combining data from multiple tables, a common requirement in real-world applications.
Problem: Find all orders with customer information, including customers who haven't placed any orders yet.
Table Structures:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100),
email VARCHAR(100),
city VARCHAR(100)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
total_amount DECIMAL(10, 2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
Solution:
SELECT
c.customer_name,
c.email,
o.order_id,
o.order_date,
o.total_amount
FROM
customers c
LEFT JOIN
orders o ON c.customer_id = o.customer_id
ORDER BY
c.customer_name, o.order_date;
Output Example:
customer_name | email | order_id | order_date | total_amount
---------------+----------------------+----------+------------+--------------
Alex Wong | [email protected] | 1003 | 2023-02-15 | 145.80
Alex Wong | [email protected] | 1008 | 2023-03-22 | 67.50
James Miller | [email protected] | NULL | NULL | NULL
Maria Garcia | [email protected] | 1001 | 2023-01-10 | 250.00
Maria Garcia | [email protected] | 1006 | 2023-03-05 | 89.99
Notice that James Miller appears in the result even though he hasn't placed any order yet (NULL values). This demonstrates the LEFT JOIN's ability to include all records from the left table regardless of matches.
Challenge 4: Subqueries
Subqueries allow you to nest one query inside another, enabling more complex data retrieval.
Problem: Find employees who earn more than the average salary in their department.
Solution:
SELECT
e1.name,
e1.department,
e1.salary
FROM
employees e1
WHERE
e1.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department = e1.department
)
ORDER BY
e1.department, e1.salary DESC;
Output Example:
name | department | salary
---------------+-------------+----------
Sarah Johnson | Engineering | 95000.00
Michael Smith | Engineering | 85000.00
Jennifer Lee | Marketing | 72500.00
David Wilson | Finance | 79000.00
Challenge 5: Window Functions
Window functions perform calculations across a set of table rows related to the current row, making them powerful tools for analytics.
Problem: Rank employees by salary within each department.
Solution:
SELECT
name,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM
employees;
Output Example:
name | department | salary | salary_rank
---------------+-------------+-----------+------------
Sarah Johnson | Engineering | 95000.00 | 1
Michael Smith | Engineering | 85000.00 | 2
Robert Brown | Engineering | 78000.00 | 3
Jennifer Lee | Marketing | 72500.00 | 1
Emily Davis | Marketing | 68000.00 | 2
David Wilson | Finance | 79000.00 | 1
Lisa Martinez | Finance | 65500.00 | 2
Challenge 6: Common Table Expressions (CTEs)
CTEs provide a way to write more readable and maintainable queries by breaking down complex logic into named temporary result sets.
Problem: Find customers who have placed orders with a total value greater than the average order value.
Solution:
WITH AverageOrderValue AS (
SELECT AVG(total_amount) AS avg_value
FROM orders
),
CustomerTotalSpend AS (
SELECT
c.customer_id,
c.customer_name,
SUM(o.total_amount) AS total_spent,
COUNT(o.order_id) AS order_count
FROM
customers c
JOIN
orders o ON c.customer_id = o.customer_id
GROUP BY
c.customer_id, c.customer_name
)
SELECT
cts.customer_name,
cts.total_spent,
cts.order_count,
aov.avg_value AS average_order_value
FROM
CustomerTotalSpend cts,
AverageOrderValue aov
WHERE
cts.total_spent > (cts.order_count * aov.avg_value)
ORDER BY
cts.total_spent DESC;
Output Example:
customer_name | total_spent | order_count | average_order_value
---------------+-------------+-------------+---------------------
Maria Garcia | 339.99 | 2 | 120.65
Alex Wong | 213.30 | 2 | 120.65
Challenge 7: Date and Time Functions
Handling dates and times is a common requirement in SQL interviews.
Problem: Group orders by month and calculate monthly sales totals for the current year.
Solution:
SELECT
EXTRACT(MONTH FROM order_date) AS month,
TO_CHAR(order_date, 'Month') AS month_name,
COUNT(*) AS order_count,
SUM(total_amount) AS monthly_total
FROM
orders
WHERE
EXTRACT(YEAR FROM order_date) = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY
EXTRACT(MONTH FROM order_date),
TO_CHAR(order_date, 'Month')
ORDER BY
month;
Output Example:
month | month_name | order_count | monthly_total
------+------------+-------------+--------------
1 | January | 8 | 1240.75
2 | February | 12 | 1875.50
3 | March | 15 | 2340.25
Challenge 8: Handling NULL Values
Dealing with NULL values correctly is essential for accurate query results.
Problem: Find all departments where at least one employee has a missing performance_rating.
Table Structure:
ALTER TABLE employees ADD COLUMN performance_rating DECIMAL(3, 1);
Solution:
SELECT
department,
COUNT(*) AS total_employees,
SUM(CASE WHEN performance_rating IS NULL THEN 1 ELSE 0 END) AS missing_ratings
FROM
employees
GROUP BY
department
HAVING
SUM(CASE WHEN performance_rating IS NULL THEN 1 ELSE 0 END) > 0
ORDER BY
missing_ratings DESC;
Output Example:
department | total_employees | missing_ratings
--------------+-----------------+----------------
Sales | 10 | 3
Operations | 7 | 2
Marketing | 8 | 1
Challenge 9: Set Operations
Set operations combine results from multiple queries.
Problem: Find employees who work in both the 'Marketing' and 'Digital' projects.
Table Structures:
CREATE TABLE projects (
project_id INT PRIMARY KEY,
project_name VARCHAR(100)
);
CREATE TABLE employee_projects (
employee_id INT,
project_id INT,
PRIMARY KEY (employee_id, project_id),
FOREIGN KEY (employee_id) REFERENCES employees(id),
FOREIGN KEY (project_id) REFERENCES projects(project_id)
);
Solution:
SELECT
e.id,
e.name
FROM
employees e
WHERE
e.id IN (
SELECT ep.employee_id
FROM employee_projects ep
JOIN projects p ON ep.project_id = p.project_id
WHERE p.project_name = 'Marketing'
)
AND e.id IN (
SELECT ep.employee_id
FROM employee_projects ep
JOIN projects p ON ep.project_id = p.project_id
WHERE p.project_name = 'Digital'
);
Alternative Solution using INTERSECT:
SELECT
e.id,
e.name
FROM
employees e
JOIN
employee_projects ep ON e.id = ep.employee_id
JOIN
projects p ON ep.project_id = p.project_id
WHERE
p.project_name = 'Marketing'
INTERSECT
SELECT
e.id,
e.name
FROM
employees e
JOIN
employee_projects ep ON e.id = ep.employee_id
JOIN
projects p ON ep.project_id = p.project_id
WHERE
p.project_name = 'Digital';
Output Example:
id | name
----+---------------
101 | Jennifer Lee
105 | Michael Smith
Challenge 10: Recursive Queries
Recursive queries are powerful for handling hierarchical or graph-like data structures.
Problem: Display an employee hierarchy starting with the CEO.
Table Structure:
ALTER TABLE employees ADD COLUMN manager_id INT;
Solution:
WITH RECURSIVE EmployeeHierarchy AS (
-- Base case: CEO (employee with no manager)
SELECT
id,
name,
manager_id,
0 AS level,
name AS hierarchy_path
FROM
employees
WHERE
manager_id IS NULL
UNION ALL
-- Recursive case: employees with managers
SELECT
e.id,
e.name,
e.manager_id,
eh.level + 1,
eh.hierarchy_path || ' > ' || e.name
FROM
employees e
JOIN
EmployeeHierarchy eh ON e.manager_id = eh.id
)
SELECT
id,
name,
level,
hierarchy_path
FROM
EmployeeHierarchy
ORDER BY
hierarchy_path;
Output Example:
id | name | level | hierarchy_path
----+----------------+-------+----------------------------------
1 | John CEO | 0 | John CEO
2 | Lisa VP | 1 | John CEO > Lisa VP
5 | Sarah Johnson | 2 | John CEO > Lisa VP > Sarah Johnson
3 | David VP | 1 | John CEO > David VP
4 | Michael Smith | 2 | John CEO > David VP > Michael Smith
7 | Robert Brown | 3 | John CEO > David VP > Michael Smith > Robert Brown
Real-world Application Example
Let's tie everything together with a comprehensive example that might appear in an advanced interview.
Scenario: You're analyzing an e-commerce database and need to identify product performance trends.
Problem: Find the top 3 products in each category by sales volume for the last quarter, including comparison with the previous quarter's performance.
Solution:
WITH QuarterlySales AS (
SELECT
p.product_id,
p.product_name,
c.category_name,
DATE_TRUNC('quarter', o.order_date) AS quarter,
SUM(oi.quantity) AS total_quantity,
SUM(oi.quantity * oi.unit_price) AS total_sales
FROM
products p
JOIN
categories c ON p.category_id = c.category_id
JOIN
order_items oi ON p.product_id = oi.product_id
JOIN
orders o ON oi.order_id = o.order_id
WHERE
o.order_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '6 months'
AND o.order_date < DATE_TRUNC('quarter', CURRENT_DATE) + INTERVAL '3 months'
GROUP BY
p.product_id, p.product_name, c.category_name, DATE_TRUNC('quarter', o.order_date)
),
RankedProducts AS (
SELECT
product_id,
product_name,
category_name,
quarter,
total_quantity,
total_sales,
RANK() OVER (PARTITION BY category_name, quarter ORDER BY total_sales DESC) AS sales_rank
FROM
QuarterlySales
),
CurrentQuarterTop AS (
SELECT
product_id,
product_name,
category_name,
total_quantity AS current_quantity,
total_sales AS current_sales
FROM
RankedProducts
WHERE
quarter = DATE_TRUNC('quarter', CURRENT_DATE)
AND sales_rank <= 3
),
PreviousQuarterData AS (
SELECT
product_id,
total_quantity AS previous_quantity,
total_sales AS previous_sales
FROM
RankedProducts
WHERE
quarter = DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'
)
SELECT
c.category_name,
c.product_name,
c.current_quantity,
c.current_sales,
p.previous_quantity,
p.previous_sales,
CASE
WHEN p.previous_sales IS NULL THEN 'New Product'
ELSE ROUND((c.current_sales - p.previous_sales) / p.previous_sales * 100, 1) || '%'
END AS sales_growth
FROM
CurrentQuarterTop c
LEFT JOIN
PreviousQuarterData p ON c.product_id = p.product_id
ORDER BY
c.category_name,
c.current_sales DESC;
Output Example:
category_name | product_name | current_quantity | current_sales | previous_quantity | previous_sales | sales_growth
--------------+---------------------+------------------+---------------+-------------------+----------------+-------------
Electronics | Premium Headphones | 450 | 44950.00 | 410 | 40950.00 | 9.8%
Electronics | Smartphone Pro | 320 | 35840.00 | 280 | 31360.00 | 14.3%
Electronics | Wireless Earbuds | 560 | 22400.00 | NULL | NULL | New Product
Home Goods | Luxury Bedding Set | 210 | 18900.00 | 180 | 16200.00 | 16.7%
Home Goods | Smart Thermostat | 280 | 15680.00 | 240 | 13440.00 | 16.7%
Home Goods | Kitchen Mixer | 175 | 12250.00 | 190 | 13300.00 | -7.9%
Summary
SQL query challenges in interviews test your ability to manipulate and extract insights from relational data. The key skills tested include:
- Basic querying: Filtering, sorting, and limiting results
- Aggregations: GROUP BY, HAVING, and aggregate functions
- Joins: Combining data from multiple tables
- Subqueries: Nesting queries for complex operations
- Window functions: Performing calculations across related rows
- CTEs: Breaking down complex queries into manageable parts
- Date/time handling: Working with temporal data
- NULL handling: Properly managing missing values
- Set operations: Combining results from multiple queries
- Recursive queries: Working with hierarchical data
Practice Exercises
To solidify your SQL skills, try these challenges:
- Find the median salary by department using window functions.
- Calculate the running total of sales by date for each product category.
- Identify customers who have purchased all products in a specific category.
- Find products that have never been ordered together in the same transaction.
- Calculate the average time between orders for each customer.
Additional Resources
- PostgreSQL Documentation
- MySQL Documentation
- SQL Zoo - Interactive SQL exercises
- LeetCode Database Problems - Practice with real interview-style questions
- Mode Analytics SQL Tutorial
Remember that SQL proficiency comes with practice. The more you work with different types of queries and database schemas, the better prepared you'll be for technical interviews.
If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)