Question paper (build 07.05.20.19)
6 of 6
Paper No: 10
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 TotalOrderValue2023)/
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 Avg Total OrderValue2024 PercentageChange
CustomerID
1
2
FullName
Trần Văn Tâm
530000.00
340000.00
2
12
Đỗ Đức Thịnh
475000.00
525000.00
-35.85%
10.53%
3
13
Võ Thị Hoa
1820000.00
446000.00
-75.49 %
4
17
Lê Thị Diễm
1070500.00
235000.00
-78.05 %
567
43
Nguyễn Lan Hương
1705000.00
680000.00
45
Lê Thị Tâm
347000.00
1102000.00
48
Võ Đức Tài
525000.00
255000.00
-60.12%
217.58 %
-51.43%
Picture 7.1
