# Top 100 Scenario-Based SQL Interview Questions and Answers

Contents

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

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````SELECT TOP 3 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

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

### 53. Identify products that have never been ordered.

``````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.

``````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.

``````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.

``````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.

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

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

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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).

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````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.

``````SELECT TOP 5 c.customer_id, c.customer_name, SUM(od.unit_price * od.order_quantity) AS total_order_value