fbpx

Top 100 Scenario-Based SQL Interview Questions and Answers

Top 100 Scenario-Based SQL Interview Questions and Answers

Contents show

1. Retrieve the names of employees who have joined in the last 6 months.

Answer:

SELECT employee_name
FROM employees
WHERE hire_date >= DATEADD(month, -6, GETDATE());

Explanation: This SQL query selects the names of employees whose hire date is within the last 6 months from the current date.


2. Calculate the total sales for each product category.

Answer:

SELECT category, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY category;

Explanation: This query groups sales data by product category and calculates the total sales for each category.


3. Find the highest-paid employee in each department.

Answer:

SELECT department_id, MAX(salary) AS max_salary
FROM employees
GROUP BY department_id;

Explanation: This SQL query identifies the highest salary in each department.


4. List customers who have made more than 5 purchases in the last month.

Answer:

SELECT customer_name
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
WHERE order_date >= DATEADD(month, -1, GETDATE())
GROUP BY customer_name
HAVING COUNT(orders.order_id) > 5;

Explanation: This query joins the customers and orders tables, filters orders from the last month, groups by customer, and then selects those with more than 5 orders.


5. Retrieve the top 5 products with the highest inventory levels.

Answer:

SELECT TOP 5 product_name, inventory_level
FROM products
ORDER BY inventory_level DESC;

Explanation: This SQL query selects the top 5 products with the highest inventory levels, ordered in descending order.


6. Identify customers who have not placed any orders.

Answer:

SELECT customer_name
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.customer_id IS NULL;

Explanation: This query performs a left join between customers and orders and selects customers without orders.


7. Calculate the average order value for each country.

Answer:

SELECT country, AVG(order_value) AS avg_order_value
FROM orders
GROUP BY country;

Explanation: This SQL query groups orders by country and calculates the average order value for each.


8. List employees who have the same job title and salary.

Answer:

SELECT e1.employee_id, e1.employee_name
FROM employees e1, employees e2
WHERE e1.job_title = e2.job_title
  AND e1.salary = e2.salary
  AND e1.employee_id <> e2.employee_id;

Explanation: This query compares each employee with every other employee to find matches in job title and salary.


9. Find the most recent order for each customer.

Answer:

SELECT customer_id, MAX(order_date) AS most_recent_order_date
FROM orders
GROUP BY customer_id;

Explanation: This query groups orders by customer and selects the maximum (most recent) order date for each.


10. Calculate the total revenue for each month in the last year.

Answer:

SELECT FORMAT(order_date, 'yyyy-MM') AS month_year, SUM(order_amount) AS total_revenue
FROM orders
WHERE order_date >= DATEADD(year, -1, GETDATE())
GROUP BY FORMAT(order_date, 'yyyy-MM');

Explanation: This SQL query filters orders from the last year, groups them by month and year, and calculates the total revenue for each.


11. List products that have never been ordered.

Answer:

SELECT product_id, product_name
FROM products
WHERE product_id NOT IN (SELECT DISTINCT product_id FROM order_details);

Explanation: This query uses a subquery to find products that have not appeared in the order_details table.


12. Retrieve the top 3 customers with the highest total purchase amount.

Answer:

SELECT TOP 3 customer_id, customer_name, SUM(order_amount) AS total_purchase_amount
FROM orders
GROUP BY customer_id, customer_name
ORDER BY total_purchase_amount DESC;

Explanation: This SQL query groups orders by customer, calculates their total purchase amount, and then selects the top 3 customers.


13. Find customers who have placed orders in at least 3 different months.

Answer:

SELECT customer_id, customer_name
FROM orders
GROUP BY customer_id, customer_name
HAVING COUNT(DISTINCT FORMAT(order_date, 'yyyy-MM')) >= 3;

Explanation: This query groups orders by customer and checks if they have orders in at least 3 different months.


14. Identify duplicate entries in the products table based on product name.

Answer:

SELECT product_name, COUNT(*) AS duplicate_count
FROM products
GROUP BY product_name
HAVING COUNT(*) > 1;

Explanation: This SQL query groups products by name and identifies those with more than one entry.


15. Retrieve the list of products along with the total number of orders for each product.

Answer:

