SQL & Analytics

-- Scenario: Sales data — calculate running total, rank, and growth

CREATE TABLE sales (
  id          INT,
  salesperson VARCHAR(50),
  region      VARCHAR(50),
  sale_date   DATE,
  amount      DECIMAL(10,2)
);

-- 1. BASIC QUERY: Total sales per salesperson
SELECT
  salesperson,
  SUM(amount)   AS total_sales,
  COUNT(*)      AS num_deals,
  AVG(amount)   AS avg_deal_size
FROM sales
GROUP BY salesperson
ORDER BY total_sales DESC;

-- 2. WINDOW FUNCTION: Running total by date (no GROUP BY needed!)
SELECT
  sale_date,
  salesperson,
  amount,
  SUM(amount) OVER (
    PARTITION BY salesperson
    ORDER BY sale_date
  ) AS running_total
FROM sales;

-- 3. RANK: Who is #1 in each region?
SELECT *
FROM (
  SELECT
    salesperson,
    region,
    SUM(amount) AS total,
    RANK() OVER (
      PARTITION BY region
      ORDER BY SUM(amount) DESC
    ) AS regional_rank
  FROM sales
  GROUP BY salesperson, region
) ranked
WHERE regional_rank = 1;

-- 4. CTE + LAG: Month-over-month growth %
WITH monthly AS (
  SELECT
    DATE_TRUNC('month', sale_date) AS month,
    SUM(amount)                    AS revenue
  FROM sales
  GROUP BY 1
)
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
        / LAG(revenue) OVER (ORDER BY month), 2) AS growth_pct
FROM monthly;