SELECT
  sale_date,
  revenue,
  ROUND(
    AVG(revenue) OVER(
      ORDER BY sale_date
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- Establish row order and the frame’s reference order
    ), 1                                        -- Frame = current row plus two preceding rows (up to three); opening rows use only available rows
  ) AS moving_avg_3d                            -- Round to one decimal place
FROM daily_sales
ORDER BY sale_date;

/*
  Execution order:
  1. FROM daily_sales                          → retrieve 5 rows
  2. OVER(ORDER BY sale_date)                  → establish ascending date order
  3. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  → define the moving-average frame
  4. ROUND(..., 1)                             → round
  5. SELECT output                                 → return the columns
  */

SELECT
  student_name,
  subject,
  score,
  ROUND(
    PERCENT_RANK() OVER(
      PARTITION BY subject
      ORDER BY score ASC    -- Calculate percentile rank independently for each subject
    ), 2                    -- With ASC and these untied scores, the lowest is 0.00 and the highest is 1.00
  ) AS pct_rank             -- Round to two decimal places
FROM test_scores
ORDER BY
  CASE subject WHEN 'Mathematics' THEN 1 WHEN 'English' THEN 2 END,
  score;

/*
  Execution order:
  1. FROM test_scores      → retrieve 5 rows
  2. PARTITION BY subject  → partition by subject
  3. ORDER BY score ASC    → sort each subject by score ascending
  4. PERCENT_RANK()        → apply to every row
  5. ROUND(..., 2)         → round to two decimal places
  6. SELECT output             → return columns in the explicit subject order, then by score
  */

-- Calculate LAG() once in the CTE and reuse it for growth in the outer query
WITH base AS (
  SELECT
    month,
    revenue,
    LAG(revenue) OVER(ORDER BY month) AS prev_revenue  -- Previous row’s revenue; the first row is NULL
  FROM monthly_revenue
)
SELECT
  month,
  revenue,
  prev_revenue,
  ROUND(
    (revenue - prev_revenue) * 100.0
      / NULLIF(prev_revenue, 0), 1  -- Decimal division; NULL when prev is NULL or zero
  ) AS growth_rate_pct
FROM base
ORDER BY month;

/*
  Execution order:
  1. CTE (WITH base)                   → read monthly_revenue
  2. OVER(ORDER BY month)              → sort by month
  3. LAG(revenue)                      → attach the previous month’s revenue
  4. Outer (revenue - prev) * 100.0 / NULLIF(prev, 0)  → calculate MoM growth safely
  5. ROUND(..., 1)                     → round and return
  */

SELECT
  category,
  product,
  amount
FROM (
  -- Evaluate the window function and assign row numbers inside the subquery
  SELECT
    category,
    product,
    amount,
    ROW_NUMBER() OVER(
      PARTITION BY category
      ORDER BY amount DESC, product ASC  -- Number rows deterministically within each category
    ) AS rn                  -- The tiebreaker makes row numbers deterministic even for equal amounts
  FROM product_sales
) ranked
WHERE rn <= 2                -- Filter by the already-calculated rn in the outer query
ORDER BY
  CASE category WHEN 'Beverages' THEN 1 WHEN 'Food' THEN 2 END,
  amount DESC, product ASC;
/*
  Execution order:
  1. Subquery FROM product_sales  → retrieve 6 rows
  2. PARTITION BY category     → partition by category
  3. ORDER BY amount DESC, product ASC  → sort by amount DESC, product ASC
  4. ROW_NUMBER()              → assign row numbers
  5. Outer WHERE rn <= 2         → keep the top two
  6. SELECT / ORDER BY         → return category, product, and amount in explicit order
  */