Are you preparing for a SQL interview? Are you confident writing basic SQL queries but concerned about being stumped by tricky, unexpected questions? If so, you’re not alone. Many job candidates are nervous about SQL interviews, especially when they know that the interviewer might ask them to write complex queries.
Fortunately, in this article, we will help you. This guide will walk you through several tricky SQL queries that interviewers commonly use to test candidates’ skills and knowledge. We’ll cover subqueries, correlated subqueries, window functions, and common table expressions (CTEs). By the end of this article, you’ll have a better understanding of what to expect in a SQL interview and how to prepare for even the toughest questions.
Table of Contents
What is SQL query?
A SQL query is a statement of instructions written in Structured Query Language (SQL) used to interact with a database. The purpose of a query is to retrieve data and information from the database, allowing it to be manipulated and utilized in other ways. The results of an SQL query are typically tables, called “relations,” representing relevant data and the linkages between them through special commands, such as SELECT and WHERE. Users can ask sophisticated questions with complex parameters to quickly and accurately manipulate data within their database.
Tricky SQL Queries Examples
Using NULL values
Select all employees that have no manager assigned to them (i.e., their manager ID is NULL).
SELECT *
FROM employees
WHERE manager_id IS NULL;
Sort a list of employees by their hire date, but put all employees with no hire date at the end of the list.
SELECT *
FROM employees
ORDER BY CASE WHEN hire_date IS NULL THEN 1 ELSE 0 END, hire_date;
Subqueries and Correlated Subqueries
Find the name and salary of the employee with the second-highest salary in the company.
SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees));
Select all employees who have a higher salary than their department’s average salary.
SELECT *
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);
Window Functions
Rank employees within each department by their salary.
SELECT name, department_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;
Calculate a running total of sales for each month, broken down by product category.
SELECT month, product_category, sales, SUM(sales) OVER (PARTITION BY product_category ORDER BY month) AS running_total
FROM sales_data;
Common Table Expressions (CTEs)
Use a CTE to recursively find all employees who report to a specific manager, including employees who report to those employees (and so on).
WITH RECURSIVE reporting_hierarchy AS (
SELECT * FROM employees WHERE id = [manager_id]
UNION ALL
SELECT e.* FROM employees e JOIN reporting_hierarchy r ON e.manager_id = r.id
)
SELECT * FROM reporting_hierarchy;
Use a self-join within a CTE to find all pairs of employees who share the same manager.
WITH employee_pairs AS (
SELECT e1.name AS name1, e2.name AS name2, e1.manager_id
FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.manager_id AND e1.id <> e2.id
)
SELECT * FROM employee_pairs;
| SQL Interview Resources
Top 60 Basic SQL Interview Questions and Answers Top 10 SQL Scenario-Based Interview Questions and Answers Top 20 SQL Join Interview Questions and Answers |
Mix
Write a query to retrieve the top 5 customers who have made the highest total purchase amount within the last 30 days.
SELECT c.customer_name, SUM(o.total_amount) AS total_purchase
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= DATEADD(day, -30, GETDATE())
GROUP BY c.customer_name
ORDER BY total_purchase DESC
LIMIT 5;
Write a query to calculate the total revenue generated by a particular product in the last quarter.
SELECT p.product_name, SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM products p
INNER JOIN order_items oi ON p.product_id = oi.product_id
INNER JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_date >= DATEADD(quarter, -1, GETDATE())
AND o.order_date < GETDATE()
GROUP BY p.product_name;
Write a query to identify all the customers who have not made a purchase within the last 6 months.
SELECT *
FROM customers c
WHERE c.customer_id NOT IN (
SELECT DISTINCT o.customer_id
FROM orders o
WHERE o.order_date >= DATEADD(month, -6, GETDATE())
);
Write a query to identify all the products that have never been purchased by any customer.
SELECT p.product_name
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.order_id IS NULL;
Write a query to calculate the average number of orders per day for the last month.
SELECT COUNT(*) / 30 AS avg_orders_per_day
FROM orders
WHERE order_date >= DATEADD(day, -30, GETDATE());
Write a query to identify all the orders that contain at least 2 products from the same category.
SELECT *
FROM orders o
WHERE EXISTS (
SELECT *
FROM order_items oi1
INNER JOIN order_items oi2 ON oi1.order_id = oi2.order_id
INNER JOIN products p1 ON oi1.product_id = p1.product_id
INNER JOIN products p2 ON oi2.product_id = p2.product_id
WHERE oi1.order_id = o.order_id
AND p1.category_id = p2.category_id
AND oi1.order_item_id <> oi2.order_item_id
);
Write a query to identify the top 10% of customers who have made the highest number of purchases within the last year.
WITH customer_purchase_counts AS (
SELECT o.customer_id, COUNT(*) AS purchase_count
FROM orders o
WHERE o.order_date >= DATEADD(year, -1, GETDATE())
GROUP BY o.customer_id
)
SELECT c.customer_name, cpc.purchase_count
FROM customers c
INNER JOIN customer_purchase_counts cpc ON c.customer_id = cpc.customer_id
ORDER BY cpc.purchase_count DESC
LIMIT CEILING(0.1 * (SELECT COUNT(*) FROM customers));
Write a query to calculate the total revenue generated by a particular customer in the last quarter.
SELECT c.customer_name, SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= DATEADD(quarter, -1, GETDATE())
AND o.order_date < GETDATE()
AND c.customer_id = <customer_id>
GROUP BY c.customer_name;
Write a query to identify all the products that have been purchased by at least 50% of the customers.
SELECT p.product_id, p.product_name
FROM products p
INNER JOIN order_items oi ON p.product_id = oi.product_id
INNER JOIN orders o ON oi.order_id = o.order_id
GROUP BY p.product_id, p.product_name
HAVING COUNT(DISTINCT o.customer_id) >= (
SELECT COUNT(*) / 2 FROM customers
)
Write a query to identify all the orders that contain at least one product with a price higher than the average price of all products.
SELECT DISTINCT o.order_id
FROM orders o
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE p.unit_price > (SELECT AVG(unit_price) FROM products)
Practice Tricky SQL Queries for Interview
- Write a query to find the top 5 customers who have made the most orders in the last month.
- Write a query to identify all the customers who have made purchases in all four seasons of the year.
- Write a query to find the product with the highest total revenue in the last quarter.
- Write a query to identify all the customers who have made orders in the last month but not in the last week.
- Write a query to find the top 10 products with the highest profit margin, where the profit margin is defined as the difference between the unit price and the cost divided by the unit price.
Tips for Answering Tricky SQL Queries
- Start by understanding the question: Before writing any SQL code, ensure you fully understand the question being asked. Ask the interviewer for clarification if needed, and make sure you know what output the query is expected to produce.
- Use the appropriate SQL concepts: Tricky SQL queries often require more advanced SQL concepts such as subqueries, joins, window functions, or common table expressions (CTEs). Make sure you know when and how to use these concepts, and choose the appropriate one for the given scenario.
- Break the problem into smaller steps: If the query seems too complex to solve all at once, try breaking it down into smaller, more manageable steps. Think about the logic behind each step and how you can use SQL to accomplish it.
- Test your queries: Once you’ve written a SQL query, test it on sample data to ensure it produces the correct results. Check your query for syntax errors, and make sure you’re not making any assumptions about the data that might lead to incorrect results.
- Be prepared to explain your thought process: During a job interview, the interviewer may be more interested in your thought process and problem-solving skills than your final solution. Be prepared to explain how you approached the problem and arrive at your final answer.
- Practice, practice, practice: The best way to prepare for tricky SQL queries is to practice writing them on your own. Use sample data sets and try to come up with your challenging SQL queries to solve. The more you practice, the more comfortable and confident you’ll feel during a job interview.
Conclusion
We tried to share tricky sql queries for interviews; if you have any, please share them in the comment box. You can confidently answer even the toughest interview questions with practice and the right approach. Understanding the question being asked, using the appropriate SQL concepts, breaking down complex problems into smaller steps, testing your queries, and explaining your thought process will help ensure that you give the best possible answer. You’ll be ready to face even the most daunting SQL challenges with enough practice and preparation. Good luck with the SQL interview.