SELECT p.product_id, p.product_name, COUNT(od.order_id) AS total_orders
FROM products p
LEFT JOIN order_details od ON p.product_id = od.product_id
GROUP BY p.product_id, p.product_name;

Explanation: This SQL query performs a left join between the products and order_details tables to get a count of orders for each product.


16. Calculate the average order amount for each customer.

Answer:

SELECT o.customer_id, c.customer_name, AVG(o.order_amount) AS avg_order_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY o.customer_id, c.customer_name;

Explanation: This query joins the orders and customers tables, calculates the average order amount for each customer, and groups the results.


17. Find the customers who placed their first order in the last month.

Answer:

SELECT c.customer_id, c.customer_name, MIN(o.order_date) AS first_order_date
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING MIN(o.order_date) >= DATEADD(month, -1, GETDATE());

Explanation: This query joins the customers and orders tables, finds the minimum order date for each customer, and filters for those who placed their first order in the last month.


18. List products that have been ordered by every customer.

Answer:

SELECT p.product_id, p.product_name
FROM products p
JOIN order_details od ON p.product_id = od.product_id
GROUP BY p.product_id, p.product_name
HAVING COUNT(DISTINCT od.customer_id) = (SELECT COUNT(*) FROM customers);

Explanation: This SQL query uses a subquery to compare the number of distinct customers who ordered each product with the total number of customers.


19. Retrieve the top 5 most expensive products.

Answer:

SELECT TOP 5 product_id, product_name, unit_price
FROM products
ORDER BY unit_price DESC;

Explanation: This query selects the top 5 products based on their unit price, ordered in descending order.


20. Retrieve the names of customers who have placed more than 3 orders in the last year.

Answer:

SELECT c.customer_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= DATEADD(year, -1, GETDATE())
GROUP BY c.customer_name
HAVING COUNT(o.order_id) > 3;

Explanation: This query joins the customers and orders tables, filters orders from the last year, groups the results by customer name, and selects customers with more than 3 orders.


21. Find the total revenue generated by each category.

Answer:

SELECT c.category_name, SUM(od.quantity * p.unit_price) AS total_revenue
FROM categories c
JOIN products p ON c.category_id = p.category_id
JOIN order_details od ON p.product_id = od.product_id
GROUP BY c.category_name;

Explanation: This SQL query involves joining the categories, products, and order_details tables to calculate the total revenue for each category.


22. Identify customers who have not placed any orders.

Answer:

SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Explanation: This query uses a left join between customers and orders and selects customers who have not placed any orders (where order_id is null).


23. List products that have never been ordered.

Answer:

SELECT p.product_id, p.product_name
FROM products p
LEFT JOIN order_details od ON p.product_id = od.product_id
WHERE od.order_id IS NULL;

Explanation: This SQL query identifies products that have never been ordered by performing a left join and checking for null order_id.


24. Retrieve the highest order amount for each customer.

Answer:

SELECT o.customer_id, c.customer_name, MAX(o.order_amount) AS highest_order_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY o.customer_id, c.customer_name;

Explanation: This query joins the orders and customers tables and calculates the highest order amount for each customer.


25. Find the top 5 customers with the highest total purchase amount.

Answer:

SELECT c.customer_id, c.customer_name, SUM(o.order_amount) AS total_purchase_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY total_purchase_amount DESC
LIMIT 5;

Explanation: This query combines the customers and orders tables, calculates the total purchase amount for each customer, and then sorts them in descending order to get the top 5.


26. Retrieve the names of customers who have placed orders on consecutive days.

Answer:

SELECT c.customer_name
FROM customers c
JOIN orders o1 ON c.customer_id = o1.customer_id
JOIN orders o2 ON c.customer_id = o2.customer_id
WHERE DATEDIFF(day, o1.order_date, o2.order_date) = 1;

Explanation: This query self-joins the orders table to itself and checks if there is a one-day difference between order dates for the same customer.


27. List products that have been ordered by every customer.

Answer:

SELECT p.product_id, p.product_name
FROM products p
JOIN order_details od ON p.product_id = od.product_id
JOIN customers c ON od.customer_id = c.customer_id
GROUP BY p.product_id, p.product_name
HAVING COUNT(DISTINCT c.customer_id) = (SELECT COUNT(*) FROM customers);

Explanation: This SQL query joins the products, order_details, and customers tables, groups by product, and checks if each product has been ordered by every customer.


