Northwind Trading Company

-- QUERY 1: Customer Segmentation Analysis
WITH customer_metrics AS (
  SELECT 
    c.CustomerID,
    c.CompanyName,
    c.Country,
    c.City,
    COUNT(DISTINCT o.OrderID) as total_orders,
    ROUND(SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)), 2) as lifetime_revenue,
    ROUND(AVG(od.UnitPrice * od.Quantity * (1 - od.Discount)), 2) as avg_order_value,
    MIN(o.OrderDate) as first_order_date,
    MAX(o.OrderDate) as last_order_date,
    ROUND(JULIANDAY(MAX(o.OrderDate)) - JULIANDAY(MIN(o.OrderDate))) as customer_lifetime_days,
    SUM(od.Quantity) as total_units_purchased,
    ROUND(AVG(od.Discount) * 100, 2) as avg_discount_percent
  FROM Customers c
  JOIN Orders o ON c.CustomerID = o.CustomerID
  JOIN OrderDetails od ON o.OrderID = od.OrderID
  GROUP BY c.CustomerID, c.CompanyName, c.Country, c.City
),
ranked_customers AS (
  SELECT *,
    ROW_NUMBER() OVER (ORDER BY lifetime_revenue DESC) as revenue_rank,
    COUNT(*) OVER () as total_customers
  FROM customer_metrics
),
customer_segments AS (
  SELECT *,
    CASE 
      WHEN revenue_rank <= CAST(total_customers * 0.15 AS INTEGER) THEN 'VIP Champions'
      WHEN revenue_rank <= CAST(total_customers * 0.35 AS INTEGER) THEN 'High Value'  
      WHEN revenue_rank <= CAST(total_customers * 0.65 AS INTEGER) THEN 'Growing'
      ELSE 'At Risk'
    END as customer_segment
  FROM ranked_customers
)
SELECT 
  customer_segment,
  COUNT(*) as customer_count,
  ROUND(AVG(lifetime_revenue), 2) as avg_lifetime_revenue,
  ROUND(AVG(avg_order_value), 2) as avg_order_value,
  ROUND(AVG(total_orders), 1) as avg_orders_per_customer,
  ROUND(AVG(customer_lifetime_days)) as avg_customer_lifetime_days,
  ROUND(SUM(lifetime_revenue), 2) as segment_total_revenue,
  ROUND(SUM(lifetime_revenue) * 100.0 / (SELECT SUM(lifetime_revenue) FROM customer_segments), 2) as revenue_percentage
FROM customer_segments
GROUP BY customer_segment
ORDER BY avg_lifetime_revenue DESC;

-- QUERY 2: Product Performance Analysis 

WITH product_performance AS (
  SELECT 
    p.ProductID,
    p.ProductName,
    cat.CategoryName,
    p.UnitPrice as current_list_price,
    p.UnitsInStock,
    p.Discontinued,
    COUNT(DISTINCT od.OrderID) as order_frequency,
    SUM(od.Quantity) as total_units_sold,
    ROUND(SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)), 2) as total_revenue,
    ROUND(AVG(od.UnitPrice * od.Quantity * (1 - od.Discount)), 2) as avg_revenue_per_order,
    ROUND(AVG(od.Discount) * 100, 2) as avg_discount_percent,
    ROUND(AVG(od.UnitPrice), 2) as avg_selling_price
  FROM Products p
  JOIN OrderDetails od ON p.ProductID = od.ProductID
  JOIN Categories cat ON p.CategoryID = cat.CategoryID
  GROUP BY p.ProductID, p.ProductName, cat.CategoryName, p.UnitPrice, p.UnitsInStock, p.Discontinued
)
SELECT 
  ProductName,
  CategoryName,
  current_list_price,
  total_revenue,
  total_units_sold,
  order_frequency,
  avg_discount_percent,
  UnitsInStock,
  CASE 
    WHEN total_revenue >= 20000 AND total_units_sold >= 1000 THEN 'Star Performer'
    WHEN total_revenue >= 10000 OR total_units_sold >= 500 THEN 'Solid Contributor'
    WHEN total_revenue >= 2000 THEN 'Moderate Performer'
    ELSE 'Underperformer'
  END as performance_tier
FROM product_performance
ORDER BY total_revenue DESC
LIMIT 10;

-- QUERY 3: Monthly Sales and Seasonal Trends   
WITH monthly_sales AS (
  SELECT 
    STRFTIME('%Y', o.OrderDate) as order_year,
    STRFTIME('%m', o.OrderDate) as order_month,
    STRFTIME('%Y-%m', o.OrderDate) as year_month,
    COUNT(DISTINCT o.OrderID) as orders_count,
    ROUND(SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)), 2) as monthly_revenue,
    ROUND(AVG(od.UnitPrice * od.Quantity * (1 - od.Discount)), 2) as avg_order_value,
    SUM(od.Quantity) as units_sold
  FROM Orders o
  JOIN OrderDetails od ON o.OrderID = od.OrderID
  GROUP BY STRFTIME('%Y', o.OrderDate), STRFTIME('%m', o.OrderDate)
),
seasonal_analysis AS (
  SELECT 
    order_month,
    CASE 
      WHEN order_month IN ('12', '01', '02') THEN 'Winter'
      WHEN order_month IN ('03', '04', '05') THEN 'Spring'
      WHEN order_month IN ('06', '07', '08') THEN 'Summer'
      ELSE 'Fall'
    END as season,
    AVG(monthly_revenue) as avg_seasonal_revenue,
    AVG(orders_count) as avg_seasonal_orders,
    AVG(avg_order_value) as avg_seasonal_order_value
  FROM monthly_sales
  GROUP BY order_month
)
SELECT 
  season,
  order_month,
  ROUND(avg_seasonal_revenue, 2) as avg_monthly_revenue,
  ROUND(avg_seasonal_orders, 0) as avg_monthly_orders,
  ROUND(avg_seasonal_order_value, 2) as avg_order_value,
  ROUND(avg_seasonal_revenue / (SELECT AVG(avg_seasonal_revenue) FROM seasonal_analysis) * 100, 1) as index_vs_average
