{"id":23975,"date":"2023-09-13T02:06:02","date_gmt":"2023-09-13T02:06:02","guid":{"rendered":"https:\/\/machinelearningplus.com\/?p=23975"},"modified":"2023-09-13T17:37:26","modified_gmt":"2023-09-13T17:37:26","slug":"sql-window-function-exercises-and-solutions-set-2","status":"publish","type":"post","link":"https:\/\/machinelearningplus.com\/sql\/sql-window-function-exercises-and-solutions-set-2\/","title":{"rendered":"SQL Window Function Exercises and Solutions – Set 2"},"content":{"rendered":"

SQL window functions exercises is designed to challenge your SQL muscle and help internalize data wrangling using window functions in SQL. The questions are designed like fun puzzles, take your time and try to solve. By the end of this set, you should feel confident in solving the hardest SQL Window function problems.<\/strong><\/p>\n

If you haven’t read the tutorial yet, read SQL Window Functions – Must Read Guide<\/strong><\/a>.<\/p>\n

Try solving hands-on in MySQL using the link provided in each question.<\/p>\n

Author’s note: The solutions provided is (mostly) one of the several possible solutions. The goal is not to conform with the provided solution. Try reach the desired output shown.<\/em><\/p>\n

\"\"<\/a><\/p>\n

Q1. Calculate the running total of sales.<\/h2>\n

Difficulty Level: Intermediate<\/p>\n

Task:<\/strong><\/p>\n

From the sales<\/code> table, calculate the running total of amount<\/code>.<\/p>\n

Input:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n
sale_id<\/th>\nsale_date<\/th>\namount<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n2022-01-01<\/td>\n100<\/td>\n<\/tr>\n
2<\/td>\n2022-01-05<\/td>\n150<\/td>\n<\/tr>\n
3<\/td>\n2022-02-15<\/td>\n200<\/td>\n<\/tr>\n
4<\/td>\n2022-02-20<\/td>\n250<\/td>\n<\/tr>\n
5<\/td>\n2022-03-10<\/td>\n300<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Desired Output:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n
sale_date<\/th>\namount<\/th>\nrunning_total<\/th>\n<\/tr>\n<\/thead>\n
2022-01-01<\/td>\n100<\/td>\n100<\/td>\n<\/tr>\n
2022-01-05<\/td>\n150<\/td>\n250<\/td>\n<\/tr>\n
2022-02-15<\/td>\n200<\/td>\n450<\/td>\n<\/tr>\n
2022-02-20<\/td>\n250<\/td>\n700<\/td>\n<\/tr>\n
2022-03-10<\/td>\n300<\/td>\n1000<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Solve Hands-On: HERE<\/a>, Table Schema and data: Gist<\/a><\/p>\n

Show Solution<\/summary>
<\/p>\n
\r\nSELECT sale_date, \r\namount,\r\nsum(amount) over (order by sale_date) as running_total\r\nFROM sales;\r\n<\/pre>\n
## Q2. Get the difference between the current sale and the previous sale.<\/p>\n
Difficulty Level: Intermediate<\/p>\n
__Task:__<\/p>\n
From the `sales` table, calculate the difference between the current sale and the previous sale.<\/p>\n
__Input:__<\/p>\n
| sale_id | sale_date  | amount |
\n| ——- | ———- | —— |
\n| 1       | 2022-01-01 | 100    |
\n| 2       | 2022-01-05 | 150    |
\n| 3       | 2022-02-15 | 200    |
\n| 4       | 2022-02-20 | 250    |
\n| 5       | 2022-03-10 | 300    |<\/p>\n
__Desired Output:__<\/p>\n
| sale_date  | amount | difference |
\n| ———- | —— | ———- |
\n| 2022-01-01 | 100    | 100        |
\n| 2022-01-05 | 150    | 50         |
\n| 2022-02-15 | 200    | 50         |
\n| 2022-02-20 | 250    | 50         |
\n| 2022-03-10 | 300    | 50         |<\/p>\n
Solve Hands-On: [HERE](https:\/\/www.db-fiddle.com\/f\/xdbTd8VZi6GYaFFzNkDUHz\/1), Table Schema and data: [Gist](https:\/\/gist.github.com\/machinelearningplus\/752971b25fcc4f15d53765c53d3ff3a1)<\/p>\n
Show Solution<\/summary>
<\/div><\/details><\/p>\n
\r\nSELECT sale_date, \r\n\t\tamount, \r\n        amount - LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS difference \r\nFROM Sales;\r\n<\/pre>\n
<\/div><\/details>\n
Q3. Find the highest salary in each department<\/h2>\n
Difficulty Level: Intermediate<\/p>\n
Task:<\/strong><\/p>\n
From the employees<\/code> table, calculate the maximum salary in each department.<\/p>\n
Input:<\/strong><\/p>\n\n\n\n\n\n\n\n\n
dept_id<\/th>\nemp_id<\/th>\nsalary<\/th>\n<\/tr>\n<\/thead>\n
10<\/td>\n1<\/td>\n50000<\/td>\n<\/tr>\n
10<\/td>\n2<\/td>\n55000<\/td>\n<\/tr>\n
20<\/td>\n3<\/td>\n60000<\/td>\n<\/tr>\n
20<\/td>\n4<\/td>\n65000<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Desired Output:<\/strong><\/p>\n\n\n\n\n\n\n\n\n
dept_id<\/th>\nemp_id<\/th>\nsalary<\/th>\nmax_salary<\/th>\n<\/tr>\n<\/thead>\n
10<\/td>\n1<\/td>\n50000<\/td>\n55000<\/td>\n<\/tr>\n
10<\/td>\n2<\/td>\n55000<\/td>\n55000<\/td>\n<\/tr>\n
20<\/td>\n3<\/td>\n60000<\/td>\n65000<\/td>\n<\/tr>\n
20<\/td>\n4<\/td>\n65000<\/td>\n65000<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Solve Hands-On: HERE<\/a>, Table Schema and data: Gist<\/a><\/p>\n
Show Solution<\/summary>
<\/p>\n
\r\nSELECT dept_id, \r\n        emp_id, \r\n        salary,\r\n        MAX(salary) OVER (PARTITION BY dept_id) AS max_salary\r\nFROM Employees;\r\n<\/pre>\n
<\/div><\/details>\n
Q4. Find the difference between the salary of an employee and the average salary of their department<\/h2>\n
Difficulty Level: Intermediate<\/p>\n
Task:<\/strong><\/p>\n
From the employees<\/code> table, find the difference between the salary of an employee and the average salary of their department.<\/p>\n
Input:<\/strong><\/p>\n\n\n\n\n\n\n\n\n
dept_id<\/th>\nemp_id<\/th>\nsalary<\/th>\n<\/tr>\n<\/thead>\n
10<\/td>\n1<\/td>\n50000<\/td>\n<\/tr>\n
10<\/td>\n2<\/td>\n55000<\/td>\n<\/tr>\n
20<\/td>\n3<\/td>\n60000<\/td>\n<\/tr>\n
20<\/td>\n4<\/td>\n65000<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Desired Output:<\/strong><\/p>\n\n\n\n\n\n\n\n\n
emp_id<\/th>\ndept_id<\/th>\nsalary<\/th>\navg_dept_salary<\/th>\ndiff_from_avg<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n10<\/td>\n50000<\/td>\n52500<\/td>\n-2500<\/td>\n<\/tr>\n
2<\/td>\n10<\/td>\n55000<\/td>\n52500<\/td>\n2500<\/td>\n<\/tr>\n
3<\/td>\n20<\/td>\n60000<\/td>\n62500<\/td>\n-2500<\/td>\n<\/tr>\n
4<\/td>\n20<\/td>\n65000<\/td>\n62500<\/td>\n2500<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Solve Hands-On: HERE<\/a>, Table Schema and data: Gist<\/a><\/p>\n
Show Solution<\/summary>
<\/p>\n
\r\nSELECT emp_id, \r\n    dept_id, \r\n    salary,\r\n    AVG(salary) OVER (PARTITION BY dept_id) AS avg_dept_salary,\r\n    salary - AVG(salary) OVER (PARTITION BY dept_id) AS diff_from_avg\r\nFROM Employees;\r\n<\/pre>\n
<\/div><\/details>\n
Q5. Calculate the 2-day moving average for the stock prices.<\/h2>\n
Difficulty Level: Intermediate<\/p>\n
Task:<\/strong><\/p>\n
From the stockprices<\/code> table, find the 2-day moving average.<\/p>\n
Input:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n
date<\/th>\nprice<\/th>\n<\/tr>\n<\/thead>\n
2022-01-01<\/td>\n100.5<\/td>\n<\/tr>\n
2022-01-02<\/td>\n101.75<\/td>\n<\/tr>\n
2022-01-03<\/td>\n102<\/td>\n<\/tr>\n
2022-01-04<\/td>\n103<\/td>\n<\/tr>\n
2022-01-05<\/td>\n103.5<\/td>\n<\/tr>\n
2022-01-06<\/td>\n107<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Desired Output:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n
date<\/th>\nprice<\/th>\nmoving_avg<\/th>\n<\/tr>\n<\/thead>\n
2022-01-01<\/td>\n100.5<\/td>\n100.5<\/td>\n<\/tr>\n
2022-01-02<\/td>\n101.75<\/td>\n101.125<\/td>\n<\/tr>\n
2022-01-03<\/td>\n102<\/td>\n101.875<\/td>\n<\/tr>\n
2022-01-04<\/td>\n103<\/td>\n102.5<\/td>\n<\/tr>\n
2022-01-05<\/td>\n103.5<\/td>\n103.25<\/td>\n<\/tr>\n
2022-01-06<\/td>\n107<\/td>\n105.25<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Solve Hands-On: HERE<\/a>, Table Schema and data: Gist<\/a><\/p>\n
Show Solution<\/summary>
<\/p>\n
\r\nSELECT date, price, \r\n       AVG(price) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS moving_avg\r\nFROM StockPrices;\r\n<\/pre>\n
<\/div><\/details>\n
Q6. Calculate the difference in days between joining dates<\/h2>\n
Difficulty Level: Intermediate<\/p>\n
Task:<\/strong><\/p>\n
Calculate the difference in days between each employee’s joining date and the previous employee’s joining date.<\/p>\n
Input:<\/strong><\/p>\n\n\n\n\n\n\n\n
emp_id<\/th>\njoin_date<\/th>\nsalary<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n2022-01-01<\/td>\n1000<\/td>\n<\/tr>\n
2<\/td>\n2022-01-10<\/td>\n1100<\/td>\n<\/tr>\n
3<\/td>\n2022-01-15<\/td>\n1200<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Desired Output:<\/strong><\/p>\n\n\n\n\n\n\n\n
emp_id<\/th>\njoin_date<\/th>\nday_diff<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n2022-01-01<\/td>\nNULL<\/td>\n<\/tr>\n
2<\/td>\n2022-01-10<\/td>\n9<\/td>\n<\/tr>\n
3<\/td>\n2022-01-15<\/td>\n5<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Solve Hands-On: HERE<\/a>, Table Schema and data: Gist<\/a><\/p>\n
Show Solution<\/summary>
<\/p>\n
\r\nwith sub as (SELECT emp_id, \r\n\t\tjoin_date,\r\n        lag(join_date, 1) over (ORDER BY join_date) as prev_date\r\nFROM employees)\r\nselect *, \r\nDATEDIFF(Date(join_date), date(prev_date)) AS day_diff\r\nfrom sub;\r\n<\/pre>\n
<\/div><\/details>\n
\n
Questions numbers Q7 onwards uses the same table as below. To avoid repetition, the input is printed only for Q7, please use the same for the full question set.<\/em><\/p>\n
Q7. Find the cumulative sales amount for each product.<\/h2>\n
Difficulty Level: Intermediate<\/p>\n
Task:<\/strong><\/p>\n
From Sales<\/code> table, find the cumulative sales amount for each product.<\/p>\n
Input:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
SaleID<\/th>\nProductID<\/th>\nSaleDate<\/th>\nQuantitySold<\/th>\nSalesAmount<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n1<\/td>\n2023-01-01<\/td>\n10<\/td>\n100.00<\/td>\n<\/tr>\n
2<\/td>\n2<\/td>\n2023-01-01<\/td>\n5<\/td>\n50.00<\/td>\n<\/tr>\n
3<\/td>\n3<\/td>\n2023-01-01<\/td>\n15<\/td>\n20.00<\/td>\n<\/tr>\n
4<\/td>\n1<\/td>\n2023-01-02<\/td>\n15<\/td>\n150.00<\/td>\n<\/tr>\n
5<\/td>\n2<\/td>\n2023-01-02<\/td>\n7<\/td>\n70.00<\/td>\n<\/tr>\n
6<\/td>\n1<\/td>\n2023-01-03<\/td>\n12<\/td>\n120.00<\/td>\n<\/tr>\n
7<\/td>\n2<\/td>\n2023-01-03<\/td>\n6<\/td>\n60.00<\/td>\n<\/tr>\n
8<\/td>\n3<\/td>\n2023-01-03<\/td>\n16<\/td>\n30.00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Desired Output:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
ProductID<\/th>\nSaleDate<\/th>\nCumulativeSales<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n2023-01-01<\/td>\n100<\/td>\n<\/tr>\n
1<\/td>\n2023-01-02<\/td>\n250<\/td>\n<\/tr>\n
1<\/td>\n2023-01-03<\/td>\n370<\/td>\n<\/tr>\n
2<\/td>\n2023-01-01<\/td>\n50<\/td>\n<\/tr>\n
2<\/td>\n2023-01-02<\/td>\n120<\/td>\n<\/tr>\n
2<\/td>\n2023-01-03<\/td>\n180<\/td>\n<\/tr>\n
3<\/td>\n2023-01-01<\/td>\n20<\/td>\n<\/tr>\n
3<\/td>\n2023-01-03<\/td>\n50<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Solve Hands-On: HERE<\/a>, Table Schema and data: Gist<\/a><\/p>\n
Show Solution<\/summary>
<\/p>\n
\r\nSELECT \r\n    ProductID,\r\n    SaleDate,\r\n    sum(SalesAmount) over(partition by ProductID order by SaleDate) AS CumulativeSales\r\nFROM sales;\r\n<\/pre>\n
<\/div><\/details>\n
Q8. Compute the average sales for each product over all days.<\/h2>\n
Difficulty Level: Intermediate<\/p>\n
Task:<\/strong><\/p>\n
From Sales<\/code> table, find the cumulative sales amount for each product.<\/p>\n
Input:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
SaleID<\/th>\nProductID<\/th>\nSaleDate<\/th>\nQuantitySold<\/th>\nSalesAmount<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n1<\/td>\n2023-01-01<\/td>\n10<\/td>\n100.00<\/td>\n<\/tr>\n
2<\/td>\n2<\/td>\n2023-01-01<\/td>\n5<\/td>\n50.00<\/td>\n<\/tr>\n
3<\/td>\n3<\/td>\n2023-01-01<\/td>\n15<\/td>\n20.00<\/td>\n<\/tr>\n
4<\/td>\n1<\/td>\n2023-01-02<\/td>\n15<\/td>\n150.00<\/td>\n<\/tr>\n
5<\/td>\n2<\/td>\n2023-01-02<\/td>\n7<\/td>\n70.00<\/td>\n<\/tr>\n
6<\/td>\n1<\/td>\n2023-01-03<\/td>\n12<\/td>\n120.00<\/td>\n<\/tr>\n
7<\/td>\n2<\/td>\n2023-01-03<\/td>\n6<\/td>\n60.00<\/td>\n<\/tr>\n
8<\/td>\n3<\/td>\n2023-01-03<\/td>\n16<\/td>\n30.00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Desired Output:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
ProductID<\/th>\nSaleDate<\/th>\nCumulativeSales<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n2023-01-01<\/td>\n100<\/td>\n<\/tr>\n
1<\/td>\n2023-01-02<\/td>\n250<\/td>\n<\/tr>\n
1<\/td>\n2023-01-03<\/td>\n370<\/td>\n<\/tr>\n
2<\/td>\n2023-01-01<\/td>\n50<\/td>\n<\/tr>\n
2<\/td>\n2023-01-02<\/td>\n120<\/td>\n<\/tr>\n
2<\/td>\n2023-01-03<\/td>\n180<\/td>\n<\/tr>\n
3<\/td>\n2023-01-01<\/td>\n20<\/td>\n<\/tr>\n
3<\/td>\n2023-01-03<\/td>\n50<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Solve Hands-On: HERE<\/a>, Table Schema and data: Gist<\/a><\/p>\n
Show Solution<\/summary>
<\/p>\n
\r\nSELECT \r\n    ProductID,\r\n    SaleDate,\r\n    sum(SalesAmount) over(partition by ProductID order by SaleDate) AS CumulativeSales\r\nFROM sales;\r\n```oyees)\r\nselect *, \r\nDATEDIFF(Date(join_date), date(prev_date)) AS day_diff\r\nfrom sub;\r\n<\/pre>\n
<\/div><\/details>\n
Q9. Compute the average sales for each product over all days<\/h2>\n
Difficulty Level: Intermediate<\/p>\n
Task:<\/strong><\/p>\n
From Sales<\/code> table, compute the average sales for each product over all days.<\/p>\n
Input:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
SaleID<\/th>\nProductID<\/th>\nSaleDate<\/th>\nQuantitySold<\/th>\nSalesAmount<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n1<\/td>\n2023-01-01<\/td>\n10<\/td>\n100.00<\/td>\n<\/tr>\n
2<\/td>\n2<\/td>\n2023-01-01<\/td>\n5<\/td>\n50.00<\/td>\n<\/tr>\n
3<\/td>\n3<\/td>\n2023-01-01<\/td>\n15<\/td>\n20.00<\/td>\n<\/tr>\n
4<\/td>\n1<\/td>\n2023-01-02<\/td>\n15<\/td>\n150.00<\/td>\n<\/tr>\n
5<\/td>\n2<\/td>\n2023-01-02<\/td>\n7<\/td>\n70.00<\/td>\n<\/tr>\n
6<\/td>\n1<\/td>\n2023-01-03<\/td>\n12<\/td>\n120.00<\/td>\n<\/tr>\n
7<\/td>\n2<\/td>\n2023-01-03<\/td>\n6<\/td>\n60.00<\/td>\n<\/tr>\n
8<\/td>\n3<\/td>\n2023-01-03<\/td>\n16<\/td>\n30.00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Desired Output:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
ProductID<\/th>\nAverageSales<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n123.33<\/td>\n<\/tr>\n
1<\/td>\n123.33<\/td>\n<\/tr>\n
1<\/td>\n123.33<\/td>\n<\/tr>\n
2<\/td>\n60<\/td>\n<\/tr>\n
2<\/td>\n60<\/td>\n<\/tr>\n
2<\/td>\n60<\/td>\n<\/tr>\n
3<\/td>\n25<\/td>\n<\/tr>\n
3<\/td>\n25<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Solve Hands-On: HERE<\/a>, Table Schema and data: Gist<\/a><\/p>\n
Show Solution<\/summary>
<\/p>\n
\r\nSELECT \r\n    ProductID,\r\n    AVG(SalesAmount) OVER(PARTITION BY ProductID) AS AverageSales\r\nFROM Sales;\r\n<\/pre>\n
<\/div><\/details>\n
Q10. Calculate the difference between the current day’s sales and the previous day’s sales<\/h2>\n
Difficulty Level: Intermediate<\/p>\n
Task:<\/strong><\/p>\n
From Sales<\/code> table, Calculate the difference between the current day’s sales and the previous day’s sales for each product.<\/p>\n
Input:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
SaleID<\/th>\nProductID<\/th>\nSaleDate<\/th>\nQuantitySold<\/th>\nSalesAmount<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n1<\/td>\n2023-01-01<\/td>\n10<\/td>\n100.00<\/td>\n<\/tr>\n
2<\/td>\n2<\/td>\n2023-01-01<\/td>\n5<\/td>\n50.00<\/td>\n<\/tr>\n
3<\/td>\n3<\/td>\n2023-01-01<\/td>\n15<\/td>\n20.00<\/td>\n<\/tr>\n
4<\/td>\n1<\/td>\n2023-01-02<\/td>\n15<\/td>\n150.00<\/td>\n<\/tr>\n
5<\/td>\n2<\/td>\n2023-01-02<\/td>\n7<\/td>\n70.00<\/td>\n<\/tr>\n
6<\/td>\n1<\/td>\n2023-01-03<\/td>\n12<\/td>\n120.00<\/td>\n<\/tr>\n
7<\/td>\n2<\/td>\n2023-01-03<\/td>\n6<\/td>\n60.00<\/td>\n<\/tr>\n
8<\/td>\n3<\/td>\n2023-01-03<\/td>\n16<\/td>\n30.00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Desired Output:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
ProductID<\/th>\nSaleDate<\/th>\nDifferenceFromPrevious<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n2023-01-01<\/td>\n<\/td>\n<\/tr>\n
1<\/td>\n2023-01-02<\/td>\n50<\/td>\n<\/tr>\n
1<\/td>\n2023-01-03<\/td>\n-30<\/td>\n<\/tr>\n
2<\/td>\n2023-01-01<\/td>\n<\/td>\n<\/tr>\n
2<\/td>\n2023-01-02<\/td>\n20<\/td>\n<\/tr>\n
2<\/td>\n2023-01-03<\/td>\n-10<\/td>\n<\/tr>\n
3<\/td>\n2023-01-01<\/td>\n<\/td>\n<\/tr>\n
3<\/td>\n2023-01-03<\/td>\n10<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Solve Hands-On: HERE<\/a>, Table Schema and data: Gist<\/a><\/p>\n
Show Solution<\/summary>
<\/p>\n
\r\nSELECT \r\n    ProductID,\r\n    SaleDate,\r\n    SalesAmount - LAG(SalesAmount) OVER(PARTITION BY ProductID ORDER BY SaleDate) AS DifferenceFromPrevious\r\nFROM Sales;\r\n<\/pre>\n
<\/div><\/details>\n
Q11. Get the next sale date for each product sale.<\/h2>\n
Difficulty Level: Intermediate<\/p>\n
Task:<\/strong><\/p>\n
From Sales<\/code> table, Get the next sale date for each product sale.<\/p>\n
Input:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
SaleID<\/th>\nProductID<\/th>\nSaleDate<\/th>\nQuantitySold<\/th>\nSalesAmount<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n1<\/td>\n2023-01-01<\/td>\n10<\/td>\n100.00<\/td>\n<\/tr>\n
2<\/td>\n2<\/td>\n2023-01-01<\/td>\n5<\/td>\n50.00<\/td>\n<\/tr>\n
3<\/td>\n3<\/td>\n2023-01-01<\/td>\n15<\/td>\n20.00<\/td>\n<\/tr>\n
4<\/td>\n1<\/td>\n2023-01-02<\/td>\n15<\/td>\n150.00<\/td>\n<\/tr>\n
5<\/td>\n2<\/td>\n2023-01-02<\/td>\n7<\/td>\n70.00<\/td>\n<\/tr>\n
6<\/td>\n1<\/td>\n2023-01-03<\/td>\n12<\/td>\n120.00<\/td>\n<\/tr>\n
7<\/td>\n2<\/td>\n2023-01-03<\/td>\n6<\/td>\n60.00<\/td>\n<\/tr>\n
8<\/td>\n3<\/td>\n2023-01-03<\/td>\n16<\/td>\n30.00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Desired Output:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
ProductID<\/th>\nSaleDate<\/th>\nNextSaleDate<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n2023-01-01<\/td>\n2023-01-02<\/td>\n<\/tr>\n
1<\/td>\n2023-01-02<\/td>\n2023-01-03<\/td>\n<\/tr>\n
1<\/td>\n2023-01-03<\/td>\n<\/td>\n<\/tr>\n
2<\/td>\n2023-01-01<\/td>\n2023-01-02<\/td>\n<\/tr>\n
2<\/td>\n2023-01-02<\/td>\n2023-01-03<\/td>\n<\/tr>\n
2<\/td>\n2023-01-03<\/td>\n<\/td>\n<\/tr>\n
3<\/td>\n2023-01-01<\/td>\n2023-01-03<\/td>\n<\/tr>\n
3<\/td>\n2023-01-03<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Solve Hands-On: HERE<\/a>, Table Schema and data: Gist<\/a><\/p>\n
Show Solution<\/summary>
<\/p>\n
\r\nSELECT \r\n    ProductID,\r\n    SaleDate,\r\n    LEAD(SaleDate) OVER(PARTITION BY ProductID ORDER BY SaleDate) AS NextSaleDate\r\nFROM Sales;\r\n<\/pre>\n
<\/div><\/details>\n
Q12. Find the total sales of the previous day for each product<\/h2>\n
Difficulty Level: Intermediate<\/p>\n
Task:<\/strong><\/p>\n
From Sales<\/code> table, Find the total sales of the previous day for each product.<\/p>\n
Input:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
SaleID<\/th>\nProductID<\/th>\nSaleDate<\/th>\nQuantitySold<\/th>\nSalesAmount<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n1<\/td>\n2023-01-01<\/td>\n10<\/td>\n100.00<\/td>\n<\/tr>\n
2<\/td>\n2<\/td>\n2023-01-01<\/td>\n5<\/td>\n50.00<\/td>\n<\/tr>\n
3<\/td>\n3<\/td>\n2023-01-01<\/td>\n15<\/td>\n20.00<\/td>\n<\/tr>\n
4<\/td>\n1<\/td>\n2023-01-02<\/td>\n15<\/td>\n150.00<\/td>\n<\/tr>\n
5<\/td>\n2<\/td>\n2023-01-02<\/td>\n7<\/td>\n70.00<\/td>\n<\/tr>\n
6<\/td>\n1<\/td>\n2023-01-03<\/td>\n12<\/td>\n120.00<\/td>\n<\/tr>\n
7<\/td>\n2<\/td>\n2023-01-03<\/td>\n6<\/td>\n60.00<\/td>\n<\/tr>\n
8<\/td>\n3<\/td>\n2023-01-03<\/td>\n16<\/td>\n30.00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Desired Output:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
ProductID<\/th>\nSaleDate<\/th>\nPreviousDaySales<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n2023-01-01<\/td>\n<\/td>\n<\/tr>\n
1<\/td>\n2023-01-02<\/td>\n100<\/td>\n<\/tr>\n
1<\/td>\n2023-01-03<\/td>\n150<\/td>\n<\/tr>\n
2<\/td>\n2023-01-01<\/td>\n<\/td>\n<\/tr>\n
2<\/td>\n2023-01-02<\/td>\n50<\/td>\n<\/tr>\n
2<\/td>\n2023-01-03<\/td>\n70<\/td>\n<\/tr>\n
3<\/td>\n2023-01-01<\/td>\n<\/td>\n<\/tr>\n
3<\/td>\n2023-01-03<\/td>\n20<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Solve Hands-On: HERE<\/a>, Table Schema and data: Gist<\/a><\/p>\n
Show Solution<\/summary>
\n
\r\nSELECT \r\n    ProductID,\r\n    SaleDate,\r\n    LAG(SalesAmount) OVER(PARTITION BY ProductID ORDER BY SaleDate) AS PreviousDaySales\r\nFROM Sales;\r\n
\r\n<\/div><\/details>\n\n
Q13. Calculate the average sales amount of the previous two days<\/h2>\n\nDifficulty Level: Hard\n\nTask:<\/strong>\n\nFrom Sales<\/code> table, for each sale, calculate the average sales amount of the previous two days (including the current day) for each product.\n\nInput:<\/strong>\n\n\n\n\n  \n\n  \n  \n  \n  \n  \n  \n  \n  
SaleID<\/th>\n  ProductID<\/th>\n  SaleDate<\/th>\n  QuantitySold<\/th>\n  SalesAmount<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n  1<\/td>\n  2023-01-01<\/td>\n  10<\/td>\n  100.00<\/td>\n<\/tr>\n
2<\/td>\n  2<\/td>\n  2023-01-01<\/td>\n  5<\/td>\n  50.00<\/td>\n<\/tr>\n
3<\/td>\n  3<\/td>\n  2023-01-01<\/td>\n  15<\/td>\n  20.00<\/td>\n<\/tr>\n
4<\/td>\n  1<\/td>\n  2023-01-02<\/td>\n  15<\/td>\n  150.00<\/td>\n<\/tr>\n
5<\/td>\n  2<\/td>\n  2023-01-02<\/td>\n  7<\/td>\n  70.00<\/td>\n<\/tr>\n
6<\/td>\n  1<\/td>\n  2023-01-03<\/td>\n  12<\/td>\n  120.00<\/td>\n<\/tr>\n
7<\/td>\n  2<\/td>\n  2023-01-03<\/td>\n  6<\/td>\n  60.00<\/td>\n<\/tr>\n
8<\/td>\n  3<\/td>\n  2023-01-03<\/td>\n  16<\/td>\n  30.00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\nDesired Output:<\/strong>\n\n\n\n\n  \n\n  \n  \n  \n  \n  \n  \n  \n  
ProductID<\/th>\n  SaleDate<\/th>\n  AvgOfLastTwoDays<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n  2023-01-01<\/td>\n  100<\/td>\n<\/tr>\n
1<\/td>\n  2023-01-02<\/td>\n  125<\/td>\n<\/tr>\n
1<\/td>\n  2023-01-03<\/td>\n  135<\/td>\n<\/tr>\n
2<\/td>\n  2023-01-01<\/td>\n  50<\/td>\n<\/tr>\n
2<\/td>\n  2023-01-02<\/td>\n  60<\/td>\n<\/tr>\n
2<\/td>\n  2023-01-03<\/td>\n  65<\/td>\n<\/tr>\n
3<\/td>\n  2023-01-01<\/td>\n  20<\/td>\n<\/tr>\n
3<\/td>\n  2023-01-03<\/td>\n  25<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\nSolve Hands-On: HERE<\/a>, Table Schema and data: Gist<\/a>\n\n
Show Solution<\/summary>
\r\n
\r\nSELECT \r\n    ProductID,\r\n    SaleDate,\r\n    AVG(SalesAmount) OVER(PARTITION BY ProductID ORDER BY SaleDate ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS AvgOfLastTwoDays\r\nFROM Sales;\r\n<\/pre>\n
<\/div><\/details><\/p>\n
Q14. Find the date of maximum sale<\/h2>\n
Difficulty Level: Hard<\/p>\n
Task:<\/strong><\/p>\n
From Sales<\/code> table, for each sale, get the sale date where maximum sales were made in the previous 2 days for each product.<\/p>\n
Input:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
SaleID<\/th>\nProductID<\/th>\nSaleDate<\/th>\nQuantitySold<\/th>\nSalesAmount<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n1<\/td>\n2023-01-01<\/td>\n10<\/td>\n100.00<\/td>\n<\/tr>\n
2<\/td>\n2<\/td>\n2023-01-01<\/td>\n5<\/td>\n50.00<\/td>\n<\/tr>\n
3<\/td>\n3<\/td>\n2023-01-01<\/td>\n15<\/td>\n20.00<\/td>\n<\/tr>\n
4<\/td>\n1<\/td>\n2023-01-02<\/td>\n15<\/td>\n150.00<\/td>\n<\/tr>\n
5<\/td>\n2<\/td>\n2023-01-02<\/td>\n7<\/td>\n70.00<\/td>\n<\/tr>\n
6<\/td>\n1<\/td>\n2023-01-03<\/td>\n12<\/td>\n120.00<\/td>\n<\/tr>\n
7<\/td>\n2<\/td>\n2023-01-03<\/td>\n6<\/td>\n60.00<\/td>\n<\/tr>\n
8<\/td>\n3<\/td>\n2023-01-03<\/td>\n16<\/td>\n30.00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Desired Output:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
ProductID<\/th>\nSaleDate<\/th>\nMaxSaleDateLastTwoDays<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n2023-01-02<\/td>\n2023-01-02<\/td>\n<\/tr>\n
1<\/td>\n2023-01-03<\/td>\n2023-01-02<\/td>\n<\/tr>\n
1<\/td>\n2023-01-01<\/td>\n2023-01-02<\/td>\n<\/tr>\n
2<\/td>\n2023-01-02<\/td>\n2023-01-02<\/td>\n<\/tr>\n
2<\/td>\n2023-01-03<\/td>\n2023-01-02<\/td>\n<\/tr>\n
2<\/td>\n2023-01-01<\/td>\n2023-01-02<\/td>\n<\/tr>\n
3<\/td>\n2023-01-03<\/td>\n2023-01-03<\/td>\n<\/tr>\n
3<\/td>\n2023-01-01<\/td>\n2023-01-03<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Solve Hands-On: HERE<\/a>, Table Schema and data: Gist<\/a><\/p>\n
Show Solution<\/summary>
<\/p>\n
\r\nSELECT \r\n    ProductID,\r\n    SaleDate,\r\n    FIRST_VALUE(SaleDate) OVER(PARTITION BY ProductID ORDER BY SalesAmount DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MaxSaleDateLastTwoDays\r\nFROM Sales;\r\n<\/pre>\n
<\/div><\/details>\n
Q15. Calculate the percentage contribution of each product's sale<\/h2>\n
Difficulty Level: Hard<\/p>\n
Task:<\/strong><\/p>\n
From Sales<\/code> table, Calculate the percentage contribution of each product's sale to the total sales of that day.<\/p>\n
Input:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
SaleID<\/th>\nProductID<\/th>\nSaleDate<\/th>\nQuantitySold<\/th>\nSalesAmount<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n1<\/td>\n2023-01-01<\/td>\n10<\/td>\n100.00<\/td>\n<\/tr>\n
2<\/td>\n2<\/td>\n2023-01-01<\/td>\n5<\/td>\n50.00<\/td>\n<\/tr>\n
3<\/td>\n3<\/td>\n2023-01-01<\/td>\n15<\/td>\n20.00<\/td>\n<\/tr>\n
4<\/td>\n1<\/td>\n2023-01-02<\/td>\n15<\/td>\n150.00<\/td>\n<\/tr>\n
5<\/td>\n2<\/td>\n2023-01-02<\/td>\n7<\/td>\n70.00<\/td>\n<\/tr>\n
6<\/td>\n1<\/td>\n2023-01-03<\/td>\n12<\/td>\n120.00<\/td>\n<\/tr>\n
7<\/td>\n2<\/td>\n2023-01-03<\/td>\n6<\/td>\n60.00<\/td>\n<\/tr>\n
8<\/td>\n3<\/td>\n2023-01-03<\/td>\n16<\/td>\n30.00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Desired Output:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
ProductID<\/th>\nSaleDate<\/th>\nMaxSaleDateLastTwoDays<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n2023-01-02<\/td>\n2023-01-02<\/td>\n<\/tr>\n
1<\/td>\n2023-01-03<\/td>\n2023-01-02<\/td>\n<\/tr>\n
1<\/td>\n2023-01-01<\/td>\n2023-01-02<\/td>\n<\/tr>\n
2<\/td>\n2023-01-02<\/td>\n2023-01-02<\/td>\n<\/tr>\n
2<\/td>\n2023-01-03<\/td>\n2023-01-02<\/td>\n<\/tr>\n
2<\/td>\n2023-01-01<\/td>\n2023-01-02<\/td>\n<\/tr>\n
3<\/td>\n2023-01-03<\/td>\n2023-01-03<\/td>\n<\/tr>\n
3<\/td>\n2023-01-01<\/td>\n2023-01-03<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Solve Hands-On: HERE<\/a>, Table Schema and data: Gist<\/a><\/p>\n
Show Solution<\/summary>
<\/p>\n
\r\nSELECT \r\n    ProductID,\r\n    SaleDate,\r\n    SalesAmount,\r\n    round((SalesAmount \/ SUM(SalesAmount) OVER(PARTITION BY SaleDate)) * 100, 2) AS PercentageContribution\r\nFROM sales;\r\n<\/pre>\n
<\/div><\/details>\n
Q16. Calculate the third highest sales amount<\/h2>\n
Difficulty Level: Hard<\/p>\n
Task:<\/strong><\/p>\n
For each product, get the third highest sales amount and its corresponding sale date.<\/p>\n
Input:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
SaleID<\/th>\nProductID<\/th>\nSaleDate<\/th>\nQuantitySold<\/th>\nSalesAmount<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n1<\/td>\n2023-01-01<\/td>\n10<\/td>\n100.00<\/td>\n<\/tr>\n
2<\/td>\n2<\/td>\n2023-01-01<\/td>\n5<\/td>\n50.00<\/td>\n<\/tr>\n
3<\/td>\n3<\/td>\n2023-01-01<\/td>\n15<\/td>\n20.00<\/td>\n<\/tr>\n
4<\/td>\n1<\/td>\n2023-01-02<\/td>\n15<\/td>\n150.00<\/td>\n<\/tr>\n
5<\/td>\n2<\/td>\n2023-01-02<\/td>\n7<\/td>\n70.00<\/td>\n<\/tr>\n
6<\/td>\n1<\/td>\n2023-01-03<\/td>\n12<\/td>\n120.00<\/td>\n<\/tr>\n
7<\/td>\n2<\/td>\n2023-01-03<\/td>\n6<\/td>\n60.00<\/td>\n<\/tr>\n
8<\/td>\n3<\/td>\n2023-01-03<\/td>\n16<\/td>\n30.00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Desired Output:<\/strong><\/p>\n\n\n\n\n\n\n
ProductID<\/th>\nSaleDate<\/th>\nSalesAmount<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n2023-01-01<\/td>\n100.00<\/td>\n<\/tr>\n
2<\/td>\n2023-01-01<\/td>\n50.00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Solve Hands-On: HERE<\/a>, Table Schema and data: Gist<\/a><\/p>\n
Show Solution<\/summary>
<\/p>\n
\r\nSELECT \r\n    ProductID,\r\n    SaleDate,\r\n    SalesAmount\r\nFROM (\r\n    SELECT \r\n        ProductID,\r\n        SaleDate,\r\n        SalesAmount,\r\n        DENSE_RANK() OVER(PARTITION BY ProductID ORDER BY SalesAmount DESC) AS rnk\r\n    FROM sales\r\n) AS T\r\nWHERE rnk = 3;\r\n<\/pre>\n
<\/div><\/details>\n
Q17. Calculate the moving variance of the last 3 sales amounts for each product.<\/h2>\n
Difficulty Level: Hard<\/p>\n
Task:<\/strong><\/p>\n
From sales<\/code> table, calculate the moving variance of the last 3 sales amounts for each product.<\/p>\n
Input:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
SaleID<\/th>\nProductID<\/th>\nSaleDate<\/th>\nQuantitySold<\/th>\nSalesAmount<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n1<\/td>\n2023-01-01<\/td>\n10<\/td>\n100.00<\/td>\n<\/tr>\n
2<\/td>\n2<\/td>\n2023-01-01<\/td>\n5<\/td>\n50.00<\/td>\n<\/tr>\n
3<\/td>\n3<\/td>\n2023-01-01<\/td>\n15<\/td>\n20.00<\/td>\n<\/tr>\n
4<\/td>\n1<\/td>\n2023-01-02<\/td>\n15<\/td>\n150.00<\/td>\n<\/tr>\n
5<\/td>\n2<\/td>\n2023-01-02<\/td>\n7<\/td>\n70.00<\/td>\n<\/tr>\n
6<\/td>\n1<\/td>\n2023-01-03<\/td>\n12<\/td>\n120.00<\/td>\n<\/tr>\n
7<\/td>\n2<\/td>\n2023-01-03<\/td>\n6<\/td>\n60.00<\/td>\n<\/tr>\n
8<\/td>\n3<\/td>\n2023-01-03<\/td>\n16<\/td>\n30.00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Desired Output:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
ProductID<\/th>\nSaleDate<\/th>\nMovingVariance<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n2023-01-01<\/td>\n0<\/td>\n<\/tr>\n
1<\/td>\n2023-01-02<\/td>\n625<\/td>\n<\/tr>\n
1<\/td>\n2023-01-03<\/td>\n422.22222222222223<\/td>\n<\/tr>\n
2<\/td>\n2023-01-01<\/td>\n0<\/td>\n<\/tr>\n
2<\/td>\n2023-01-02<\/td>\n100<\/td>\n<\/tr>\n
2<\/td>\n2023-01-03<\/td>\n66.66666666666667<\/td>\n<\/tr>\n
3<\/td>\n2023-01-01<\/td>\n0<\/td>\n<\/tr>\n
3<\/td>\n2023-01-03<\/td>\n25<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Solve Hands-On: HERE<\/a>, Table Schema and data: Gist<\/a><\/p>\n
Show Solution<\/summary>
<\/p>\n
\r\nSELECT \r\n    ProductID,\r\n    SaleDate,\r\n    VARIANCE(SalesAmount) OVER(PARTITION BY ProductID ORDER BY SaleDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingVariance\r\nFROM sales;\r\n<\/pre>\n
<\/div><\/details>\n
Q18. Find the product that had the least sales amount difference compared to the previous day.<\/h2>\n
Difficulty Level: Hard<\/p>\n
Task:<\/strong><\/p>\n
From sales<\/code> table, For each sale date, find the product that had the least sales amount difference compared to the previous day.<\/p>\n
Input:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
SaleID<\/th>\nProductID<\/th>\nSaleDate<\/th>\nQuantitySold<\/th>\nSalesAmount<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n1<\/td>\n2023-01-01<\/td>\n10<\/td>\n100.00<\/td>\n<\/tr>\n
2<\/td>\n2<\/td>\n2023-01-01<\/td>\n5<\/td>\n50.00<\/td>\n<\/tr>\n
3<\/td>\n3<\/td>\n2023-01-01<\/td>\n15<\/td>\n20.00<\/td>\n<\/tr>\n
4<\/td>\n1<\/td>\n2023-01-02<\/td>\n15<\/td>\n150.00<\/td>\n<\/tr>\n
5<\/td>\n2<\/td>\n2023-01-02<\/td>\n7<\/td>\n70.00<\/td>\n<\/tr>\n
6<\/td>\n1<\/td>\n2023-01-03<\/td>\n16<\/td>\n160.00<\/td>\n<\/tr>\n
7<\/td>\n2<\/td>\n2023-01-03<\/td>\n6<\/td>\n60.00<\/td>\n<\/tr>\n
8<\/td>\n3<\/td>\n2023-01-03<\/td>\n16<\/td>\n30.00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Desired Output:<\/strong><\/p>\n\n\n\n\n\n\n\n
SaleDate<\/th>\nProductID<\/th>\nDiff<\/th>\n<\/tr>\n<\/thead>\n
2023-01-01<\/td>\n1<\/td>\n<\/td>\n<\/tr>\n
2023-01-02<\/td>\n2<\/td>\n20.00<\/td>\n<\/tr>\n
2023-01-03<\/td>\n2<\/td>\n10.00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Solve Hands-On: HERE<\/a>, Table Schema and data: Gist<\/a><\/p>\n
Show Solution<\/summary>
<\/p>\n
\r\nWITH Differences AS (\r\n    SELECT \r\n        ProductID,\r\n        SaleDate,\r\n        ABS(SalesAmount - LAG(SalesAmount) OVER(PARTITION BY ProductID ORDER BY SaleDate)) AS Diff\r\n    FROM sales\r\n)\r\n\r\nSELECT \r\n    SaleDate,\r\n    ProductID,\r\n    Diff\r\nFROM (\r\n    SELECT \r\n        SaleDate,\r\n        ProductID,\r\n        Diff,\r\n        ROW_NUMBER() OVER(PARTITION BY SaleDate ORDER BY Diff ASC) AS rnk\r\n    FROM Differences\r\n) AS T\r\nWHERE rnk = 1;\r\n<\/pre>\n
<\/div><\/details>\n
Q19. Determine the average change in sales amounts<\/h2>\n
Difficulty Level: Hard<\/p>\n
Task:<\/strong><\/p>\n
From sales<\/code> table, for each product, determine the average change in sales amounts corresponding to the previous sale day.<\/p>\n
Input:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
SaleID<\/th>\nProductID<\/th>\nSaleDate<\/th>\nQuantitySold<\/th>\nSalesAmount<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n1<\/td>\n2023-01-01<\/td>\n10<\/td>\n100.00<\/td>\n<\/tr>\n
2<\/td>\n2<\/td>\n2023-01-01<\/td>\n5<\/td>\n50.00<\/td>\n<\/tr>\n
3<\/td>\n3<\/td>\n2023-01-01<\/td>\n15<\/td>\n20.00<\/td>\n<\/tr>\n
4<\/td>\n1<\/td>\n2023-01-02<\/td>\n15<\/td>\n150.00<\/td>\n<\/tr>\n
5<\/td>\n2<\/td>\n2023-01-02<\/td>\n7<\/td>\n70.00<\/td>\n<\/tr>\n
6<\/td>\n1<\/td>\n2023-01-03<\/td>\n16<\/td>\n160.00<\/td>\n<\/tr>\n
7<\/td>\n2<\/td>\n2023-01-03<\/td>\n6<\/td>\n60.00<\/td>\n<\/tr>\n
8<\/td>\n3<\/td>\n2023-01-03<\/td>\n16<\/td>\n30.00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Desired Output:<\/strong><\/p>\n\n\n\n\n\n\n\n
PRODUCTID<\/th>\nAVGCHANGE<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n30.000000<\/td>\n<\/tr>\n
2<\/td>\n5.000000<\/td>\n<\/tr>\n
3<\/td>\n10.000000<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Solve Hands-On: HERE<\/a>, Table Schema and data: Gist<\/a><\/p>\n
Show Solution<\/summary>
<\/p>\n
\r\nwith tbl as (SELECT \r\n    PRODUCTID,\r\n    SALEDATE,\r\n    (SalesAmount - LAG(SalesAmount, 1) OVER(PARTITION BY PRODUCTID ORDER BY SALEDATE)) AS diff,\r\n    COUNT(*) over (PARTITION BY PRODUCTID) - 1 as num_changes\r\nFROM sales),\r\n\r\ntbl2 as (\r\nSELECT PRODUCTID,\r\n        max(SALEDATE) as SALEDATE,\r\n        sum(diff) as SUMDIFF,\r\n        max(num_changes) as TOTALCHANGES\r\n        from tbl\r\ngroup by ProductID)\r\nSELECT PRODUCTID,\r\n       SUMDIFF\/TOTALCHANGES as AVGCHANGE\r\nFROM tbl2;\r\n<\/pre>\n
<\/div><\/details>\n
Q20. Find the median sales amount for each product.<\/h2>\n
Difficulty Level: Hard<\/p>\n
Task:<\/strong><\/p>\n
From sales<\/code> table, for each product, determine the average change in sales amounts corresponding to the previous sale day.<\/p>\n
Input:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
SaleID<\/th>\nProductID<\/th>\nSaleDate<\/th>\nQuantitySold<\/th>\nSalesAmount<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n1<\/td>\n2023-01-01<\/td>\n10<\/td>\n100.00<\/td>\n<\/tr>\n
2<\/td>\n2<\/td>\n2023-01-01<\/td>\n5<\/td>\n50.00<\/td>\n<\/tr>\n
3<\/td>\n3<\/td>\n2023-01-01<\/td>\n15<\/td>\n20.00<\/td>\n<\/tr>\n
4<\/td>\n1<\/td>\n2023-01-02<\/td>\n15<\/td>\n150.00<\/td>\n<\/tr>\n
5<\/td>\n2<\/td>\n2023-01-02<\/td>\n7<\/td>\n70.00<\/td>\n<\/tr>\n
6<\/td>\n1<\/td>\n2023-01-03<\/td>\n16<\/td>\n160.00<\/td>\n<\/tr>\n
7<\/td>\n2<\/td>\n2023-01-03<\/td>\n6<\/td>\n60.00<\/td>\n<\/tr>\n
8<\/td>\n3<\/td>\n2023-01-03<\/td>\n16<\/td>\n30.00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Desired Output:<\/strong><\/p>\n\n\n\n\n\n\n\n
ProductID<\/th>\nMedian<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n150.000000<\/td>\n<\/tr>\n
2<\/td>\n60.000000<\/td>\n<\/tr>\n
3<\/td>\n25.000000<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Solve Hands-On: HERE<\/a>, Table Schema and data: Gist<\/a><\/p>\n
Show Solution<\/summary>
<\/p>\n
\r\nWITH RankedSales AS (\r\n    SELECT \r\n        ProductID,\r\n        SalesAmount,\r\n        ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY SalesAmount) AS rn,\r\n        COUNT(*) OVER(PARTITION BY ProductID) AS cnt\r\n    FROM sales\r\n)\r\n\r\nSELECT \r\n    ProductID,\r\n    AVG(SalesAmount) AS Median\r\nFROM RankedSales\r\nWHERE rn IN (FLOOR((cnt+1)\/2), CEIL((cnt+1)\/2))\r\nGROUP BY ProductID;\r\n<\/pre>\n
<\/div><\/details>\n
Q20. Find the difference from the average for each entry<\/h2>\n
Difficulty Level: Hard<\/p>\n
Task:<\/strong><\/p>\n
For each sale, calculate the difference from the monthly average of the product's sales amount.<\/p>\n
Input:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
SaleID<\/th>\nProductID<\/th>\nSaleDate<\/th>\nQuantitySold<\/th>\nSalesAmount<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n1<\/td>\n2023-01-01<\/td>\n10<\/td>\n100.00<\/td>\n<\/tr>\n
2<\/td>\n2<\/td>\n2023-01-01<\/td>\n5<\/td>\n50.00<\/td>\n<\/tr>\n
3<\/td>\n3<\/td>\n2023-01-01<\/td>\n15<\/td>\n20.00<\/td>\n<\/tr>\n
4<\/td>\n1<\/td>\n2023-01-02<\/td>\n15<\/td>\n150.00<\/td>\n<\/tr>\n
5<\/td>\n2<\/td>\n2023-01-02<\/td>\n7<\/td>\n70.00<\/td>\n<\/tr>\n
6<\/td>\n1<\/td>\n2023-01-03<\/td>\n16<\/td>\n160.00<\/td>\n<\/tr>\n
7<\/td>\n2<\/td>\n2023-01-03<\/td>\n6<\/td>\n60.00<\/td>\n<\/tr>\n
8<\/td>\n3<\/td>\n2023-01-03<\/td>\n16<\/td>\n30.00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Desired Output:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
ProductID<\/th>\nSaleDate<\/th>\nSalesAmount<\/th>\nDiffFromMonthlyAvg<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n2023-01-01<\/td>\n100.00<\/td>\n-36.666667<\/td>\n<\/tr>\n
1<\/td>\n2023-01-02<\/td>\n150.00<\/td>\n13.333333<\/td>\n<\/tr>\n
1<\/td>\n2023-01-03<\/td>\n160.00<\/td>\n23.333333<\/td>\n<\/tr>\n
2<\/td>\n2023-01-01<\/td>\n50.00<\/td>\n-10.000000<\/td>\n<\/tr>\n
2<\/td>\n2023-01-02<\/td>\n70.00<\/td>\n10.000000<\/td>\n<\/tr>\n
2<\/td>\n2023-01-03<\/td>\n60.00<\/td>\n0.000000<\/td>\n<\/tr>\n
3<\/td>\n2023-01-01<\/td>\n20.00<\/td>\n-5.000000<\/td>\n<\/tr>\n
3<\/td>\n2023-01-03<\/td>\n30.00<\/td>\n5.000000<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Solve Hands-On: HERE<\/a>, Table Schema and data: Gist<\/a><\/p>\n
Show Solution<\/summary>
<\/p>\n
\r\nSELECT \r\n    ProductID,\r\n    SaleDate,\r\n    SalesAmount,\r\n    SalesAmount - AVG(SalesAmount) OVER(PARTITION BY ProductID, EXTRACT(MONTH FROM SaleDate)) AS DiffFromMonthlyAvg\r\nFROM sales;\r\n<\/pre>\n
<\/div><\/details>\n
Q21. Rank products by the variability<\/h2>\n
Difficulty Level: Hard<\/p>\n
Task:<\/strong><\/p>\n
For each sale, calculate the difference from the monthly average of the product's sales amount.<\/p>\n
Input:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
SaleID<\/th>\nProductID<\/th>\nSaleDate<\/th>\nQuantitySold<\/th>\nSalesAmount<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n1<\/td>\n2023-01-01<\/td>\n10<\/td>\n100.00<\/td>\n<\/tr>\n
2<\/td>\n2<\/td>\n2023-01-01<\/td>\n5<\/td>\n50.00<\/td>\n<\/tr>\n
3<\/td>\n3<\/td>\n2023-01-01<\/td>\n15<\/td>\n20.00<\/td>\n<\/tr>\n
4<\/td>\n1<\/td>\n2023-01-02<\/td>\n15<\/td>\n150.00<\/td>\n<\/tr>\n
5<\/td>\n2<\/td>\n2023-01-02<\/td>\n7<\/td>\n70.00<\/td>\n<\/tr>\n
6<\/td>\n1<\/td>\n2023-01-03<\/td>\n16<\/td>\n160.00<\/td>\n<\/tr>\n
7<\/td>\n2<\/td>\n2023-01-03<\/td>\n6<\/td>\n60.00<\/td>\n<\/tr>\n
8<\/td>\n3<\/td>\n2023-01-03<\/td>\n16<\/td>\n30.00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Desired Output:<\/strong><\/p>\n\n\n\n\n\n\n\n
ProductID<\/th>\nSTDV<\/th>\nVariabilityRank<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n32.1455<\/td>\n1<\/td>\n<\/tr>\n
2<\/td>\n10<\/td>\n2<\/td>\n<\/tr>\n
3<\/td>\n7.0711<\/td>\n3<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Solve Hands-On: HERE<\/a>, Table Schema and data: Gist<\/a><\/p>\n
Show Solution<\/summary>
<\/p>\n
\r\nSELECT \r\n    ProductID,\r\n    ROUND(STDDEV_SAMP(SalesAmount),4) as STDV,\r\n    RANK() OVER(ORDER BY STDDEV_SAMP(SalesAmount) DESC) AS VariabilityRank\r\nFROM sales\r\nGROUP BY ProductID;\r\n<\/pre>\n
<\/div><\/details>\n
Q22. Calculate a 3-day centered moving average<\/h2>\n
Difficulty Level: Hard<\/p>\n
Task:<\/strong><\/p>\n
Calculate a 3-day centered moving average for each product's sales amount.<\/p>\n
Input:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
SaleID<\/th>\nProductID<\/th>\nSaleDate<\/th>\nQuantitySold<\/th>\nSalesAmount<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n1<\/td>\n2023-01-01<\/td>\n10<\/td>\n100.00<\/td>\n<\/tr>\n
2<\/td>\n2<\/td>\n2023-01-01<\/td>\n5<\/td>\n50.00<\/td>\n<\/tr>\n
3<\/td>\n3<\/td>\n2023-01-01<\/td>\n15<\/td>\n20.00<\/td>\n<\/tr>\n
4<\/td>\n1<\/td>\n2023-01-02<\/td>\n15<\/td>\n150.00<\/td>\n<\/tr>\n
5<\/td>\n2<\/td>\n2023-01-02<\/td>\n7<\/td>\n70.00<\/td>\n<\/tr>\n
6<\/td>\n1<\/td>\n2023-01-03<\/td>\n16<\/td>\n160.00<\/td>\n<\/tr>\n
7<\/td>\n2<\/td>\n2023-01-03<\/td>\n6<\/td>\n60.00<\/td>\n<\/tr>\n
8<\/td>\n3<\/td>\n2023-01-03<\/td>\n16<\/td>\n30.00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Desired Output:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
ProductID<\/th>\nSaleDate<\/th>\nSalesAmount<\/th>\nCenteredMovingAverage<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n2023-01-01<\/td>\n100.00<\/td>\n125.000000<\/td>\n<\/tr>\n
1<\/td>\n2023-01-02<\/td>\n150.00<\/td>\n136.666667<\/td>\n<\/tr>\n
1<\/td>\n2023-01-03<\/td>\n160.00<\/td>\n155.000000<\/td>\n<\/tr>\n
2<\/td>\n2023-01-01<\/td>\n50.00<\/td>\n60.000000<\/td>\n<\/tr>\n
2<\/td>\n2023-01-02<\/td>\n70.00<\/td>\n60.000000<\/td>\n<\/tr>\n
2<\/td>\n2023-01-03<\/td>\n60.00<\/td>\n65.000000<\/td>\n<\/tr>\n
3<\/td>\n2023-01-01<\/td>\n20.00<\/td>\n25.000000<\/td>\n<\/tr>\n
3<\/td>\n2023-01-03<\/td>\n30.00<\/td>\n25.000000<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Solve Hands-On: HERE<\/a>, Table Schema and data: Gist<\/a><\/p>\n
Show Solution<\/summary>
<\/p>\n
\r\nSELECT \r\n    ProductID,\r\n    SaleDate,\r\n    SalesAmount,\r\n    AVG(SalesAmount) OVER(PARTITION BY ProductID ORDER BY SaleDate ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS CenteredMovingAverage\r\nFROM sales;\r\n<\/pre>\n
<\/div><\/details>\n
Q23. Determine if the sales amount of each product was above or below average<\/h2>\n
Difficulty Level: Hard<\/p>\n
Task:<\/strong><\/p>\n
For each sale date, determine if the sales amount of each product was above or below its previous 3 days average.<\/p>\n
Input:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
SaleID<\/th>\nProductID<\/th>\nSaleDate<\/th>\nQuantitySold<\/th>\nSalesAmount<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n1<\/td>\n2023-01-01<\/td>\n10<\/td>\n100.00<\/td>\n<\/tr>\n
2<\/td>\n2<\/td>\n2023-01-01<\/td>\n5<\/td>\n50.00<\/td>\n<\/tr>\n
3<\/td>\n3<\/td>\n2023-01-01<\/td>\n15<\/td>\n20.00<\/td>\n<\/tr>\n
4<\/td>\n1<\/td>\n2023-01-02<\/td>\n15<\/td>\n150.00<\/td>\n<\/tr>\n
5<\/td>\n2<\/td>\n2023-01-02<\/td>\n7<\/td>\n70.00<\/td>\n<\/tr>\n
6<\/td>\n1<\/td>\n2023-01-03<\/td>\n16<\/td>\n160.00<\/td>\n<\/tr>\n
7<\/td>\n2<\/td>\n2023-01-03<\/td>\n6<\/td>\n60.00<\/td>\n<\/tr>\n
8<\/td>\n3<\/td>\n2023-01-03<\/td>\n16<\/td>\n30.00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Desired Output:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
ProductID<\/th>\nSaleDate<\/th>\nSalesAmount<\/th>\nComparisonToLast3Days<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n2023-01-01<\/td>\n100.00<\/td>\nBelow<\/td>\n<\/tr>\n
2<\/td>\n2023-01-01<\/td>\n50.00<\/td>\nBelow<\/td>\n<\/tr>\n
3<\/td>\n2023-01-01<\/td>\n20.00<\/td>\nBelow<\/td>\n<\/tr>\n
1<\/td>\n2023-01-02<\/td>\n150.00<\/td>\nAbove<\/td>\n<\/tr>\n
2<\/td>\n2023-01-02<\/td>\n70.00<\/td>\nAbove<\/td>\n<\/tr>\n
1<\/td>\n2023-01-03<\/td>\n160.00<\/td>\nAbove<\/td>\n<\/tr>\n
2<\/td>\n2023-01-03<\/td>\n60.00<\/td>\nBelow<\/td>\n<\/tr>\n
3<\/td>\n2023-01-03<\/td>\n30.00<\/td>\nAbove<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Solve Hands-On: HERE<\/a>, Table Schema and data: Gist<\/a><\/p>\n
Show Solution<\/summary>
<\/p>\n
\r\nWITH threeDayAverage AS (\r\n    SELECT \r\n        ProductID,\r\n        SaleDate,\r\n        AVG(SalesAmount) OVER(PARTITION BY ProductID ORDER BY SaleDate ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) AS avg_last_3_days\r\n    FROM sales\r\n)\r\n\r\nSELECT \r\n    S.ProductID,\r\n    S.SaleDate,\r\n    S.SalesAmount,\r\n    CASE \r\n        WHEN S.SalesAmount > A.avg_last_3_days THEN 'Above'\r\n        ELSE 'Below'\r\n    END AS ComparisonToLast3Days\r\nFROM sales S\r\nJOIN threeDayAverage A ON S.ProductID = A.ProductID AND S.SaleDate = A.SaleDate;\r\n<\/pre>\n
<\/div><\/details>\n
Q24. Calculate the cumulative sales growth rate<\/h2>\n
Difficulty Level: Hard<\/p>\n
Task:<\/strong><\/p>\n
Calculate the cumulative sales growth rate for each product. (Sales growth rate from one day to the next is (TodaysSale\u2212YesterdaysSale)\/(YesterdaysSale).<\/p>\n
Input:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
SaleID<\/th>\nProductID<\/th>\nSaleDate<\/th>\nQuantitySold<\/th>\nSalesAmount<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n1<\/td>\n2023-01-01<\/td>\n10<\/td>\n100.00<\/td>\n<\/tr>\n
2<\/td>\n2<\/td>\n2023-01-01<\/td>\n5<\/td>\n50.00<\/td>\n<\/tr>\n
3<\/td>\n3<\/td>\n2023-01-01<\/td>\n15<\/td>\n20.00<\/td>\n<\/tr>\n
4<\/td>\n1<\/td>\n2023-01-02<\/td>\n15<\/td>\n150.00<\/td>\n<\/tr>\n
5<\/td>\n2<\/td>\n2023-01-02<\/td>\n7<\/td>\n70.00<\/td>\n<\/tr>\n
6<\/td>\n1<\/td>\n2023-01-03<\/td>\n16<\/td>\n160.00<\/td>\n<\/tr>\n
7<\/td>\n2<\/td>\n2023-01-03<\/td>\n6<\/td>\n60.00<\/td>\n<\/tr>\n
8<\/td>\n3<\/td>\n2023-01-03<\/td>\n16<\/td>\n30.00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Desired Output:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
ProductID<\/th>\nSaleDate<\/th>\nSalesAmount<\/th>\ncumulative_growth_rate<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n2023-01-01<\/td>\n100.00<\/td>\n<\/td>\n<\/tr>\n
1<\/td>\n2023-01-02<\/td>\n150.00<\/td>\n0.500000<\/td>\n<\/tr>\n
1<\/td>\n2023-01-03<\/td>\n160.00<\/td>\n0.566667<\/td>\n<\/tr>\n
2<\/td>\n2023-01-01<\/td>\n50.00<\/td>\n<\/td>\n<\/tr>\n
2<\/td>\n2023-01-02<\/td>\n70.00<\/td>\n0.400000<\/td>\n<\/tr>\n
2<\/td>\n2023-01-03<\/td>\n60.00<\/td>\n0.257143<\/td>\n<\/tr>\n
3<\/td>\n2023-01-01<\/td>\n20.00<\/td>\n<\/td>\n<\/tr>\n
3<\/td>\n2023-01-03<\/td>\n30.00<\/td>\n0.500000<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Solve Hands-On: HERE<\/a>, Table Schema and data: Gist<\/a><\/p>\n
Show Solution<\/summary>
<\/p>\n
\r\nWITH GrowthRates AS (\r\n    SELECT \r\n        ProductID,\r\n        SaleDate,\r\n        SalesAmount,\r\n        (SalesAmount - LAG(SalesAmount) OVER(PARTITION BY ProductID ORDER BY SaleDate)) \/ LAG(SalesAmount) OVER(PARTITION BY ProductID ORDER BY SaleDate) AS growth_rate\r\n    FROM sales\r\n)\r\n\r\nSELECT \r\n    ProductID,\r\n    SaleDate,\r\n    SalesAmount,\r\n    SUM(growth_rate) OVER(PARTITION BY ProductID ORDER BY SaleDate) AS cumulative_growth_rate\r\nFROM GrowthRates;\r\n<\/pre>\n
<\/div><\/details>\n
Next Steps<\/h2>\n
    \n
  1. Try the SQL Window Functions Set 1<\/a> if you haven't already done.<\/li>\n
  2. To become real solid at SQL, the SQL courses (basic, intermediate and advanced) are included as part of the Machine Learning Plus - Complete Data Science University Access<\/a>. Subscribe<\/a>.<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"
    SQL window functions exercises is designed to challenge your SQL muscle and help internalize data wrangling using window functions in SQL. The questions are designed like fun puzzles, take your time and try to solve. By the end of this set, you should feel confident in solving the hardest SQL Window function problems. If you […]<\/p>\n","protected":false},"author":1,"featured_media":23999,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"site-sidebar-layout":"default","site-content-layout":"default","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[2091],"tags":[2106],"class_list":["post-23975","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql","tag-window-functions"],"acf":[],"yoast_head":"\nSQL Window Function Exercises and Solutions - Set 2 | machinelearningplus<\/title>\n<meta name=\"description\" content=\"SQL window function exercises is designed to challenge your SQL muscle and help internalize data wrangling using window functions in SQL.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/localhost:8080\/sql\/sql-window-function-exercises-and-solutions-set-2\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Window Function Exercises and Solutions - Set 2 | machinelearningplus\" \/>\n<meta property=\"og:description\" content=\"SQL window function exercises is designed to challenge your SQL muscle and help internalize data wrangling using window functions in SQL.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/localhost:8080\/sql\/sql-window-function-exercises-and-solutions-set-2\/\" \/>\n<meta property=\"og:site_name\" content=\"machinelearningplus\" \/>\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/rtipaday\/\" \/>\n<meta property=\"article:published_time\" content=\"2023-09-13T02:06:02+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-09-13T17:37:26+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/localhost:8080\/wp-content\/uploads\/2023\/09\/SQL-Window-Function-Exercises-and-Solutions-2.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1080\" \/>\n\t<meta property=\"og:image:height\" content=\"1080\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Selva Prabhakaran\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/twitter.com\/R_Programming\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Selva Prabhakaran\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"22 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"TechArticle\",\"@id\":\"https:\\\/\\\/localhost:8080\\\/sql\\\/sql-window-function-exercises-and-solutions-set-2\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/localhost:8080\\\/sql\\\/sql-window-function-exercises-and-solutions-set-2\\\/\"},\"author\":{\"name\":\"Selva Prabhakaran\",\"@id\":\"https:\\\/\\\/machinelearningplus.com\\\/#\\\/schema\\\/person\\\/510885c0515804366fa644c38258391e\"},\"headline\":\"SQL Window Function Exercises and Solutions – Set 2\",\"datePublished\":\"2023-09-13T02:06:02+00:00\",\"dateModified\":\"2023-09-13T17:37:26+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/localhost:8080\\\/sql\\\/sql-window-function-exercises-and-solutions-set-2\\\/\"},\"wordCount\":2143,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/machinelearningplus.com\\\/#organization\"},\"image\":{\"@id\":\"https:\\\/\\\/localhost:8080\\\/sql\\\/sql-window-function-exercises-and-solutions-set-2\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/machinelearningplus.com\\\/wp-content\\\/uploads\\\/2023\\\/09\\\/SQL-Window-Function-Exercises-and-Solutions-2.png\",\"keywords\":[\"Window Functions\"],\"articleSection\":[\"SQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/localhost:8080\\\/sql\\\/sql-window-function-exercises-and-solutions-set-2\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/localhost:8080\\\/sql\\\/sql-window-function-exercises-and-solutions-set-2\\\/\",\"url\":\"https:\\\/\\\/localhost:8080\\\/sql\\\/sql-window-function-exercises-and-solutions-set-2\\\/\",\"name\":\"SQL Window Function Exercises and Solutions - Set 2 | machinelearningplus\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/machinelearningplus.com\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/localhost:8080\\\/sql\\\/sql-window-function-exercises-and-solutions-set-2\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/localhost:8080\\\/sql\\\/sql-window-function-exercises-and-solutions-set-2\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/machinelearningplus.com\\\/wp-content\\\/uploads\\\/2023\\\/09\\\/SQL-Window-Function-Exercises-and-Solutions-2.png\",\"datePublished\":\"2023-09-13T02:06:02+00:00\",\"dateModified\":\"2023-09-13T17:37:26+00:00\",\"description\":\"SQL window function exercises is designed to challenge your SQL muscle and help internalize data wrangling using window functions in SQL.\",\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/localhost:8080\\\/sql\\\/sql-window-function-exercises-and-solutions-set-2\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/localhost:8080\\\/sql\\\/sql-window-function-exercises-and-solutions-set-2\\\/#primaryimage\",\"url\":\"https:\\\/\\\/machinelearningplus.com\\\/wp-content\\\/uploads\\\/2023\\\/09\\\/SQL-Window-Function-Exercises-and-Solutions-2.png\",\"contentUrl\":\"https:\\\/\\\/machinelearningplus.com\\\/wp-content\\\/uploads\\\/2023\\\/09\\\/SQL-Window-Function-Exercises-and-Solutions-2.png\",\"width\":1080,\"height\":1080,\"caption\":\"SQL Window Function Exercises and Solutions 2\"},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/machinelearningplus.com\\\/#website\",\"url\":\"https:\\\/\\\/machinelearningplus.com\\\/\",\"name\":\"machinelearningplus\",\"description\":\"Learn Data Science (AI \\\/ ML) Online\",\"publisher\":{\"@id\":\"https:\\\/\\\/machinelearningplus.com\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/machinelearningplus.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/machinelearningplus.com\\\/#organization\",\"name\":\"machinelearningplus\",\"url\":\"https:\\\/\\\/machinelearningplus.com\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/machinelearningplus.com\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/machinelearningplus.com\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/MachineLearningplus-logo.svg\",\"contentUrl\":\"https:\\\/\\\/machinelearningplus.com\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/MachineLearningplus-logo.svg\",\"width\":348,\"height\":36,\"caption\":\"machinelearningplus\"},\"image\":{\"@id\":\"https:\\\/\\\/machinelearningplus.com\\\/#\\\/schema\\\/logo\\\/image\\\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/machinelearningplus.com\\\/#\\\/schema\\\/person\\\/510885c0515804366fa644c38258391e\",\"name\":\"Selva Prabhakaran\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/machinelearningplus.com\\\/wp-content\\\/litespeed\\\/avatar\\\/a994280177da541405c016f593e86ea7.jpg?ver=1783622267\",\"url\":\"https:\\\/\\\/machinelearningplus.com\\\/wp-content\\\/litespeed\\\/avatar\\\/a994280177da541405c016f593e86ea7.jpg?ver=1783622267\",\"contentUrl\":\"https:\\\/\\\/machinelearningplus.com\\\/wp-content\\\/litespeed\\\/avatar\\\/a994280177da541405c016f593e86ea7.jpg?ver=1783622267\",\"caption\":\"Selva Prabhakaran\"},\"description\":\"Selva is an experienced Data Scientist and leader, specializing in executing AI projects for large companies. Selva started machinelearningplus to make Data Science \\\/ ML \\\/ AI accessible to everyone. The website enjoys 4 Million+ readership. His courses, lessons, and videos are loved by hundreds of thousands of students and practitioners.\",\"sameAs\":[\"https:\\\/\\\/localhost:8080\\\/\",\"https:\\\/\\\/www.facebook.com\\\/rtipaday\\\/\",\"https:\\\/\\\/x.com\\\/https:\\\/\\\/twitter.com\\\/R_Programming\"],\"url\":\"https:\\\/\\\/machinelearningplus.com\\\/author\\\/selva86\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Window Function Exercises and Solutions - Set 2 | machinelearningplus","description":"SQL window function exercises is designed to challenge your SQL muscle and help internalize data wrangling using window functions in SQL.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/localhost:8080\/sql\/sql-window-function-exercises-and-solutions-set-2\/","og_locale":"en_US","og_type":"article","og_title":"SQL Window Function Exercises and Solutions - Set 2 | machinelearningplus","og_description":"SQL window function exercises is designed to challenge your SQL muscle and help internalize data wrangling using window functions in SQL.","og_url":"https:\/\/localhost:8080\/sql\/sql-window-function-exercises-and-solutions-set-2\/","og_site_name":"machinelearningplus","article_author":"https:\/\/www.facebook.com\/rtipaday\/","article_published_time":"2023-09-13T02:06:02+00:00","article_modified_time":"2023-09-13T17:37:26+00:00","og_image":[{"width":1080,"height":1080,"url":"https:\/\/localhost:8080\/wp-content\/uploads\/2023\/09\/SQL-Window-Function-Exercises-and-Solutions-2.png","type":"image\/png"}],"author":"Selva Prabhakaran","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/R_Programming","twitter_misc":{"Written by":"Selva Prabhakaran","Est. reading time":"22 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"TechArticle","@id":"https:\/\/localhost:8080\/sql\/sql-window-function-exercises-and-solutions-set-2\/#article","isPartOf":{"@id":"https:\/\/localhost:8080\/sql\/sql-window-function-exercises-and-solutions-set-2\/"},"author":{"name":"Selva Prabhakaran","@id":"https:\/\/machinelearningplus.com\/#\/schema\/person\/510885c0515804366fa644c38258391e"},"headline":"SQL Window Function Exercises and Solutions – Set 2","datePublished":"2023-09-13T02:06:02+00:00","dateModified":"2023-09-13T17:37:26+00:00","mainEntityOfPage":{"@id":"https:\/\/localhost:8080\/sql\/sql-window-function-exercises-and-solutions-set-2\/"},"wordCount":2143,"commentCount":0,"publisher":{"@id":"https:\/\/machinelearningplus.com\/#organization"},"image":{"@id":"https:\/\/localhost:8080\/sql\/sql-window-function-exercises-and-solutions-set-2\/#primaryimage"},"thumbnailUrl":"https:\/\/machinelearningplus.com\/wp-content\/uploads\/2023\/09\/SQL-Window-Function-Exercises-and-Solutions-2.png","keywords":["Window Functions"],"articleSection":["SQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/localhost:8080\/sql\/sql-window-function-exercises-and-solutions-set-2\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/localhost:8080\/sql\/sql-window-function-exercises-and-solutions-set-2\/","url":"https:\/\/localhost:8080\/sql\/sql-window-function-exercises-and-solutions-set-2\/","name":"SQL Window Function Exercises and Solutions - Set 2 | machinelearningplus","isPartOf":{"@id":"https:\/\/machinelearningplus.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/localhost:8080\/sql\/sql-window-function-exercises-and-solutions-set-2\/#primaryimage"},"image":{"@id":"https:\/\/localhost:8080\/sql\/sql-window-function-exercises-and-solutions-set-2\/#primaryimage"},"thumbnailUrl":"https:\/\/machinelearningplus.com\/wp-content\/uploads\/2023\/09\/SQL-Window-Function-Exercises-and-Solutions-2.png","datePublished":"2023-09-13T02:06:02+00:00","dateModified":"2023-09-13T17:37:26+00:00","description":"SQL window function exercises is designed to challenge your SQL muscle and help internalize data wrangling using window functions in SQL.","inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/localhost:8080\/sql\/sql-window-function-exercises-and-solutions-set-2\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/localhost:8080\/sql\/sql-window-function-exercises-and-solutions-set-2\/#primaryimage","url":"https:\/\/machinelearningplus.com\/wp-content\/uploads\/2023\/09\/SQL-Window-Function-Exercises-and-Solutions-2.png","contentUrl":"https:\/\/machinelearningplus.com\/wp-content\/uploads\/2023\/09\/SQL-Window-Function-Exercises-and-Solutions-2.png","width":1080,"height":1080,"caption":"SQL Window Function Exercises and Solutions 2"},{"@type":"WebSite","@id":"https:\/\/machinelearningplus.com\/#website","url":"https:\/\/machinelearningplus.com\/","name":"machinelearningplus","description":"Learn Data Science (AI \/ ML) Online","publisher":{"@id":"https:\/\/machinelearningplus.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/machinelearningplus.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/machinelearningplus.com\/#organization","name":"machinelearningplus","url":"https:\/\/machinelearningplus.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/machinelearningplus.com\/#\/schema\/logo\/image\/","url":"https:\/\/machinelearningplus.com\/wp-content\/uploads\/2022\/05\/MachineLearningplus-logo.svg","contentUrl":"https:\/\/machinelearningplus.com\/wp-content\/uploads\/2022\/05\/MachineLearningplus-logo.svg","width":348,"height":36,"caption":"machinelearningplus"},"image":{"@id":"https:\/\/machinelearningplus.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/machinelearningplus.com\/#\/schema\/person\/510885c0515804366fa644c38258391e","name":"Selva Prabhakaran","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/machinelearningplus.com\/wp-content\/litespeed\/avatar\/a994280177da541405c016f593e86ea7.jpg?ver=1783622267","url":"https:\/\/machinelearningplus.com\/wp-content\/litespeed\/avatar\/a994280177da541405c016f593e86ea7.jpg?ver=1783622267","contentUrl":"https:\/\/machinelearningplus.com\/wp-content\/litespeed\/avatar\/a994280177da541405c016f593e86ea7.jpg?ver=1783622267","caption":"Selva Prabhakaran"},"description":"Selva is an experienced Data Scientist and leader, specializing in executing AI projects for large companies. Selva started machinelearningplus to make Data Science \/ ML \/ AI accessible to everyone. The website enjoys 4 Million+ readership. His courses, lessons, and videos are loved by hundreds of thousands of students and practitioners.","sameAs":["https:\/\/localhost:8080\/","https:\/\/www.facebook.com\/rtipaday\/","https:\/\/x.com\/https:\/\/twitter.com\/R_Programming"],"url":"https:\/\/machinelearningplus.com\/author\/selva86\/"}]}},"_links":{"self":[{"href":"https:\/\/machinelearningplus.com\/wp-json\/wp\/v2\/posts\/23975","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/machinelearningplus.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/machinelearningplus.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/machinelearningplus.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/machinelearningplus.com\/wp-json\/wp\/v2\/comments?post=23975"}],"version-history":[{"count":0,"href":"https:\/\/machinelearningplus.com\/wp-json\/wp\/v2\/posts\/23975\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/machinelearningplus.com\/wp-json\/wp\/v2\/media\/23999"}],"wp:attachment":[{"href":"https:\/\/machinelearningplus.com\/wp-json\/wp\/v2\/media?parent=23975"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/machinelearningplus.com\/wp-json\/wp\/v2\/categories?post=23975"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/machinelearningplus.com\/wp-json\/wp\/v2\/tags?post=23975"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}