28. Find the average order amount for each year.

Answer:

SELECT YEAR(order_date) AS order_year, AVG(order_amount) AS avg_order_amount
FROM orders
GROUP BY YEAR(order_date);

Explanation: This query extracts the year from the order date, groups orders by year, and calculates the average order amount for each year.


29. Identify customers who have placed orders in all product categories.

Answer:

SELECT c.customer_id, c.customer_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id
GROUP BY c.customer_id, c.customer_name
HAVING COUNT(DISTINCT p.category_id) = (SELECT COUNT(*) FROM categories);

Explanation: This SQL query involves multiple joins to identify customers who have placed orders in all product categories.


30. Retrieve the names of customers who have not placed any orders.

Answer:

SELECT customer_name
FROM customers
WHERE customer_id NOT IN (SELECT DISTINCT customer_id FROM orders);

Explanation: This query uses a subquery to find customers who have placed orders, and then selects customers who are not in that list.


31. Calculate the total revenue generated by each product.

Answer:

SELECT p.product_id, p.product_name, SUM(od.order_quantity * od.unit_price) AS total_revenue
FROM products p
JOIN order_details od ON p.product_id = od.product_id
GROUP BY p.product_id, p.product_name;

Explanation: This query joins the products and order_details tables, calculates the revenue for each product, and groups the results by product.


32. Find the customer who has placed the highest total number of orders.

Answer:

SELECT TOP 1 c.customer_id, c.customer_name, COUNT(o.order_id) AS total_orders
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY total_orders DESC;

Explanation: This query combines the customers and orders tables, counts the number of orders for each customer, and selects the customer with the highest count.


33. List products that have never been ordered.

Answer:

SELECT p.product_id, p.product_name
FROM products p
LEFT JOIN order_details od ON p.product_id = od.product_id
WHERE od.product_id IS NULL;

Explanation: This query performs a left join between the products and order_details tables, and then selects products with no corresponding order details.


34. Retrieve the names of customers who have placed orders for all products in a specific category.

Answer:

SELECT c.customer_id, c.customer_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id
WHERE p.category_id = [category_id]
GROUP BY c.customer_id, c.customer_name
HAVING COUNT(DISTINCT p.product_id) = (SELECT COUNT(*) FROM products WHERE category_id = [category_id]);

Explanation: This query retrieves customers who have placed orders for all products within a specific category.


35. Retrieve the average order value for each customer.

Answer:

SELECT c.customer_id, c.customer_name, AVG(od.unit_price * od.order_quantity) AS avg_order_value
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_details od ON o.order_id = od.order_id
GROUP BY c.customer_id, c.customer_name;

Explanation: This query calculates the average order value for each customer by joining the customers, orders, and order_details tables.


36. Find the products that were ordered by a specific customer, along with the quantity ordered.

Answer:

SELECT p.product_id, p.product_name, od.order_quantity
FROM products p
JOIN order_details od ON p.product_id = od.product_id
JOIN orders o ON od.order_id = o.order_id
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id = [customer_id];

Explanation: This query retrieves products and their corresponding order quantities for a specific customer.


37. Identify customers who have placed orders exceeding a certain total value.

Answer:

SELECT c.customer_id, c.customer_name, SUM(od.unit_price * od.order_quantity) AS total_order_value
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_details od ON o.order_id = od.order_id
GROUP BY c.customer_id, c.customer_name
HAVING SUM(od.unit_price * od.order_quantity) > [threshold_value];

Explanation: This query identifies customers whose total order value exceeds a specified threshold.


38. Retrieve the latest order placed by each customer.

Answer:

SELECT c.customer_id, c.customer_name, MAX(o.order_date) AS latest_order_date
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

Explanation: This query finds the latest order date for each customer.


39. List the customers who have placed orders in consecutive months.

Answer:

SELECT c.customer_id, c.customer_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE DATEDIFF(MONTH, o.order_date, LEAD(o.order_date) OVER (PARTITION BY c.customer_id ORDER BY o.order_date)) = 1;

Explanation: This query identifies customers who have placed orders in consecutive months using the LEAD function.


40. Find the top-selling product in each category.

Answer:

