Kwizera Eric Gasana

--Questions --a) What is the total number of units sold per product SKU? select productid,sum(inventoryquantity) as Total_Units_Sold from sales group by productid order by Total_Units_Sold desc; --Key Insight --The highest selling product is 9806. --b) Which product category had the highest sales volume last month? select p.productcategory,sum(s.inventoryquantity) as Sales_Volume from sales s join product p on s.productid = p.productid where s.sales_year = '2021' and s.sales_month = '11' group by p.productcategory order by Sales_Volume desc limit 1; --Key Insight --Electronics was the highest category with a sales volume of 678 units --c) How does the inflation rate correlate with sales volume for a specific month? select s.sales_month,s.sales_year,round(avg(f.inflationrate),2) as Average_Inflation ,sum(s.inventoryquantity) as Sales_Volume from sales s join factors f on s.salesdate = s.salesdate group by s.sales_month,s.sales_year order by s.sales_month,s.sales_year; --Key Insight --The average inflation has an positive impact on sales volume. --d) What is the correlation between the inflation rate and sales quantity --for all products combined on a monthly basis over the last year? select s.sales_year,s.sales_month,round(avg(f.inflationrate),1) as Average_Inflation, sum(s.inventoryquantity) as Total_Sales_Quantity from sales s join factors f on s.salesdate = f.salesdate where s.salesdate >= (current_date - interval '1 year') group by s.sales_year,s.sales_month order by s.sales_year,s.sales_month; --Key Insight --There is no correlation between average inflation and sales quantity. --e) Did promotions significantly impact the sales quantity of products? select p.productcategory,round(avg(inventoryquantity),0) as Average_Sales_Without_Promotions ,promotions from sales s join product p on p.productid = s.productid where p.promotions = 'No' group by p.productcategory,p.promotions union all select p.productcategory,round(avg(inventoryquantity),0) as Average_Sales_Without_Promotions ,promotions from sales s join product p on p.productid = s.productid where p.promotions = 'Yes' group by p.productcategory,p.promotions; --Key Insights --Only home appliances appliances sales benefit from promitions. --The rest of the products do not benefit from promotions --f) What is the average sales quantity per product category? select p.productcategory,round(avg(inventoryquantity),0) as Average_Sales from sales s join product p on p.productid = s.productid group by productcategory order by Average_Sales desc; --Key Insight --Electronics have the highest average quantity sold while laptops have --the lowest sales quantity --g) How does the GDP affect the total sales volume? select s.sales_year,round(sum(gdp),0) as Total_GDP,round(sum(inventoryquantity),0) as Total_Sales from sales s join factors f on f.salesdate = s.salesdate group by s.sales_year order by Total_Sales desc; --Key Insight --The sales fluctuate even with change in GDP. --There is no direct relation between GDP and total sales. positive correlation --h) What are the top 10 best-selling product SKUs? select productid,sum(inventoryquantity) as Total_Sales from sales group by productid order by Total_Sales desc Limit 10; --Key Insight --The highest product is 9806 --i) How do seasonal factors influence sales quantities for different --product categories? select p.productcategory,round(avg(seasonalfactor),4) as Average_Seasonal_Factor ,round(sum(inventoryquantity),0) as Total_Sales from sales s join product p on p.productid = s.productid join factors f on f.salesdate = s.salesdate group by productcategory order by Average_Seasonal_Factor; --Key Insight --There is no straight forward correlation between seasonal factor and total sales --j) What is the average sales quantity per product category, --and how many products within each category were part of a promotion? select p.productcategory,round(avg(inventoryquantity),0) as Average_Sales, count(case when p.promotions = 'Yes' then 1 end) as Promtion_Count from sales s join product p on p.productid = s.productid group by p.productcategory order by Average_Sales; --Key Insight --Promotion do not increase sales --Overall Recommedation --Focus on promoting the highest selling product which is electronics.