LAG() is a window function that returns the value from the preceding row in the same partition for the current row. It is ideal for time-series differences such as year-over-year or month-over-month change.

LAG(revenue) OVER (
  PARTITION BY company_name   -- An independent window for each company
  ORDER BY     fiscal_year    -- Ascending year order determines the preceding row
) AS prev_revenue             -- The first row is NULL because no preceding row exists

Using annual revenue data for three companies, calculate each company's year-over-year growth rate (YoY %). Use a CTE to attach the previous year's revenue as prev_revenue, then calculate yoy_pct, rounded to one decimal place, in the outer query. Return company_name, fiscal_year, revenue, prev_revenue, yoy_pct, sorted by company_name ascending and then fiscal_year ascending.

Expected output (company_name ascending → fiscal_year ascending):

The first year, 2022, has no previous-year data, so its values are NULL. BetaSoft's revenue fell in 2023, producing a negative growth rate.

WITH base AS (
  SELECT
    company_name,
    fiscal_year,
    revenue,
    LAG(revenue) OVER (
      PARTITION BY company_name    -- An independent window for each company
      ORDER BY     fiscal_year     -- Refer to the preceding row in ascending year order
    ) AS prev_revenue               -- The first year, 2022, is NULL for every company
  FROM   annual_revenue
)
SELECT
  company_name,
  fiscal_year,
  revenue,
  prev_revenue,
  ROUND(
    (revenue - prev_revenue) * 100.0 /
    NULLIF(prev_revenue, 0),         -- Return NULL when prev=0 to prevent division by zero
    1
  ) AS yoy_pct
FROM   base
ORDER BY company_name, fiscal_year;

/*
  Execution order (logical SQL evaluation order):
  1. CTE base    → Define the CTE and evaluate LAG
  2. Outer query:
     FROM base    → Read the rows
     SELECT       → Evaluate the columns and format yoy_pct
     ORDER BY     → Sort and return the result
*/

SQL provides three ranking window functions, and they handle ties differently.

RANK() OVER (
  PARTITION BY category      -- Restart at 1 for each category
  ORDER BY     avg_score DESC  -- Rank by score descending
) AS score_rank

Using customer-satisfaction scores by category and company, assign a within-category score ranking named score_rank. Return category, company_name, avg_score, review_count, score_rank, sorted by category ascending and then score_rank ascending.

Expected output (category ascending → score_rank ascending):

BetaSoft leads Security with 4.5, giving the category a different ranking structure from Cloud, where AlphaTech leads with 4.3.

SELECT
  category,
  company_name,
  avg_score,
  review_count,
  RANK() OVER (
    PARTITION BY category        -- Restart at 1 for each category
    ORDER BY     avg_score DESC  -- Assign 1, 2, 3, ... from highest to lowest score
  ) AS score_rank
FROM   competitor_scores
ORDER BY category, score_rank;

/*
  Execution order (logical SQL evaluation order):
  1. FROM competitor_scores  → Read the rows
  2. RANK() OVER (...)       → Evaluate the window function without changing the row count
  3. SELECT                  → Evaluate the columns
  4. ORDER BY                → Sort and return the result
*/

When data is stored in long format, CASE WHEN, MAX, and GROUP BY can convert, or pivot, it into wide format.

↓ Pivot with CASE WHEN + GROUP BY

MAX(CASE WHEN kpi_name = 'Revenue (JPY 100M)' THEN kpi_value END) AS revenue_100m

From the long-format competitor_kpi table, pivot each company's KPIs into columns. Return company_name, revenue_100m, op_margin_pct, new_customers_10k, sorted by revenue_100m descending.

Expected output (revenue_100m descending):

The 12 long-format rows are aggregated into 4 wide-format rows, making the competitors' major KPIs easy to compare side by side.

SELECT
  company_name,
  MAX(CASE WHEN kpi_name = 'Revenue (JPY 100M)'    THEN kpi_value END) AS revenue_100m,  -- Pivot long-format rows into columns
  MAX(CASE WHEN kpi_name = 'Operating margin (%)'   THEN kpi_value END) AS op_margin_pct,
  MAX(CASE WHEN kpi_name = 'New customers (10K)' THEN kpi_value END) AS new_customers_10k
FROM   competitor_kpi
GROUP BY company_name
ORDER BY revenue_100m DESC;

/*
  Execution order (logical SQL evaluation order):
  1. FROM competitor_kpi         → Read the long-format data
  2. GROUP BY company_name       → Group the rows by company
  3. CASE + MAX(...)             → Pivot the rows into columns
  4. SELECT                      → Select the columns
  5. ORDER BY revenue_100m DESC  → Sort and return the result
  */

NTILE(n) is a window function that sorts rows with ORDER BY and then distributes them into n buckets. It is useful for price-band analysis, customer segmentation, sales quantiles, and many other tasks.

NTILE(4) OVER (
  ORDER BY monthly_price    -- Sort by price ascending and divide into four buckets
) AS price_quartile         -- 1=lowest, 4=highest

Classify 11 competing products from four companies into four price quartiles, from Low to High. Calculate the NTILE value as price_quartile in a subquery, then add an English price_segment label in the outer query. Return product_name, company_name, monthly_price, price_quartile, price_segment, sorted by monthly_price ascending.

Expected output (monthly_price ascending):

For 11 rows ÷ 4, the remainder is 3, so buckets 1, 2, and 3 contain three rows and bucket 4 contains two. AlphaTech spans every band with a tiered pricing strategy.

SELECT
  product_name,
  company_name,
  monthly_price,
  price_quartile,
  CASE price_quartile
    WHEN 1 THEN 'Low'     -- Q1: lowest-priced group
    WHEN 2 THEN 'Lower-middle'
    WHEN 3 THEN 'Upper-middle'
    WHEN 4 THEN 'High'    -- Q4: highest-priced group
  END AS price_segment
FROM (
  SELECT
    product_name,
    company_name,
    monthly_price,
    NTILE(4) OVER (
      ORDER BY monthly_price  -- Sort by price ascending and divide into four buckets
    ) AS price_quartile
  FROM   competitor_products
) sub
ORDER BY monthly_price;

/*
  Execution order (logical SQL evaluation order):
  1. Subquery sub
  2. Outer query
  */