SELECT c.category_id, c.category_name, p.product_id, p.product_name, SUM(od.order_quantity) AS total_quantity_sold
FROM categories c
JOIN products p ON c.category_id = p.category_id
JOIN order_details od ON p.product_id = od.product_id
GROUP BY c.category_id, c.category_name, p.product_id, p.product_name
HAVING SUM(od.order_quantity) = (
    SELECT TOP 1 SUM(order_quantity) AS total_quantity
    FROM order_details
    WHERE product_id = p.product_id
    GROUP BY product_id
    ORDER BY total_quantity DESC
);

Explanation: This query identifies the top-selling product in each category based on the total quantity sold.


41. Calculate the total revenue generated by each customer in the past year.

Answer:

SELECT c.customer_id, c.customer_name, 
       SUM(od.unit_price * od.order_quantity) AS total_revenue
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_details od ON o.order_id = od.order_id
WHERE o.order_date >= DATEADD(YEAR, -1, GETDATE())
GROUP BY c.customer_id, c.customer_name;

Explanation: This query calculates the total revenue generated by each customer in the past year.


42. Identify products that have never been ordered.

Answer:

SELECT p.product_id, p.product_name
FROM products p
LEFT JOIN order_details od ON p.product_id = od.product_id
WHERE od.product_id IS NULL;

Explanation: This query identifies products that have never been ordered by using a LEFT JOIN and checking for NULL entries in the order_details table.


43. Determine the average time taken to ship orders for each customer.

Answer:

SELECT c.customer_id, c.customer_name, 
       AVG(DATEDIFF(DAY, o.order_date, o.ship_date)) AS avg_shipping_time
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

Explanation: This query calculates the average time taken to ship orders for each customer.


44. Find the customer with the highest total spend.

Answer:

SELECT TOP 1 c.customer_id, c.customer_name, 
             SUM(od.unit_price * od.order_quantity) AS total_spend
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_details od ON o.order_id = od.order_id
GROUP BY c.customer_id, c.customer_name
ORDER BY total_spend DESC;

Explanation: This query identifies the customer with the highest total spend.


45. Retrieve the highest and lowest selling products for each year.

Answer:

SELECT YEAR(o.order_date) AS year,
       MAX(p.product_name) AS highest_selling_product,
       MIN(p.product_name) AS lowest_selling_product
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id
GROUP BY YEAR(o.order_date);

Explanation: This query retrieves the highest and lowest selling products for each year.


46. Calculate the total revenue for each product category.

Answer:

SELECT c.category_name, 
       SUM(od.unit_price * od.order_quantity) AS total_revenue
FROM categories c
JOIN products p ON c.category_id = p.category_id
JOIN order_details od ON p.product_id = od.product_id
GROUP BY c.category_name;

Explanation: This query calculates the total revenue for each product category.


47. Identify customers who have placed orders in all categories.

Answer:

SELECT c.customer_id, c.customer_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING COUNT(DISTINCT o.category_id) = (SELECT COUNT(DISTINCT category_id) FROM categories);

Explanation: This query identifies customers who have placed orders in all categories.


48. Find the top 5 products with the highest profit margin.

Answer:

SELECT TOP 5 p.product_id, p.product_name, 
             (od.unit_price - p.cost_price) AS profit_margin
FROM products p
JOIN order_details od ON p.product_id = od.product_id
ORDER BY profit_margin DESC;

Explanation: This query finds the top 5 products with the highest profit margin.


49. Retrieve the order with the highest total quantity.

Answer:

SELECT TOP 1 o.order_id, o.order_date, 
             SUM(od.order_quantity) AS total_quantity
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
GROUP BY o.order_id, o.order_date
ORDER BY total_quantity DESC;

Explanation: This query retrieves the order with the highest total quantity.


50. Calculate the total number of orders for each customer, including those who haven’t placed any orders.

Answer:

SELECT c.customer_id, c.customer_name, 
       COUNT(o.order_id) AS total_orders
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

Explanation: This query calculates the total number of orders for each customer, including those who haven’t placed any orders.


51. Retrieve the average order value for each month.

Answer:

SELECT FORMAT(o.order_date, 'MMM yyyy') AS month_year,
       AVG(od.unit_price * od.order_quantity) AS avg_order_value
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
GROUP BY FORMAT(o.order_date, 'MMM yyyy');

Explanation: This query retrieves the average order value for each month.


52. Find the top 3 customers with the highest lifetime spending.

