7 of 7
Paper No: 7
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
-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 - Avg Total OrderValue2023)/
Avg Total OrderValue2023*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 Avg TotalOrderValue2024 PercentageChange
CustomerID
FullName
1
2
Trần Văn Tâm
2
12
Đỗ Đức Thịnh
3
13
Võ Thị Hoa
17
Lê Thị Diễm
530000.00
475000.00
1820000.00
1070500.00
43
45
Lê Thị Tâm
Nguyễn Lan Hương 1705000.00
347000.00
48
Võ Đức Tài
525000.00
Picture 7.1
340000.00
-35.85 %
525000.00
10.53%
446000.00
-75.49 %
235000.00
-78.05 %
680000.00
-60.12 %
1102000.00
255000.00
217.58%
-51.43 %
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
Caravamale when we avenuto the Monthly Colon Cummon procedure by using the following
+ 100%
Close
