Explain the difference between LAG and LEAD functions and provide use cases for each.

LAG accesses previous rows in the ordered result set - perfect for "compared to last period" analysis like month-over-month growth, stock price changes, or trend analysis. LEAD accesses future rows - ideal for forecasting, planning, and predictive analysis like "will this customer churn next month?" or "how does our forecast compare to actual results?" Both require ORDER BY and optionally take offset (how many rows back/forward) and default value (what to return when no row exists).

Why are LAG and LEAD better than self-joins for time-series analysis?

LAG/LEAD are simpler, faster, and more readable than self-joins. Self-joins require complex ON conditions, are harder to understand, and perform poorly on large datasets. LAG/LEAD are optimized window functions that scan data once, while self-joins can require multiple table scans. Example: LAG(sales, 1) OVER (ORDER BY month) is much clearer than joining the table to itself on month relationships.

How do you handle NULL values and edge cases with LAG and LEAD?

Always provide default values as the third parameter: LAG(column, offset, default). For first rows, LAG returns the default; for last rows, LEAD returns the default. Handle division by zero in percentage calculations with NULLIF() or CASE statements. Use meaningful defaults: 0 for numeric calculations, 'N/A' for text, or NULL when no comparison makes sense.

Write a query to calculate month-over-month revenue growth percentage, handling edge cases properly.

SELECT

month,

revenue,

LAG(revenue, 1, 0) OVER (ORDER BY month) as prev_month_revenue,

CASE

WHEN LAG(revenue, 1, 0) OVER (ORDER BY month) = 0 THEN 'N/A'

ELSE CONCAT(

ROUND(

(revenue - LAG(revenue, 1, 0) OVER (ORDER BY month)) * 100.0 /

LAG(revenue, 1, 0) OVER (ORDER BY month), 2

), '%'

)

END as growth_rate

FROM monthly_revenue

ORDER BY month;

Uses LAG with default value 0, handles division by zero with CASE statement, and formats the result as a percentage. The first month shows 'N/A' since there's no previous month to compare to.

Create a query to identify customers whose engagement is declining over the next 2 weeks using LEAD.

WITH engagement_trends AS (

SELECT

customer_id,

week_date,

engagement_score,

LEAD(engagement_score, 1, 0) OVER (

PARTITION BY customer_id ORDER BY week_date

) as next_week_score,

LEAD(engagement_score, 2, 0) OVER (

PARTITION BY customer_id ORDER BY week_date

) as two_weeks_score

FROM customer_weekly_engagement

)

SELECT customer_id, week_date, engagement_score,

next_week_score, two_weeks_score

FROM engagement_trends