Answer:

SELECT TOP 3 c.customer_id, c.customer_name,
             SUM(od.unit_price * od.order_quantity) AS lifetime_spending
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_details od ON o.order_id = od.order_id
GROUP BY c.customer_id, c.customer_name
ORDER BY lifetime_spending DESC;

Explanation: This query finds the top 3 customers with the highest lifetime spending.


53. Identify products that have never been ordered.

Answer:

SELECT p.product_id, p.product_name
FROM products p
LEFT JOIN order_details od ON p.product_id = od.product_id
WHERE od.order_id IS NULL;

Explanation: This query identifies products that have never been ordered.


54. Calculate the total revenue for each month.

Answer:

SELECT FORMAT(o.order_date, 'MMM yyyy') AS month_year,
       SUM(od.unit_price * od.order_quantity) AS total_revenue
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
GROUP BY FORMAT(o.order_date, 'MMM yyyy');

Explanation: This query calculates the total revenue for each month.


55. Find customers who have placed orders for products from all categories.

Answer:

SELECT c.customer_id, c.customer_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING COUNT(DISTINCT o.category_id) = (SELECT COUNT(DISTINCT category_id) FROM categories);

Explanation: This query finds customers who have placed orders for products from all categories.


56. Retrieve the most recent order for each customer.

Answer:

SELECT c.customer_id, c.customer_name, 
       MAX(o.order_date) AS most_recent_order_date
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

Explanation: This query retrieves the most recent order for each customer.


57. List all products that have never been discounted.

Answer:

SELECT product_id, product_name
FROM products
WHERE discount_percentage = 0 OR discount_percentage IS NULL;

Explanation: This query lists all products that have never been discounted.


58. Find the total number of orders placed by each customer.

Answer:

SELECT c.customer_id, c.customer_name, COUNT(o.order_id) AS total_orders
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

Explanation: This query finds the total number of orders placed by each customer.


59. Identify orders with more than one product.

Answer:

SELECT o.order_id, COUNT(od.product_id) AS num_products
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
GROUP BY o.order_id
HAVING COUNT(od.product_id) > 1;

Explanation: This query identifies orders with more than one product.


60. Calculate the average time between orders for each customer.

Answer:

SELECT c.customer_id, c.customer_name,
       AVG(DATEDIFF(day, o1.order_date, o2.order_date)) AS avg_time_between_orders
FROM customers c
JOIN orders o1 ON c.customer_id = o1.customer_id
JOIN orders o2 ON c.customer_id = o2.customer_id
WHERE o1.order_id < o2.order_id
GROUP BY c.customer_id, c.customer_name;

Explanation: This query calculates the average time between orders for each customer.


61. Find the products that are usually ordered together.

Answer:

SELECT od1.product_id AS product1, od2.product_id AS product2, COUNT(*) AS order_count
FROM order_details od1
JOIN order_details od2 ON od1.order_id = od2.order_id
WHERE od1.product_id < od2.product_id
GROUP BY od1.product_id, od2.product_id
HAVING COUNT(*) > 1
ORDER BY order_count DESC;

Explanation: This query finds the products that are usually ordered together.


62. Retrieve the highest revenue-generating category.

Answer:

SELECT c.category_id, c.category_name, SUM(od.unit_price * od.order_quantity) AS total_revenue
FROM categories c
JOIN products p ON c.category_id = p.category_id
JOIN order_details od ON p.product_id = od.product_id
GROUP BY c.category_id, c.category_name
ORDER BY total_revenue DESC
LIMIT 1;

Explanation: This query retrieves the highest revenue-generating category.


63. Retrieve the customer who has spent the most on orders.

Answer:

SELECT c.customer_id, c.customer_name, SUM(od.unit_price * od.order_quantity) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_details od ON o.order_id = od.order_id
GROUP BY c.customer_id, c.customer_name
ORDER BY total_spent DESC
LIMIT 1;

Explanation: This query retrieves the customer who has spent the most on orders.


64. Find the average order value for each month.

Answer:

SELECT DATEPART(month, o.order_date) AS order_month,
       AVG(od.unit_price * od.order_quantity) AS avg_order_value
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
GROUP BY DATEPART(month, o.order_date)
ORDER BY order_month;

Explanation: This query finds the average order value for each month.


