Advanced Window Functions

Advanced Window Functions: Time Travel with Your Data! 🚀

Imagine you’re watching a parade from your bedroom window. You can see who’s passing by right now, but what if you could also peek at who just passed or who’s about to come? That’s exactly what advanced window functions let you do with your data!

The Magic Window Analogy 🪟

Think of your database table as a long parade of numbers marching past your window. Regular SQL only shows you who’s at your window RIGHT NOW. But with window functions, you get magical binoculars that let you:

• 👀 Look BACK at who just passed (LAG)
• 👀 Look FORWARD at who’s coming next (LEAD)
• 📏 Set BOUNDARIES for how far you can see (Window Frame)
• ➕ Keep a RUNNING SCORE of everything (Running Totals)
• 📊 Get SUMMARIES of what you see (Aggregate Windows)

1. LAG Function: Peeking at the Past 👈

What is LAG?

LAG is like having eyes in the back of your head. It lets you see the value from a previous row while staying on your current row.

Simple Example: Yesterday’s Temperature

Imagine you’re tracking daily temperatures:

SELECT
  day_name,
  temperature,
  LAG(temperature, 1)
    OVER (ORDER BY day_date)
    AS yesterday_temp
FROM weather;

Result:

How LAG Works

LAG(column, offset, default)
    │        │       │
    │        │       └── What to show if no previous row (optional)
    │        └────────── How many rows back to look (default: 1)
    └─────────────────── Which column to peek at

Real-Life Use: Price Changes

SELECT
  product_name,
  price,
  LAG(price) OVER (
    PARTITION BY product_name
    ORDER BY change_date
  ) AS old_price,
  price - LAG(price) OVER (
    PARTITION BY product_name
    ORDER BY change_date
  ) AS price_change
FROM prices;

🎯 Key Insight: LAG looks BACKWARDS. Think “LAG = Look At past Guy”

2. LEAD Function: Seeing the Future 👉

What is LEAD?

LEAD is LAG’s twin sister, but she looks forward instead of backward. She tells you what’s coming NEXT!

Simple Example: Tomorrow’s Weather

SELECT
  day_name,
  temperature,
  LEAD(temperature, 1)
    OVER (ORDER BY day_date)
    AS tomorrow_temp
FROM weather;

Result:

The LEAD Pattern

LEAD(column, offset, default)
    │        │       │
    │        │       └── What to show if no future row
    │        └────────── How many rows ahead to look
    └─────────────────── Which column to peek at

Real-Life Use: Appointment Gaps

SELECT
  appointment_time,
  patient_name,
  LEAD(appointment_time) OVER (
    ORDER BY appointment_time
  ) AS next_appointment,
  TIMESTAMPDIFF(
    MINUTE,
    appointment_time,
    LEAD(appointment_time) OVER (
      ORDER BY appointment_time
    )
  ) AS gap_minutes
FROM appointments;

🎯 Key Insight: LEAD looks FORWARD. Think “LEAD = Look Eager to Ahead Data”

3. Window Frame Clauses: Setting Your View 📐

What is a Window Frame?

Remember our parade analogy? A window frame decides exactly how much of the parade you can see through your magic window!

The Building Blocks

OVER (
  PARTITION BY category    -- Which parade?
  ORDER BY date           -- Which direction?
  ROWS BETWEEN ... AND ... -- How wide is window?
)

Frame Types: ROWS vs RANGE

ROWS  = Count by position (1st, 2nd, 3rd...)
RANGE = Count by value (same values = same group)

Frame Boundaries Explained

UNBOUNDED PRECEDING  ← From the very beginning
N PRECEDING         ← From N rows back
CURRENT ROW         ← Right here
N FOLLOWING         ← To N rows ahead
UNBOUNDED FOLLOWING ← To the very end

Visual: Frame Examples

Data: [10] [20] [30] [40] [50]
            ▲ (Current Row)

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW:
      [20] [30]
      └──┬──┘
       Window

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING:
      [20] [30] [40]
      └────┬────┘
         Window

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW:
[10] [20] [30]
└────┬────┘
   Window (all from start to here)

Practical Example: 3-Day Moving Average

