SQLQuery-1. Analyze the yearly performance of products by comparing their sales to both the average sales performance of the product and the previous year's sales.
SQLQuery-2. Segment products into cost ranges and count how many products fall into each segment.
SQLQuery-3. Which Categories contribute the most to overall sales?
SQLQuery-4. Group customers into three segments based on their spending behaviours:
-VIP : Customers with at least 12months of history and spending more than 5000ドル.
-REGULAR : Customers with at least 12 months of history but spending 5000$ or less
-NEW : Customers with a lifespan less than 12 months.
and find the total numbers od customers by each group
SQLQuery-5. CUSTOMER REPORT
Purpose: This report consolidates key customer metrics and behaviours.
#Highlights:
1. Gathers essential fields such as names,ages,and transaction details.
2. Segments customers into categories (VIP,Regular,New) and age groups.
3.Aggregates customer-level metrics:
-total orders
-total sales
-total quantity purchased
-total products
-lifespan (in months)
4.Calculates valueable KPIs:
- recency(months since last order)
-average order value
-average monthly spend
SQLQuery-6. PRODUCT REPORT
Purpose: This report consolidates key product metrics and behaviours.
#Highlights:
1. Gathers essential fields such as product name,category,subcategory and cost.
2. Segments products by revenue to identify High-Performers,Midd-Range,or Low-Range
3.Aggregates product-level metrics:
-total orders
-total sales
-total quantity sold
-total customers (unique)
-lifespan (in months)
4.Calculates valueable KPIs:
- recency(months sincelast sale)
-average order revenue (AOR)
-average monthly revenue