65. Identify customers who have not placed any orders.

Answer:

SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Explanation: This query identifies customers who have not placed any orders.


66. List the top 5 products with the highest unit price.

Answer:

SELECT product_id, product_name, unit_price
FROM products
ORDER BY unit_price DESC
LIMIT 5;

Explanation: This query lists the top 5 products with the highest unit price.


67. Retrieve the total revenue for each year.

Answer:

SELECT YEAR(o.order_date) AS order_year,
       SUM(od.unit_price * od.order_quantity) AS total_revenue
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
GROUP BY YEAR(o.order_date)
ORDER BY order_year;

Explanation: This query retrieves the total revenue for each year.


68. Find the average time taken to ship orders.

Answer:

SELECT AVG(DATEDIFF(day, o.order_date, o.shipped_date)) AS avg_shipment_time
FROM orders o
WHERE o.shipped_date IS NOT NULL;

Explanation: This query finds the average time taken to ship orders.


69. Retrieve the products with the highest sales in each category.

Answer:

SELECT p.product_id, p.product_name, p.category_id, MAX(od.unit_price * od.order_quantity) AS max_sales
FROM products p
JOIN order_details od ON p.product_id = od.product_id
GROUP BY p.product_id, p.product_name, p.category_id;

Explanation: This query retrieves the products with the highest sales in each category.


70. List the customers who have placed orders in every category.

Answer:

SELECT c.customer_id, c.customer_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING COUNT(DISTINCT o.category_id) = (SELECT COUNT(DISTINCT category_id) FROM categories);

Explanation: This query lists the customers who have placed orders in every category.


71. Find the total revenue generated by each customer.

Answer:

SELECT c.customer_id, c.customer_name, SUM(od.unit_price * od.order_quantity) AS total_revenue
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_details od ON o.order_id = od.order_id
GROUP BY c.customer_id, c.customer_name
ORDER BY total_revenue DESC;

Explanation: This query finds the total revenue generated by each customer.


72. Retrieve the products that have never been ordered.

Answer:

SELECT p.product_id, p.product_name
FROM products p
LEFT JOIN order_details od ON p.product_id = od.product_id
WHERE od.product_id IS NULL;

Explanation: This query retrieves the products that have never been ordered.


73. Calculate the average number of orders placed per month.

Answer:

SELECT AVG(monthly_order_count) AS avg_orders_per_month
FROM (
    SELECT DATEPART(month, order_date) AS order_month, COUNT(*) AS monthly_order_count
    FROM orders
    GROUP BY DATEPART(month, order_date)
) AS monthly_orders;

Explanation: This query calculates the average number of orders placed per month.


74. Identify the customers who have placed orders with a total value exceeding $1000.

Answer:

SELECT c.customer_id, c.customer_name
FROM customers c
JOIN (
    SELECT o.customer_id, SUM(od.unit_price * od.order_quantity) AS total_value
    FROM orders o
    JOIN order_details od ON o.order_id = od.order_id
    GROUP BY o.customer_id
    HAVING SUM(od.unit_price * od.order_quantity) > 1000
) AS high_value_orders ON c.customer_id = high_value_orders.customer_id;

Explanation: This query identifies the customers who have placed orders with a total value exceeding $1000.


75. List the top 5 products by revenue.

Answer:

SELECT TOP 5 p.product_id, p.product_name, SUM(od.unit_price * od.order_quantity) AS total_revenue
FROM products p
JOIN order_details od ON p.product_id = od.product_id
GROUP BY p.product_id, p.product_name
ORDER BY total_revenue DESC;

Explanation: This query lists the top 5 products by revenue.


76. Find the customers who have placed orders on consecutive days.

Answer:

SELECT DISTINCT c.customer_id, c.customer_name
FROM customers c
JOIN orders o1 ON c.customer_id = o1.customer_id
JOIN orders o2 ON c.customer_id = o2.customer_id
WHERE DATEDIFF(day, o1.order_date, o2.order_date) = 1;

Explanation: This query finds the customers who have placed orders on consecutive days.


77. Calculate the total number of orders for each year.

Answer:

SELECT YEAR(order_date) AS order_year, COUNT(*) AS total_orders
FROM orders
GROUP BY YEAR(order_date)
ORDER BY order_year;

Explanation: This query calculates the total number of orders for each year.