SELECT
  sale_date,
  revenue,
  AVG(revenue) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING
             AND CURRENT ROW
  ) AS three_day_avg
FROM sales;

4. Running Totals and Averages: The Scoreboard 📊

What are Running Totals?

Imagine keeping score in a basketball game. After each basket, you ADD to the total. You don’t start over—you keep a running score!

The Classic Running Total

SELECT
  order_date,
  order_amount,
  SUM(order_amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING
             AND CURRENT ROW
  ) AS running_total
FROM orders;

Result:

Running Average: The Moving Lens

SELECT
  test_date,
  score,
  AVG(score) OVER (
    ORDER BY test_date
    ROWS BETWEEN UNBOUNDED PRECEDING
             AND CURRENT ROW
  ) AS running_avg
FROM test_scores;

Grouped Running Totals

Want separate running totals per category? Use PARTITION BY:

SELECT
  category,
  sale_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY category
    ORDER BY sale_date
    ROWS UNBOUNDED PRECEDING
  ) AS category_running_total
FROM sales;

graph TD
    A["Each Row"] --> B{PARTITION BY?}
    B -->|Yes| C["Separate Running Total<br>per Group"]
    B -->|No| D["One Running Total<br>for All"]
    C --> E["ORDER BY determines<br>accumulation order"]
    D --> E

5. Aggregate Window Functions: The Bird’s Eye View 🦅

What Makes Them Special?

Regular aggregates (SUM, AVG, COUNT) collapse many rows into one. Window aggregates give you the summary while keeping every row!

Compare: Regular vs Window

Regular Aggregate:

SELECT category, SUM(price)
FROM products
GROUP BY category;
-- Returns ONE row per category

Window Aggregate:

SELECT
  product_name,
  category,
  price,
  SUM(price) OVER (
    PARTITION BY category
  ) AS category_total
FROM products;
-- Returns EVERY row, plus the total!

The Power Trio: SUM, AVG, COUNT as Windows

SELECT
  employee_name,
  department,
  salary,
  -- Total salary in department
  SUM(salary) OVER (
    PARTITION BY department
  ) AS dept_total,
  -- Average salary in department
  AVG(salary) OVER (
    PARTITION BY department
  ) AS dept_avg,
  -- How many in department
  COUNT(*) OVER (
    PARTITION BY department
  ) AS dept_count,
  -- Your salary as % of department
  ROUND(100.0 * salary / SUM(salary)
    OVER (PARTITION BY department), 1
  ) AS pct_of_dept
FROM employees;

MIN and MAX Windows

SELECT
  product_name,
  price,
  MIN(price) OVER () AS cheapest,
  MAX(price) OVER () AS priciest,
  price - MIN(price) OVER ()
    AS above_minimum
FROM products;

Putting It All Together: A Complete Example 🎪

Let’s analyze a store’s daily sales with ALL our tools:

SELECT
  sale_date,
  daily_revenue,

  -- LAG: Yesterday's revenue
  LAG(daily_revenue) OVER w
    AS yesterday,

  -- LEAD: Tomorrow's revenue
  LEAD(daily_revenue) OVER w
    AS tomorrow,

  -- Running Total
  SUM(daily_revenue) OVER (
    ORDER BY sale_date
    ROWS UNBOUNDED PRECEDING
  ) AS running_total,

  -- 3-Day Moving Average
  AVG(daily_revenue) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING
             AND CURRENT ROW
  ) AS three_day_avg,

  -- Comparison to overall average
  daily_revenue - AVG(daily_revenue)
    OVER () AS vs_overall_avg

FROM daily_sales
WINDOW w AS (ORDER BY sale_date);

Quick Reference: The Cheat Codes 🎮

You Did It! 🎉

You’ve just learned the time-travel powers of SQL! Now you can:

• ✅ Peek at previous values with LAG
• ✅ See future values with LEAD
• ✅ Control your view with Window Frames
• ✅ Keep running scores with Running Totals
• ✅ Get summaries while keeping details with Aggregate Windows

These aren’t just fancy tricks—they’re essential tools for real data analysis. Stock analysts use them for moving averages. Business analysts use them for trend analysis. And now, YOU can use them too!

💡 Final Tip: Practice with small datasets first. Once you see the pattern, these functions become second nature!