We have 25 SQL puzzles and the answers. After the puzzle section there are answers and explanations.
Here you can find some important SQL keywords and the explanations that are being used in these puzzles
1. Find the Second Highest Salary
Consider a table named employees with the following columns: employee_id and salary. Write a SQL query to find the second-highest salary from the employees table. Your query should return the employee_id and the corresponding salary.
2. Calculate the Average Score
Consider a table named student_scores with the following columns: student_id, subject, and score. Write a SQL query to calculate the average score for each subject. Your query should return the subject along with its average score.
3. Finding Duplicate Entries
Consider a table named customer_orders with the following columns: order_id, customer_id, and order_date. Write a SQL query to find all the duplicate customer_id values in the customer_orders table.
4. Retrieve the Latest Order for Each Customer
Consider a table named orders with the following columns: order_id, customer_id, order_date, and total_amount. Write a SQL query to retrieve the latest order for each customer, including the order_id, customer_id, order_date, and total_amount.
5. Calculate Running Total
Consider a table named sales with the following columns: transaction_id, transaction_date, and amount. Write a SQL query to calculate the running total of the amount column ordered by transaction_date. Include the transaction_id, transaction_date, amount, and the running total in the result.
6. Identify Overlapping Dates
Consider a table named events with the following columns: event_id, event_name, start_date, and end_date. Write a SQL query to identify pairs of events that have overlapping dates. Return the event_id and event_name for each pair.
7. Finding Missing Numbers
Consider a table named numbers with a single column value. The value column contains integers from 1 to 100, but some numbers are missing. Write a SQL query to find the missing numbers in the range from 1 to 100.
8. Finding the Nth Highest Salary
Consider a table named employees with the following columns: employee_id and salary. Write a SQL query to find the Nth highest salary from the employees table. Assume that there are at least N distinct salaries in the table.
9. Calculate the Median Salary
Consider a table named salaries with the following columns: employee_id and salary. Write a SQL query to calculate the median salary from the salaries table.
10. Find Customers with Multiple Orders on the Same Day
Consider two tables named customers and orders with the following columns:
customers table: customer_id, customer_name
orders table: order_id, customer_id, order_date
Write a SQL query to find customers who have placed multiple orders on the same day. Return the customer_id, customer_name, and the order_date where this condition is met.
11. Calculate the Difference in Days Between Consecutive Orders
Consider a table named orders with the following columns: order_id, customer_id, and order_date. Write a SQL query to calculate the difference in days between consecutive orders for each customer. Return the customer_id, order_id, order_date, and the calculated difference in days.
12. Identify Customers with a Decreasing Order Amount Trend
Consider two tables named customers and orders with the following columns:
customers table: customer_id, customer_name
orders table: order_id, customer_id, order_date, order_amount
Write a SQL query to identify customers who have a decreasing trend in order amounts over consecutive orders. Return the customer_id, customer_name, order_date, order_amount, and a flag indicating whether the order amount is less than the previous order.
13. Retrieve the Earliest Order for Each Customer
Consider two tables named customers and orders with the following columns:
customers table: customer_id, customer_name
orders table: order_id, customer_id, order_date, order_amount
Write a SQL query to retrieve the earliest order for each customer, including the customer_id, customer_name, order_id, order_date, and order_amount.
14. Finding the Most Recent Order for Each Customer
Consider two tables named customers and orders with the following columns:
customers table: customer_id, customer_name
orders table: order_id, customer_id, order_date, order_amount
Write a SQL query to find the most recent order for each customer, including the customer_id, customer_name, order_id, order_date, and order_amount.
15. Calculate the Average Time Between Orders for Each Customer
Consider a table named orders with the following columns: order_id, customer_id, and order_date. Write a SQL query to calculate the average time (in days) between consecutive orders for each customer. Return the customer_id, customer_name, and the calculated average time between orders.
16. Identify Customers with Consistently Increasing Order Amounts
Consider two tables named customers and orders with the following columns:
customers table: customer_id, customer_name
orders table: order_id, customer_id, order_date, order_amount
Write a SQL query to identify customers who have consistently increasing order amounts over consecutive orders. Return the customer_id, customer_name, and a flag indicating whether the order amounts consistently increase
17. Retrieve the Second Minimum Order Amount for Each Customer
Consider a table named orders with the following columns: order_id, customer_id, and order_amount. Write a SQL query to retrieve the second minimum order amount for each customer. If a customer has only one order, consider the second minimum as the same as the minimum order amount.
18. Find Customers with Identical Order Amounts
Consider a table named orders with the following columns: order_id, customer_id, and order_amount. Write a SQL query to find customers who have placed orders with identical order amounts. Return the customer_id, order_amount, and the count of orders with that amount.
19. Find the Nth Highest Order Amount for Each Customer
Consider a table named orders with the following columns: order_id, customer_id, and order_amount. Write a SQL query to find the Nth highest order amount for each customer.
20. Calculate the Percentage Growth of Order Amounts
Consider a table named orders with the following columns: order_id, customer_id, order_date, and order_amount. Write a SQL query to calculate the percentage growth of order amounts for each customer, comparing each order amount with the previous order amount. Return the customer_id, order_id, order_date, order_amount, and the calculated percentage growth.
21. Identify Customers with a Continuous Increase in Order Amounts
Consider a table named orders with the following columns: order_id, customer_id, order_date, and order_amount. Write a SQL query to identify customers who have a continuous increase in order amounts, meaning that for each customer, the order amounts are higher than the previous order amount in each consecutive order. Return the customer_id, order_id, order_date, and the order amount.
22. Find Customers with Alternating Increase and Decrease in Order Amounts
Consider a table named orders with the following columns: order_id, customer_id, order_date, and order_amount. Write a SQL query to find customers who have an alternating pattern of increase and decrease in order amounts, meaning that for each customer, the order amounts follow a sequence of increasing and then decreasing values. Return the customer_id, order_id, order_date, and the order amount.
23. Find the Latest Order for Each Customer with a Specific Product
Consider two tables named customers and orders with the following columns:
customers table: customer_id, customer_name
orders table: order_id, customer_id, order_date
Write a SQL query to find the latest order for each customer who has ordered a specific product (let's say, product with product_id equals to 123).
24. Calculate the Running Total of Order Amounts
Consider a table named orders with the following columns: order_id, customer_id, order_date, and order_amount. Write a SQL query to calculate the running total of order amounts for each customer, ordered by order_date.
25. Find Customers with Multiple Orders on the Same Day and Same Amount
Consider a table named orders with the following columns: order_id, customer_id, order_date, and order_amount. Write a SQL query to find customers who have placed multiple orders on the same day and with the same order amount. Return the customer_id, order_date, and order_amount for such orders.
Answers
1. Find the Second Highest Salary
SELECT employee_id,
salary
FROM employees
WHERE salary = (SELECT Max(salary)
FROM employees
WHERE salary < (SELECT Max(salary)
FROM employees));
Explanation:
The innermost subquery (SELECT MAX(salary) FROM employees) finds the maximum salary in the employees table.
The outer subquery (SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees)) finds the maximum salary that is less than the overall maximum salary, effectively giving you the second-highest salary.
The outermost query selects the employee_id and salary from the employees table where the salary matches the second-highest salary found in the subquery.
This query will return the employee_id and salary of the employee with the second-highest salary in the employees table.
2. Calculate the Average Score
SELECT subject,
Avg(score) AS average_score
FROM student_scores
GROUP BY subject
Explanation:
SELECT subject: This specifies the column that you want to include in the result set. Here, you want to retrieve the distinct subjects from the student_scores table.
AVG(score) AS average_score: This calculates the average score for each subject and assigns it an alias (average_score) for better readability in the result set.
FROM student_scores: This specifies the table from which you are retrieving data.
GROUP BY subject: This groups the result set by the subject column, so the average score is calculated for each unique subject.
The query will return a list of subjects along with their corresponding average scores.
3. Finding Duplicate Entries
SELECT customer_id,
Count(*) AS duplicate_count
FROM customer_orders
GROUP BY customer_id
HAVING Count(*) > 1;
Explanation:
SELECT customer_id: This specifies the column for which you want to find duplicates.
COUNT(*) AS duplicate_count: This counts the occurrences of each customer_id and assigns an alias (duplicate_count) for better readability.
FROM customer_orders: This specifies the table from which you are retrieving data.
GROUP BY customer_id: This groups the result set by the customer_id column.
HAVING COUNT(*) > 1: This condition filters the groups to include only those with more than one occurrence, effectively finding the duplicate customer_id values.
The query will return the customer_id values along with the count of occurrences for each duplicate entry.
4. Retrieve the Latest Order for Each Customer
SELECT order_id,
customer_id,
order_date,
total_amount
FROM (SELECT order_id,
customer_id,
order_date,
total_amount,
Row_number()
OVER (
partition BY customer_id
ORDER BY order_date DESC) AS row_num
FROM orders) AS ordered_orders
WHERE row_num = 1;
Explanation:
The inner query uses the ROW_NUMBER() window function to assign a row number to each order within each partition of customers. The PARTITION BY customer_id ensures that the numbering restarts for each customer, and ORDER BY order_date DESC orders the rows within each partition by order_date in descending order.
The outer query selects the columns order_id, customer_id, order_date, and total_amount from the result of the inner query.
The WHERE row_num = 1 condition filters the result set to include only the rows with the latest order for each customer (where the row number is 1 within each partition).
This query will give you the order_id, customer_id, order_date, and total_amount for the latest order of each customer.
5. Calculate Running Total
SELECT transaction_id,
transaction_date,
amount,
Sum(amount)
OVER (
ORDER BY transaction_date) AS running_total
FROM sales;
Explanation:
The SUM(amount) OVER (ORDER BY transaction_date) uses the window function SUM to calculate the running total of the amount column. The ORDER BY transaction_date ensures that the running total is calculated based on the order of transaction_date.
The result set includes transaction_id, transaction_date, amount, and the calculated running_total.
This query will give you a result set with the transaction_id, transaction_date, amount, and the running total of the amount column ordered by transaction_date.
6. Identify Overlapping Dates
SELECT e1.event_id AS event_id1,
e1.event_name AS event_name1,
e2.event_id AS event_id2,
e2.event_name AS event_name2
FROM events e1
JOIN events e2
ON e1.event_id < e2.event_id
WHERE ( e1.start_date <= e2.end_date
AND e1.end_date >= e2.start_date )
OR ( e2.start_date <= e1.end_date
AND e2.end_date >= e1.start_date );
Explanation:
e1 and e2 are aliases for the events table, representing two instances of the same table to compare different rows.
The condition e1.event_id < e2.event_id ensures that we don't compare the same event with itself and avoid duplicate pairs.
The WHERE clause checks for overlapping dates using logical conditions for both event pairs.
This query will return pairs of events (event_id1, event_name1, event_id2, event_name2) where the date ranges overlap. Each pair is included only once to avoid duplicates.
7. Finding Missing Numbers
SELECT DISTINCT n1.value + 1 AS missing_number
FROM numbers n1
LEFT JOIN numbers n2
ON n1.value + 1 = n2.value
WHERE n2.value IS NULL
AND n1.value < 100;
Explanation:
The query uses a self-join on the numbers table (n1 and n2) to compare consecutive values.
n1.value + 1 AS missing_number: This generates a new column representing the potential missing number.
LEFT JOIN numbers n2 ON n1.value + 1 = n2.value: This performs a left join to find matching pairs of consecutive numbers.
WHERE n2.value IS NULL AND n1.value < 100: This condition filters out the rows where there is no match (i.e., the next number is missing) and ensures that we only consider values less than 100.
SELECT DISTINCT: This ensures that each missing number is only listed once.
The result will be a list of missing numbers in the range from 1 to 100.
8. Finding the Nth Highest Salary
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC limit 1 offset n - 1;
Explanation:
ORDER BY salary DESC: This orders the salaries in descending order, so the highest salary comes first.
LIMIT 1 OFFSET N - 1: This limits the result set to only one row, starting from the (N-1)th row. In other words, it skips the first N-1 rows and returns the Nth row, which corresponds to the Nth highest salary.
SELECT DISTINCT salary: This ensures that if there are multiple employees with the same salary at the Nth position, only one distinct salary value is returned.
Replace N with the specific value for the Nth highest salary you want to find.
9. Calculate the Median Salary
SELECT Percentile_cont(0.5) within GROUP (ORDER BY salary) OVER () AS median_salary
FROM salaries;
Explanation:
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER (): This calculates the median by ordering the salaries and then finding the point where 50% of the data falls below and 50% falls above.
Please note that the availability of the PERCENTILE_CONT function and the exact syntax might vary depending on the specific database you're using. If you're using a database that doesn't support this function, there are alternative methods, but they tend to be more complex due to the need to handle different cases (even or odd number of rows).
10. Find Customers with Multiple Orders on the Same Day
SELECT c.customer_id,
c.customer_name,
o.order_date
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
WHERE ( o.order_date, o.customer_id ) IN (SELECT order_date,
customer_id
FROM orders
GROUP BY order_date,
customer_id
HAVING Count(*) > 1);
Explanation:
The subquery (SELECT order_date, customer_id FROM orders GROUP BY order_date, customer_id HAVING COUNT(*) > 1) identifies the combinations of order_date and customer_id where there are multiple orders on the same day.
The main query joins the customers and orders tables on customer_id, and then filters the result based on the conditions specified in the WHERE clause. It selects the customer_id, customer_name, and order_date for customers who have multiple orders on the same day.
This query provides the customer information and the order dates where the specified condition is met.
11. Calculate the Difference in Days Between Consecutive Orders
SELECT customer_id,
order_id,
order_date,
Lag(order_date)
OVER (
partition BY customer_id
ORDER BY order_date) AS previous_order_date,
Datediff(order_date, Lag(order_date)
OVER (
partition BY customer_id
ORDER BY order_date)) AS days_difference
FROM orders;
Explanation:
LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_order_date: This uses the LAG window function to get the order_date of the previous order for each customer, ordered by order_date.
DATEDIFF(order_date, LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)) AS days_difference: This calculates the difference in days between the current order and the previous order for each customer.
The SELECT statement includes the customer_id, order_id, order_date, previous_order_date, and days_difference.
This query provides information about the difference in days between consecutive orders for each customer.
12. Identify Customers with a Decreasing Order Amount Trend
WITH orderedorders
AS (SELECT customer_id,
order_id,
order_date,
order_amount,
Lag(order_amount)
OVER (
partition BY customer_id
ORDER BY order_date) AS previous_order_amount
FROM orders)
SELECT c.customer_id,
c.customer_name,
o.order_id,
o.order_date,
o.order_amount,
CASE
WHEN o.order_amount < o.previous_order_amount THEN 'Decreasing'
ELSE 'Not Decreasing'
END AS trend_flag
FROM customers c
JOIN orderedorders o
ON c.customer_id = o.customer_id;
Explanation:
The Common Table Expression (CTE) named OrderedOrders uses the LAG window function to retrieve the previous order_amount for each order within the same customer, ordered by order_date.
The main query then joins the customers table with the OrderedOrders CTE on the customer_id.
The CASE statement is used to determine whether the order_amount is less than the previous order amount, and it assigns a flag accordingly.
This query provides information about customers, their orders, order dates, order amounts, and a flag indicating whether the order amounts are decreasing over consecutive orders.
13. Retrieve the Earliest Order for Each Customer
WITH earliestorders
AS (SELECT customer_id,
Min(order_date) AS earliest_order_date
FROM orders
GROUP BY customer_id)
SELECT c.customer_id,
c.customer_name,
o.order_id,
o.order_date,
o.order_amount
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
JOIN earliestorders eo
ON o.customer_id = eo.customer_id
AND o.order_date = eo.earliest_order_date;
Explanation:
The Common Table Expression (CTE) named EarliestOrders identifies the earliest order date for each customer using the MIN aggregate function and GROUP BY.
The main query then joins the customers table with the orders table and the EarliestOrders CTE to select the earliest order for each customer.
This query provides information about the earliest order for each customer, including the customer_id, customer_name, order_id, order_date, and order_amount.
14. Finding the Most Recent Order for Each Customer
WITH recentorders
AS (SELECT customer_id,
Max(order_date) AS most_recent_order_date
FROM orders
GROUP BY customer_id)
SELECT c.customer_id,
c.customer_name,
o.order_id,
o.order_date,
o.order_amount
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
JOIN recentorders ro
ON o.customer_id = ro.customer_id
AND o.order_date = ro.most_recent_order_date;
Explanation:
The Common Table Expression (CTE) named RecentOrders identifies the most recent order date for each customer using the MAX aggregate function and GROUP BY.
The main query then joins the customers table with the orders table and the RecentOrders CTE to select the most recent order for each customer.
This query provides information about the most recent order for each customer, including the customer_id, customer_name, order_id, order_date, and order_amount.
15. Calculate the Average Time Between Orders for Each Customer
WITH orderintervals
AS (SELECT customer_id,
Datediff(order_date, Lag(order_date)
OVER (
partition BY customer_id
ORDER BY order_date)) AS
time_between_orders
FROM orders)
SELECT c.customer_id,
c.customer_name,
Avg(time_between_orders) AS average_time_between_orders
FROM customers c
JOIN orderintervals oi
ON c.customer_id = oi.customer_id
GROUP BY c.customer_id,
c.customer_name;
Explanation:
The Common Table Expression (CTE) named OrderIntervals uses the LAG window function to calculate the time between consecutive orders for each customer. The DATEDIFF function calculates the difference in days between the current order and the previous order.
The main query then joins the customers table with the OrderIntervals CTE on the customer_id.\
The AVG(time_between_orders) calculates the average time between consecutive orders for each customer.
The result includes the customer_id, customer_name, and the calculated average_time_between_orders.
This query provides information about the average time between consecutive orders for each customer.
16. Identify Customers with Consistently Increasing Order Amounts
WITH OrderTrends AS ( SELECT customer_id, order_id, order_amount,WITH ordertrends AS
(
SELECT customer_id,
order_id,
order_amount,
Lag(order_amount) OVER (partition BY customer_id ORDER BY order_date) AS previous_order_amount
FROM orders)
SELECT c.customer_id,
c.customer_name,
CASE
WHEN Min(order_amount) = Max(order_amount) THEN ‘constant' WHEN MIN(order_amount) < MAX(order_amount) THEN ‘Increasing'
ELSE 'Not Increasing’ END AS order_trend FROM customers c JOIN OrderTrends ot ON c.customer_id = ot.customer_id GROUP BY c.customer_id, c.customer_name;
LAG(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_order_amount FROM orders)
SELECT c.customer_id, c.customer_name,
CASE WHEN MIN(order_amount) = MAX(order_amount) THEN ‘Constant' WHEN MIN(order_amount) < MAX(order_amount) THEN ‘Increasing' ELSE 'Not Increasing’
END AS order_trend FROM customers c
JOIN OrderTrends ot ON c.customer_id = ot.customer_id
GROUP BY c.customer_id, c.customer_name;
Explanation:
The Common Table Expression (CTE) named OrderTrends uses the LAG window function to get the order_amount of the previous order for each order within the same customer, ordered by order_date.
The main query then joins the customers table with the OrderTrends CTE on the customer_id.
The CASE statement is used to determine the order trend for each customer. If the minimum order amount is equal to the maximum order amount, the trend is 'Constant'. If the minimum order amount is less than the maximum order amount, the trend is 'Increasing', otherwise, it's 'Not Increasing’.
This query provides information about customers, their order trend, and whether their order amounts consistently increase over consecutive orders.
17. Retrieve the Second Minimum Order Amount for Each Customer
WITH rankedorders
AS (SELECT order_id,
customer_id,
order_amount,
Row_number()
OVER (
partition BY customer_id
ORDER BY order_amount) AS order_rank
FROM orders)
SELECT customer_id,
Min(CASE
WHEN order_rank = 2 THEN order_amount
ELSE order_amount
END) AS second_min_order_amount
FROM rankedorders
GROUP BY customer_id;
Explanation:
The Common Table Expression (CTE) named RankedOrders uses the ROW_NUMBER window function to assign a rank to each order amount within each customer, ordered by order_amount.
The main query then selects the customer_id and calculates the minimum order amount using the MIN function. The CASE statement is used to consider the second minimum order amount (order_rank = 2). If a customer has only one order, the query considers the minimum order amount.
This query provides information about the second minimum order amount for each customer.
18. Find Customers with Identical Order Amounts
SELECT customer_id,
order_amount,
Count(*) AS order_count
FROM orders
GROUP BY customer_id,
order_amount
HAVING Count(*) > 1;
Explanation:
The GROUP BY clause groups the orders by customer_id and order_amount.
The COUNT(*) function counts the number of orders for each unique combination of customer_id and order_amount.
The HAVING COUNT(*) > 1 condition filters the result to include only those combinations where there are multiple orders with the same order amount for a customer.
This query will provide information about customers who have placed orders with identical order amounts, including the customer_id, order_amount, and the count of orders with that amount.
19. Find the Nth Highest Order Amount for Each Customer
WITH rankedorders
AS (SELECT order_id,
customer_id,
order_amount,
Row_number()
OVER (
partition BY customer_id
ORDER BY order_amount DESC) AS order_rank
FROM orders)
SELECT customer_id,
order_amount AS nth_highest_order_amount
FROM rankedorders
WHERE order_rank = N;
Explanation:
The Common Table Expression (CTE) named RankedOrders uses the ROW_NUMBER window function to assign a rank to each order amount within each customer, ordered in descending order by order_amount.
The main query then selects the customer_id and the order amount where the order_rank is equal to N, representing the Nth highest order amount.
Replace N with the specific value for the Nth highest order amount you want to find.
20. Calculate the Percentage Growth of Order Amounts
WITH ordergrowth
AS (SELECT order_id,
customer_id,
order_date,
order_amount,
Lag(order_amount)
OVER (
partition BY customer_id
ORDER BY order_date) AS previous_order_amount
FROM orders)
SELECT customer_id,
order_id,
order_date,
order_amount,
CASE
WHEN previous_order_amount IS NULL THEN NULL
ELSE ( ( order_amount - previous_order_amount ) / previous_order_amount
) *
100
END AS percentage_growth
FROM ordergrowth;
Explanation:
The Common Table Expression (CTE) named OrderGrowth uses the LAG window function to get the order_amount of the previous order for each order within the same customer, ordered by order_date.
The main query then calculates the percentage growth using the formula ((order_amount - previous_order_amount) / previous_order_amount) * 100. If previous_order_amount is NULL, the growth is also NULL.
This query provides information about the percentage growth of order amounts for each customer, comparing each order with the previous order.
21. Identify Customers with a Continuous Increase in Order Amounts
WITH ordertrends
AS (SELECT order_id,
customer_id,
order_date,
order_amount,
Lag(order_amount)
OVER (
partition BY customer_id
ORDER BY order_date) AS previous_order_amount
FROM orders)
SELECT customer_id,
order_id,
order_date,
order_amount
FROM ordertrends
WHERE previous_order_amount IS NULL
OR order_amount > previous_order_amount;
Explanation:
The Common Table Expression (CTE) named OrderTrends uses the LAG window function to get the order_amount of the previous order for each order within the same customer, ordered by order_date.
The main query then selects the customer_id, order_id, order_date, and order_amount for orders where either the previous_order_amount is NULL (indicating the first order for a customer) or the order_amount is greater than the previous_order_amount.
This query provides information about customers who have a continuous increase in order amounts, where each order amount is higher than the previous order amount in each consecutive order.
22. Find Customers with Alternating Increase and Decrease in Order Amounts
WITH ordertrends
AS (SELECT order_id,
customer_id,
order_date,
order_amount,
Lag(order_amount)
OVER (
partition BY customer_id
ORDER BY order_date) AS previous_order_amount
FROM orders)
SELECT customer_id,
order_id,
order_date,
order_amount
FROM ordertrends
WHERE ( previous_order_amount IS NULL
OR order_amount > previous_order_amount )
AND ( Lead(order_amount)
OVER (
partition BY customer_id
ORDER BY order_date) IS NULL
OR order_amount < Lead(order_amount)
OVER (
partition BY customer_id
ORDER BY order_date) );
Explanation:
The Common Table Expression (CTE) named OrderTrends uses the LAG window function to get the order_amount of the previous order for each order within the same customer, ordered by order_date.
The main query then selects the customer_id, order_id, order_date, and order_amount for orders where the order amount is greater than the previous order amount and less than the next order amount.
This query provides information about customers who have an alternating pattern of increase and decrease in order amounts.
23. Find the Latest Order for Each Customer with a Specific Product
WITH latestorders
AS (SELECT o.order_id,
o.customer_id,
o.order_date,
Row_number()
OVER (
partition BY o.customer_id
ORDER BY o.order_date DESC) AS row_num
FROM orders o
JOIN order_details od
ON o.order_id = od.order_id
WHERE od.product_id = 123)
SELECT lo.customer_id,
c.customer_name,
lo.order_id,
lo.order_date
FROM latestorders lo
JOIN customers c
ON lo.customer_id = c.customer_id
WHERE lo.row_num = 1;
Explanation:
The Common Table Expression (CTE) named LatestOrders selects orders that include the specific product (product_id = 123) and assigns a row number to each order within the same customer, ordering them by order_date in descending order.
The main query then joins the LatestOrders CTE with the customers table to retrieve customer information.
The final WHERE clause filters the result to include only the latest order for each customer (row_num = 1).
This query provides information about the latest order for each customer who has ordered the specific product with product_id = 123.
24. Calculate the Running Total of Order Amounts
SELECT order_id,
customer_id,
order_date,
order_amount,
Sum(order_amount)
OVER (
partition BY customer_id
ORDER BY order_date) AS running_total
FROM orders;
Explanation:
The SUM(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date) is a window function that calculates the running total of order_amount for each customer. The PARTITION BY clause ensures that the running total resets for each customer, and the ORDER BY clause orders the rows by order_date for the running total calculation.
The result includes the original columns (order_id, customer_id, order_date, order_amount) and the calculated running_total.
This query provides the running total of order amounts for each customer, ordered by order_date.
25. Find Customers with Multiple Orders on the Same Day and Same Amount
WITH duplicateorders
AS (SELECT customer_id,
order_date,
order_amount,
Count(*) AS order_count
FROM orders
GROUP BY customer_id,
order_date,
order_amount
HAVING Count(*) > 1)
SELECT do.customer_id,
do.order_date,
do.order_amount
FROM duplicateorders do
JOIN orders o
ON do.customer_id = o.customer_id
AND do.order_date = o.order_date
AND do.order_amount = o.order_amount;
Explanation:
The Common Table Expression (CTE) named DuplicateOrders identifies orders with the same customer_id, order_date, and order_amount where the count is greater than 1.
The main query then joins the DuplicateOrders CTE with the original orders table to retrieve the customer_id, order_date, and order_amount for such orders.
This query provides information about customers who have placed multiple orders on the same day and with the same order amount.
Top comments (0)