magnitude_analysis.sql

-- MAGNITUDE ANALYSIS
-- to understand importance of categories
print('comparing ∑ [MEASURES][DIMENSION] ') 
print('eg. TOTAL SALES by COUNTRY , AVG PRICE by PRODUCT')

-- Q.1 Find TOTAL CUSTOMERS by COUNTRY
SELECT country, COUNT(customer_key) as total_customer_in_country 
FROM gold.dim_customers
GROUP BY country 
ORDER BY total_customer_in_country DESC;

-- Q.2 find TOTAL CUSTOMERS by GENDER
SELECT gender, COUNT(customer_id) as genderwise_customer
FROM gold.dim_customers
GROUP BY gender
ORDER BY genderwise_customer DESC;

-- Q.3 find TOTAL PRODUCTS by CATEGORY
SELECT category, COUNT(product_id) as categorywise_products
FROM gold.dim_products
GROUP BY category
ORDER BY categorywise_products DESC;

-- Q.4 find AVG costs by CATEGORY
SELECT category , AVG(product_cost) as AVG_cost
FROM gold.dim_products
GROUP BY category
ORDER BY AVG_cost DESC;

-- Q.5 what is TOTAL REVENUE for each CATEGORY
SELECT p.category, SUM(f.sales) as Total_Category_Revenue 
FROM gold.fact_sales as f
LEFT JOIN gold.dim_products as p
ON p.product_key =  f.product_key
GROUP BY p.category
ORDER BY Total_Category_Revenue DESC;

-- Q.6 what is TOTAL REVENUE for each CUSTOMER
SELECT c.customer_id, c.first_name, c.last_name,
SUM(f.sales) as Total_Customer_Revenue
FROM gold.fact_sales as f
LEFT JOIN gold.dim_customers as c
ON c.customer_key = f.customer_key
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY Total_Customer_Revenue DESC;

-- Q.7 what is the DISTRIBUTION of SOLD ITEMS ACROSS COUNTRIES
SELECT c.country, SUM(f.quantity) as Countrywise_Distribution
FROM gold.fact_sales as f
LEFT JOIN gold.dim_customers as c
ON c.customer_key = f.customer_key
GROUP BY c.country
ORDER BY Countrywise_Distribution DESC;

PRINT('LOW CARDINILITY')
-- Dimension wid low Unique Values : Country , Gender, Category
PRINT('HIGH CARDINALITY')
-- Dimension wid high Unique Values : Product, Customer, Address