SQL Analytical Functions – LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE()

🔹 Why Analytical Functions Matter

Analytical or window functions add insights across rows without collapsing the dataset. They power comparisons, time-series analysis, dashboards, and QA validation where the original row structure must stay intact.

⚙️ 1️⃣ Analytical Function Primer

Analytical functions operate over a window defined by OVER (PARTITION BY ... ORDER BY ...). They return a value per row instead of aggregating multiple rows into one result.

⚙️ 2️⃣ Example Dataset – Regional Sales

⚙️ 3️⃣ LAG() – Compare to Previous Row

SELECT 
  Region,
  Month,
  Sales,
  LAG(Sales, 1) OVER (PARTITION BY Region ORDER BY Month) AS PrevMonthSales,
  Sales - LAG(Sales, 1) OVER (PARTITION BY Region ORDER BY Month) AS SalesChange
FROM Sales;

⚙️ 4️⃣ LEAD() – Peek at the Next Row

SELECT 
  Region,
  Month,
  Sales,
  LEAD(Sales, 1) OVER (PARTITION BY Region ORDER BY Month) AS NextMonthSales
FROM Sales;

⚙️ 5️⃣ FIRST_VALUE() – First Value per Partition

SELECT 
  Region,
  Month,
  Sales,
  FIRST_VALUE(Sales) OVER (PARTITION BY Region ORDER BY Month) AS FirstMonthSales
FROM Sales;

⚙️ 6️⃣ LAST_VALUE() – Last Value per Partition

SELECT 
  Region,
  Month,
  Sales,
  LAST_VALUE(Sales) OVER (
      PARTITION BY Region
      ORDER BY Month
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS LastMonthSales
FROM Sales;

⚙️ 7️⃣ Combine Multiple Functions

SELECT 
  Region,
  Month,
  Sales,
  LAG(Sales)  OVER (PARTITION BY Region ORDER BY Month) AS Prev,
  LEAD(Sales) OVER (PARTITION BY Region ORDER BY Month) AS Next,
  FIRST_VALUE(Sales) OVER (PARTITION BY Region ORDER BY Month) AS First,
  LAST_VALUE(Sales)  OVER (
      PARTITION BY Region ORDER BY Month
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS Last
FROM Sales;

🧩 Real-World Scenarios

⚖️ Best Practices

• Partition logically (region, customer, department) to reset calculations.
• Always specify ORDER BY so the window has a deterministic sequence.
• Handle NULLs explicitly for boundary rows returned by LAG/LEAD.
• Limit frame sizes when working with massive datasets to control memory usage.

💡 Interview Tip