File dataset (download)

train.csv

1. Tổng doanh thu theo năm

SELECT 
    YEAR(Ship_Date) AS Year,  
    SUM(Sales) AS total_revenue  
FROM dbo.train  
GROUP BY YEAR(Ship_Date)
ORDER BY Year;

→ run sql:

Year total_revenue 2015 208421717 2016 247353633 2017 305460575 2018 350519106 2019 2577640

1. Tổng doanh thu theo quý

select 
	YEAR(Ship_Date) as Year,
	DATEPART(Quarter, Ship_Date) as Quarter,
	SUM(Sales) as total_revenue
from train
Group by YEAR(Ship_Date), DATEPART(Quarter, Ship_Date)
Order by Year, Quarter;

→ run sql:

Year Quarter total_revenue 2015 1 20161216 2015 2 37594693 2015 3 69510824 2015 4 81154984 2016 1 53973686 2016 2 49648797 2016 3 52526330 2016 4 91204820 2017 1 34107249 2017 2 61631125 2017 3 93930752 2017 4 115791449 2018 1 42599357 2018 2 83274486 2018 3 97099791 2018 4 127545472 2019 1 2577640

3. Tổng doanh thu theo tháng

select 
	YEAR(Ship_Date) as year, 
	MONTH(Ship_Date) as month,
	SUM(Sales) as total_revenue
from train
group by YEAR(Ship_Date), MONTH(Ship_Date)
order by year, month;

→ run sql:

year month total_revenue 2015 1 2554454 2015 2 1052643 2015 3 16554119 2015 4 11040548 2015 5 7799604 2015 6 18754541 2015 7 16720145 2015 8 13474953 2015 9 39315726 ….

4. Top 10 sản phẩm bán chạy nhất

select top 10
	Product_Name,
	SUM(Sales) as total_revenue
from train
group by Product_Name
order by total_Revenue

→ run sql:

Product_Name total_revenue Sanitaire Vibra Groomer IR Commercial Upright Vacuum, Replacement Belts 13 Sanford Pocket Accent Highlighters 40 Avery Hi-Liter Pen Style Six-Color Fluorescent Set 77 Avery Hi-Liter Comfort Grip Fluorescent Highlighter, Yellow Ink 78 Sanford Uni-Blazer View Highlighters, Chisel Tip, Yellow 110 Dell Slim USB Multimedia Keyboard 125 Samsung Replacement EH64AVFWE Premium Headset 154 Acco Glide Clips 196 Advantus Plastic Paper Clips 225 Memorex Mini Travel Drive 32 GB USB 2.0 Flash Drive 280

5. Khách hàng có giá trị cao nhất (High-Value Customers)

*Top 10 khách hàng có tổng chi tiêu cao nhất