Basic Comparison - Sales Analysis

basic

Compare GROUP BY vs PARTITION BY for the same business question

-- Business Question: Analyze sales performance by region

-- APPROACH 1: GROUP BY (Summary Only)

SELECT

region,

COUNT(*) as total_sales,

SUM(sale_amount) as total_revenue,

AVG(sale_amount) as avg_sale_amount,

MAX(sale_amount) as largest_sale

FROM sales

GROUP BY region

ORDER BY total_revenue DESC;

-- Result: 4 summary rows (one per region)

-- Perfect for: Executive dashboard, regional performance report

-- APPROACH 2: PARTITION BY (Detail + Context)

SELECT

sale_id,

salesperson,

region,

sale_amount,

sale_date,

-- Regional context for each sale

COUNT(*) OVER (PARTITION BY region) as region_total_sales,

SUM(sale_amount) OVER (PARTITION BY region) as region_total_revenue,

AVG(sale_amount) OVER (PARTITION BY region) as region_avg_sale,

-- Individual performance vs region

sale_amount - AVG(sale_amount) OVER (PARTITION BY region) as vs_region_avg,

RANK() OVER (PARTITION BY region ORDER BY sale_amount DESC) as region_rank,

-- Performance percentile

PERCENT_RANK() OVER (PARTITION BY region ORDER BY sale_amount) as region_percentile

FROM sales

ORDER BY region, sale_amount DESC;

-- Result: All original sales rows with regional context

-- Perfect for: Individual performance analysis, sales coaching

-- GROUP BY Results (4 summary rows):

region | total_sales | total_revenue | avg_sale_amount | largest_sale

North | 150 | 750000 | 5000 | 25000

South | 120 | 600000 | 5000 | 20000