FROM seasonal_analysis
ORDER BY CAST(order_month AS INTEGER);

-- QUERY 4: Employee Performance Analysis 
WITH employee_performance AS (
  SELECT 
    e.EmployeeID,
    e.FirstName || ' ' || e.LastName as employee_name,
    e.Title,
    e.HireDate,
    COUNT(DISTINCT o.OrderID) as total_orders,
    COUNT(DISTINCT o.CustomerID) as unique_customers_served,
    ROUND(SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)), 2) as total_sales,
    ROUND(AVG(od.UnitPrice * od.Quantity * (1 - od.Discount)), 2) as avg_order_value,
    ROUND(SUM(od.UnitPrice * od.Quantity * od.Discount), 2) as total_discounts_given,
    ROUND(AVG(od.Discount) * 100, 2) as avg_discount_rate,
    MIN(o.OrderDate) as first_sale_date,
    MAX(o.OrderDate) as last_sale_date
  FROM Employees e
  JOIN Orders o ON e.EmployeeID = o.EmployeeID
  JOIN OrderDetails od ON o.OrderID = od.OrderID
  GROUP BY e.EmployeeID, e.FirstName, e.LastName, e.Title, e.HireDate
)
SELECT 
  employee_name,
  Title,
  HireDate,
  total_orders,
  unique_customers_served,
  total_sales,
  avg_order_value,
  ROUND(total_sales / total_orders, 2) as sales_per_order,
  ROUND(total_sales / unique_customers_served, 2) as sales_per_customer,
  avg_discount_rate,
  total_discounts_given,
  ROW_NUMBER() OVER (ORDER BY total_sales DESC) as sales_rank,
  CASE 
    WHEN total_sales >= (SELECT AVG(total_sales) * 1.5 FROM employee_performance) THEN 'Top Performer'
    WHEN total_sales >= (SELECT AVG(total_sales) FROM employee_performance) THEN 'Above Average'
    ELSE 'Needs Development'
  END as performance_category
FROM employee_performance
ORDER BY total_sales DESC;

-- QUERY 5: Geographic Market Analysis 
WITH geographic_performance AS (
  SELECT 
    o.ShipCountry,
    COUNT(DISTINCT o.CustomerID) as unique_customers,
    COUNT(DISTINCT o.OrderID) as total_orders,
    ROUND(SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)), 2) as total_revenue,
    ROUND(AVG(od.UnitPrice * od.Quantity * (1 - od.Discount)), 2) as avg_order_value,
    SUM(od.Quantity) as total_units_sold,
    ROUND(AVG(o.Freight), 2) as avg_shipping_cost
  FROM Orders o
  JOIN OrderDetails od ON o.OrderID = od.OrderID
  GROUP BY o.ShipCountry
)
SELECT 
  ShipCountry,
  unique_customers,
  total_orders,
  total_revenue,
  avg_order_value,
  ROUND(total_revenue / unique_customers, 2) as revenue_per_customer,
  ROUND(CAST(total_orders AS REAL) / unique_customers, 1) as orders_per_customer,
  total_units_sold,
  avg_shipping_cost,
  ROUND(total_revenue * 100.0 / (SELECT SUM(total_revenue) FROM geographic_performance), 2) as revenue_percentage,
  ROW_NUMBER() OVER (ORDER BY total_revenue DESC) as revenue_rank
FROM geographic_performance
ORDER BY total_revenue DESC
LIMIT 10;

-- QUERY 6: Category Performance Deep Dive 
SELECT 
  cat.CategoryName,
  COUNT(DISTINCT p.ProductID) as products_in_category,
  COUNT(DISTINCT od.OrderID) as orders_with_category,
  ROUND(SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)), 2) as category_revenue,
  ROUND(AVG(od.UnitPrice * od.Quantity * (1 - od.Discount)), 2) as avg_order_line_value,
  SUM(od.Quantity) as total_units_sold,
  ROUND(AVG(od.Discount) * 100, 2) as avg_discount_percent,
  ROUND(SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)) / COUNT(DISTINCT p.ProductID), 2) as revenue_per_product,
  ROUND(SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)) * 100.0 / 
        (SELECT SUM(od2.UnitPrice * od2.Quantity * (1 - od2.Discount)) 
         FROM OrderDetails od2), 2) as category_revenue_share
FROM Categories cat
JOIN Products p ON cat.CategoryID = p.CategoryID
JOIN OrderDetails od ON p.ProductID = od.ProductID
GROUP BY cat.CategoryID, cat.CategoryName
ORDER BY category_revenue_share DESC;