- Overview
- Problem Statement by Marketing Manageer
* SQL
* Power BI - Problem Statement by SupplyChain Manageer
* SQL
* Power BI - Problem Statement by Finanace Manageer
* SQL
* Power BI - Power BI Dashboard
- The DataBase has details of the sales transaction of the SuperMart.
- All the details is distributed across these tables Customer, Product and Sales.
- Customer table has all the relevant information about customer and similary product table is aout product details.
- Slaes table has all the transaction details and it is linked with customer and product tables with the Foreign keys.
- With the help of Analytical tools, Solve problem statements and help the stakeholders involved in the business to make strategic decisions backed by the Data.
The Data Model of the SuperMart Database is as follows:
Problem Statement: Lisa Wants to plan new marketing campaign. She has two modes of Campaign:
- Social Media for Younger people
- Print advertisemetns in News paper for Older people.
She needs data about the customers who belong to three age categories in all four regions:
- Less than 36 years.
- Between 36 to 54 years.
- Above 54 years.
SQL Query to fetch the required Data
Select
Region,
case
when age < 36 then 'Young'
when age between 36 and 54 then 'Middle'
else 'old'
end as age_category,
count(customer_id)
from
customer
group by
Region, age_category
order by
region asc, age_category desc
Problem Statement: Sam facing Issues in Managing inventory in South(Over utilised) and East regions(Under Utilised). He needsd information about these products:
- Top 5 selling Products in the East Region.
- Least 5 selling Products in the South Region.
SQL Query to fetch the required Data
-- Least 5 Selling products from south Region
select
p.product_name, sum(s.quantity) quantity
from
sales s inner join product p
on p.product_id = s.product_id
inner join customer c
on c.customer_id = s.customer_id
where
c.region = 'South'
group by
p.product_name
order by
quantity asc, p.product_name asc
limit 5
-- Top 5 Selling products from East Region
select
p.product_name, sum(s.quantity) quantity
from
sales s inner join product p
on p.product_id = s.product_id
inner join customer c
on c.customer_id = s.customer_id
where
c.region = 'East'
group by
p.product_name
order by
quantity desc, p.product_name asc
limit 5
Problem Statement: To Manage the revenues, he needs more detail about the Discounts given for the products.
- Total Revenue loss due to Discounts.
- Total Revenue and Discount for Each product.
SQL Query to fetch the required data
select
sum(discount*sales) as total_discount
from
sales;
select
product_id, sum(discount*sales) as Discount,
(sum(sales)- sum(discount*sales)) as Revenue,
sum(discount*sales)/(sum(sales)- sum(discount*sales)) as ratio
from
sales
group by
product_id
order by
ratio desc
SQL, ETL, Python, Power BI...