78. Retrieve the products that have been ordered by more than 3 different customers.

Answer:

SELECT p.product_id, p.product_name
FROM products p
JOIN order_details od ON p.product_id = od.product_id
JOIN orders o ON od.order_id = o.order_id
GROUP BY p.product_id, p.product_name
HAVING COUNT(DISTINCT o.customer_id) > 3;

Explanation: This query retrieves the products that have been ordered by more than 3 different customers.


79. Find the customers who have placed orders for all available products.

Answer:

SELECT c.customer_id, c.customer_name
FROM customers c
JOIN products p ON NOT EXISTS (
    SELECT * FROM products p2
    WHERE NOT EXISTS (
        SELECT * FROM orders o
        JOIN order_details od ON o.order_id = od.order_id
        WHERE o.customer_id = c.customer_id AND od.product_id = p2.product_id
    )
);

Explanation: This query finds the customers who have placed orders for all available products.


80. Calculate the average time between consecutive orders for each customer.

Answer:

SELECT customer_id, AVG(DATEDIFF(day, prev_order_date, order_date)) AS avg_time_between_orders
FROM (
    SELECT customer_id, order_date, 
           LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_date
    FROM orders
) AS ordered_dates
GROUP BY customer_id;

Explanation: This query calculates the average time between consecutive orders for each customer.


81. Retrieve the names of customers who have placed orders for at least 3 different product categories.

Answer:

SELECT c.customer_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id
GROUP BY c.customer_name
HAVING COUNT(DISTINCT p.category_id) >= 3;

Explanation: This query retrieves the names of customers who have placed orders for at least 3 different product categories.


82. Find the product with the highest total revenue.

Answer:

SELECT TOP 1 p.product_id, p.product_name, SUM(od.unit_price * od.order_quantity) AS total_revenue
FROM products p
JOIN order_details od ON p.product_id = od.product_id
GROUP BY p.product_id, p.product_name
ORDER BY total_revenue DESC;

Explanation: This query finds the product with the highest total revenue.


83. Calculate the total revenue for each month in the year 2023.

Answer:

SELECT DATEPART(month, order_date) AS order_month, 
       DATEPART(year, order_date) AS order_year, 
       SUM(od.unit_price * od.order_quantity) AS total_revenue
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
WHERE DATEPART(year, order_date) = 2023
GROUP BY DATEPART(month, order_date), DATEPART(year, order_date)
ORDER BY order_year, order_month;

Explanation: This query calculates the total revenue for each month in the year 2023.


84. List the products that have not been ordered yet.

Answer:

SELECT p.product_id, p.product_name
FROM products p
LEFT JOIN order_details od ON p.product_id = od.product_id
WHERE od.product_id IS NULL;

Explanation: This query lists the products that have not been ordered yet.


85. Find the customers who have placed orders for all products in a specific category (e.g., CategoryID = 5).

Answer:

SELECT c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT p.product_id
    FROM products p
    WHERE p.category_id = 5
    EXCEPT
    SELECT od.product_id
    FROM order_details od
    JOIN orders o ON od.order_id = o.order_id
    WHERE o.customer_id = c.customer_id
);

Explanation: This query finds the customers who have placed orders for all products in a specific category.


86. Retrieve the top 5 customers who have spent the most on orders.

Answer:

SELECT TOP 5 c.customer_id, c.customer_name, SUM(od.unit_price * od.order_quantity) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_details od ON o.order_id = od.order_id
GROUP BY c.customer_id, c.customer_name
ORDER BY total_spent DESC;

Explanation: This query retrieves the top 5 customers who have spent the most on orders.


87. Calculate the average time it takes for a customer to place a repeat order.

Answer:

SELECT AVG(DATEDIFF(day, o1.order_date, o2.order_date)) AS avg_repeat_time
FROM orders o1
JOIN orders o2 ON o1.customer_id = o2.customer_id
WHERE o2.order_date > o1.order_date;

Explanation: This query calculates the average time it takes for a customer to place a repeat order.


88. List the customers who have placed orders in consecutive months.

Answer:

SELECT DISTINCT c.customer_id, c.customer_name
FROM customers c
JOIN orders o1 ON c.customer_id = o1.customer_id
JOIN orders o2 ON c.customer_id = o2.customer_id
WHERE ABS(DATEDIFF(month, o1.order_date, o2.order_date)) = 1;

