SQL Puzzles
Tags: sql, database, programming, beginners
Published: 2023-12-30 03:00:19

We have 25 SQL puzzles and the answers. After the puzzle section there are answers and explanations.

[Here](https://dev.to/binoy123/sql-key-words-with-the-examples-e6b) 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.