Capstone Challenge 1

Build an efficient daily KPI summary query — the kind of query that powers real dashboards.

💡 Strategy: LAG with Offset

You've used LAG(column) to get the previous row. You can also specify an offset:

LAG(total_sessions, 7) OVER(ORDER BY date)
-- Gets the value from 7 rows (days) ago!

Week-over-Week Growth Formula

growth_pct = (today - same_day_last_week) / same_day_last_week × 100

⚠️ NULL Handling

The first 7 days won't have a previous week value → they'll be NULL. Filter them out with WHERE.

Requirements

Using the pre-aggregated daily_stats table:

• Calculate week-over-week growth for sessions
• Show: date, total_sessions, prev_week_sessions, session_growth_pct
• Use LAG with offset 7 to get the value from 7 days ago
• Only show dates where we have previous week data