Explanation: This query lists the customers who have placed orders in consecutive months.


89. Find the products that have been ordered by every customer.

Answer:

SELECT p.product_id, p.product_name
FROM products p
JOIN order_details od ON p.product_id = od.product_id
GROUP BY p.product_id, p.product_name
HAVING COUNT(DISTINCT od.order_id) = (SELECT COUNT(DISTINCT customer_id) FROM customers);

Explanation: This query finds the products that have been ordered by every customer.


90. Retrieve the order with the highest number of distinct products.

Answer:

SELECT TOP 1 o.order_id, COUNT(DISTINCT od.product_id) AS distinct_products_count
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
GROUP BY o.order_id
ORDER BY distinct_products_count DESC;

Explanation: This query retrieves the order with the highest number of distinct products.


91. Retrieve the average order value for each customer.

Answer:

SELECT o.customer_id, AVG(od.unit_price * od.order_quantity) AS avg_order_value
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
GROUP BY o.customer_id;

Explanation: This query retrieves the average order value for each customer.


92. Identify customers who have not placed any orders.

Answer:

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;

Explanation: This query identifies customers who have not placed any orders.


93. Find the top 3 products with the highest total sales value.

Answer:

SELECT TOP 3 p.product_id, p.product_name, SUM(od.unit_price * od.order_quantity) AS total_sales_value
FROM products p
JOIN order_details od ON p.product_id = od.product_id
GROUP BY p.product_id, p.product_name
ORDER BY total_sales_value DESC;

Explanation: This query finds the top 3 products with the highest total sales value.


94. Calculate the total revenue generated for each month.

Answer:

SELECT DATEPART(year, o.order_date) AS year, DATEPART(month, o.order_date) AS month, 
       SUM(od.unit_price * od.order_quantity) AS total_revenue
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
GROUP BY DATEPART(year, o.order_date), DATEPART(month, o.order_date)
ORDER BY year, month;

Explanation: This query calculates the total revenue generated for each month.


95. Retrieve the customers who have placed orders for all product categories.

Answer:

SELECT c.customer_id, c.customer_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_details od ON o.order_id = od.order_id
GROUP BY c.customer_id, c.customer_name
HAVING COUNT(DISTINCT od.category_id) = (SELECT COUNT(DISTINCT category_id) FROM products);

Explanation: This query retrieves the customers who have placed orders for all product categories.


96. Find the orders with the highest total quantity.

Answer:

SELECT TOP 1 o.order_id, SUM(od.order_quantity) AS total_quantity
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
GROUP BY o.order_id
ORDER BY total_quantity DESC;

Explanation: This query finds the orders with the highest total quantity.


97. Retrieve the customers who have made at least two orders in the last quarter.

Answer:

SELECT c.customer_id, c.customer_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= DATEADD(quarter, -1, GETDATE())
GROUP BY c.customer_id, c.customer_name
HAVING COUNT(o.order_id) >= 2;

Explanation: This query retrieves customers who have made at least two orders in the last quarter.


98. Calculate the total revenue generated by each product category.

Answer:

SELECT p.category_id, pc.category_name, SUM(od.unit_price * od.order_quantity) AS total_revenue
FROM products p
JOIN order_details od ON p.product_id = od.product_id
JOIN product_categories pc ON p.category_id = pc.category_id
GROUP BY p.category_id, pc.category_name;

Explanation: This query calculates the total revenue generated by each product category.


99. Find the products that have not been ordered in the last six months.

Answer:

SELECT p.product_id, p.product_name
FROM products p
WHERE p.product_id NOT IN (
    SELECT DISTINCT od.product_id
    FROM order_details od
    JOIN orders o ON od.order_id = o.order_id
    WHERE o.order_date >= DATEADD(month, -6, GETDATE())
);

Explanation: This query finds products that have not been ordered in the last six months.


100. Retrieve the top 5 customers with the highest total order value.

Answer:

SELECT TOP 5 c.customer_id, c.customer_name, SUM(od.unit_price * od.order_quantity) AS total_order_value
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_details od ON o.order_id = od.order_id
GROUP BY c.customer_id, c.customer_name
ORDER BY total_order_value DESC;

Explanation: This query retrieves the top 5 customers with the highest total order value.