01.Exploration.sql

-- Database exploration
select * from INFORMATION_SCHEMA.TABLES
select * from INFORMATION_SCHEMA.COLUMNS
-- Columns exploration
where TABLE_NAME = 'dim_customers'
select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'dim_products'
select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'fact_sales'
--Dimension exploration
select DISTINCT country
from dim_customers

select DISTINCT category, subcategory
from dim_products

-- Date exploration
--Timespan in dataset
Select MIN(order_date) as First_order_date,
MAX(Order_date) as last_order_date,
DATEDIFF(YEAR, MIN(order_date), MAX(Order_date)) AS No_of_years
from fact_sales

--Youngest and oldest customer 
select MIN(birthdate) AS oldest_customer,
DATEDIFF (year, MIN(birthdate), getdate()) AS OLDEST_AGE,
MAX(birthdate) AS YOUNGEST_AGE,
DATEDIFF (year, MAX(birthdate), getdate()) AS YOUNGEST_AGE
from dim_customers

--Measure exploration
--Find the total sales
select SUM(Sales_amount) AS Total_sales 
from fact_sales

--How many items are sold?
select SUM(quantity) AS Total_quantity
from fact_sales

--Find the average selling price
Select AVG(price) AS AVG_price
from fact_sales

--Find the total number of orders
select count(order_number) AS Total_orders
from fact_sales
select count(DISTINCT order_number) AS Total_orders
from fact_sales

--Find the total number of customers
select count(DISTINCT customer_id) AS Total_customers
from dim_customers

--Find the total number of products
Select count(DISTINCT product_id) AS Total_products
from dim_products

--Find the total number of customers that have placed an order
Select COUNT(customer_id)
from dim_customers

--Generate a report that shows all key metrics of the business
select 'TOTAL_SALES' AS Measure_name,SUM(Sales_amount) AS Measure_value from fact_sales
UNION ALL
select 'TOTAL_QUANTITY' AS Measure_name,SUM(quantity) AS Measure_value from fact_sales
UNION ALL
Select 'AVERAGE_PRICE' AS measure_name, AVG(price) AS Measure_value from fact_sales
UNION ALL
select 'Total_orders' AS measure_name,count(DISTINCT order_number) AS measure_value from fact_sales
UNION ALL
select 'total_customers' as measure_nmae, count(DISTINCT customer_id) AS MEASURE_VALUE from dim_customers
UNION ALL
Select 'Total_products' as measure_name,count(DISTINCT product_id) AS measure_value from dim_products

--MAGNITUDE ANALYSIS
--Find total customers by countries
select COUNTRY,
COUNT(DISTINCT customer_id) AS Total_customers
from dim_customers
group by Country
ORDER BY TOTAL_CUSTOMERS DESC

--Find the total customers by gender
Select Gender,
COUNT(DISTINCT customer_id) AS Total_customers
from dim_customers
group by gender
ORDER BY TOTAL_CUSTOMERS DESC

--Find the total_products by category
select Category, 
count(product_id) AS TOTAL_Products
from dim_products
group by category
order by Total_products DESC

--Find the average cost in each category
Select category,
avg(cost) AS Average_cost
from dim_products
group by category
order by Average_cost DESC

--Find the total revenue generated in each category
Select p.category,
SUM(s.sales_amount) AS Total_revenue
from fact_sales as s
left join dim_products as p
on p.product_key = s.product_key
group by p.category
order by Total_revenue DESC


--find the total revenue generated by each customer
Select DISTINCT p.customer_key,
P.FIRST_NAME,
P.LAST_NAME,
SUM(s.sales_amount) AS Total_revenue
from fact_sales as s
left join dim_customers as p
on p.customer_key = s.customer_key
group by p.customer_key,
P.FIRST_NAME,
P.LAST_NAME
order by Total_revenue DESC

--Find the total distribution of sold items across countries
select c.country,
Count(s.quantity) AS Total_Quantity
from fact_sales as s
left join dim_customers as c
on s.Customer_key = c.customer_key
group by c.country
order by Total_Quantity DESC

--Ranking Analysis
--What 5 products generate the highest revenue?
select TOP 5
P.product_name,
SUM(S.sales_amount) as Highest_revenue
from dim_products as p
left join fact_sales as s
on P.product_key = s.product_key
group by p.product_name
ORDER BY Highest_revenue desc

--What are the 5 worst performing products in terms of sales?
select TOP 5
P.product_name,
SUM(S.sales_amount) as Highest_revenue
from fact_sales as s
left join dim_products as p
on P.product_key = s.product_key
group by p.product_name
ORDER BY Highest_revenue