Project Data Analyst for Retail: Sales Performance Report by DQLab

Mentor: Nelda Ampulembang Parenta — Senior Data Analyst at Logisly

SELECT 
YEAR(order_date) AS years,
SUM(sales) AS sales,
COUNT(order_quantity) AS number_of_order
FROM dqlab_sales_store
WHERE order_status = 'Order Finished'
GROUP BY YEAR(order_date);
Figure 1. Output Results for Overall Performance by Year
SELECT 
YEAR(order_date) AS years,
product_sub_category,
SUM(sales) AS sales
FROM dqlab_sales_store
WHERE order_status = ‘Order Finished’
AND YEAR(order_date) > 2010
GROUP BY years, product_sub_category
ORDER BY years, sales DESC;
Figure 2. Output Results for Overall Performance by Product Subcategory in 2011–2012
SELECT 
YEAR(order_date) AS years,
SUM(sales) as sales,
SUM(discount_value) AS promotion_value,
ROUND((SUM(discount_value)/SUM(sales))*100,2) AS burn_rate_percentage
FROM dqlab_sales_store
WHERE order_status = ‘Order Finished’
GROUP BY years;
Figure 3. Output Results for Promotion Effectiveness and Efficiency by Year
SELECT 
YEAR(order_date) as years,
product_sub_category,
product_category,
SUM(sales) AS sales,
SUM(discount_value) AS promotion_value,
ROUND((SUM(discount_value)/SUM(sales))*100,2) AS burn_rate_percentage
FROM dqlab_sales_store
WHERE order_status = 'Order Finished'
AND YEAR(order_date) = 2012
GROUP BY
years,
product_sub_category,
product_category
ORDER by sales DESC;
Figure 4. Output Results for Promotion Effectiveness and Efficiency by Product Subcategories in 2012
SELECT 
YEAR(order_date) AS years,
COUNT(DISTINCT customer) AS number_of_customer
FROM dqlab_sales_store
WHERE order_status = ‘Order Finished’
GROUP BY years
ORDER BY years;
Figure 5. Output Results for Consumers Who Make Transactions by Year
SELECT YEAR(date_first_transaction) AS years,
COUNT(DISTINCT customer) AS number_of_new_customer
FROM (
SELECT customer,
MIN(order_date) AS date_first_transaction
FROM dqlab_sales_store
WHERE order_status = ‘Order Finished’
GROUP BY customer) AS table_a
GROUP BY years
ORDER BY years;
Figure 6. Output Results New Consumers Who Make Transactions by Year
Table 1. Table of Sales Growth by Product Subcategories

Be happy

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store