Picture 6.1
Question 7:
Write an SQL query to analyze customer spending trends between 2023 and 2024 based on
completed orders. For each customer who placed completed orders in both 2023 and 2024,
calculate and display
- CustomerID
- FullName
6 of 6
Paper No: 5
-Avg TotalOrderValue2023- the average total order value of the customer's completed
orders in 2023, rounded to two decimal places.
Avg TotalOrderValue2024 - the average total order value of the customer's completed
orders in 2024, rounded to two decimal places.
-PercentageChange - the percentage growth in the average total order value from 2023 to
2024. Il is calculated as (Avg TotalOrderValue2024 - AvgTotalOrderValue2023/
Avg TotalOrderValue2023*100. Round the percentage change to two decimal places, and
append % at the end.
Order the result in ascending order of CustomerID as shown in the following figure.
Avg TotalOrderValue2023 AvgTotalOrderValue2024 PercentageChange
CustomerID FullName
1
2
Trần Văn Tâm
530000.00
340000.00
-35.85%
2
12
Đỗ Đức Thịnh
475000.00
525000.00
10.53%
3
13
Vô Thị Hoàn
1820000.00
446000.00
-75.49%
4
17
Lê Thị Diễm
1070500.00
235000.00
-78.05 %
5
43
Nguyễn Lan Hương
1706000.09.
680000.00
00.12%
6
45
Le Thi Tam
347000.00
7
48
MỘ ĐỨC TẠI
525000.00
1102000.00
255000.00
217.58%
-51.43%
Picture 7.1
Question 8:
Write a stored procedure named MonthlySalesSummary that generates a monthly sales
report for a specified year. Your procedure must accept one input parameter: @year int - the
year for which the report will be generated. The procedure must display all 12 months (from
January to December), even if there are no orders in a given month. For each month,
calculate and display:
-Month in the format Month/Year
-NumberOfOrders - the total count of completed orders in that month
- TotalRevenue - the total revenue (sum of Quantity Price) from all completed orders in that
month
Zoom
+ 62%
Close
