SQL AVG OVER
SQL AVG OVER helps analysts calculate a windowed average while preserving every detail row.
What & Why
The AVG OVER pattern is used to calculate a windowed average while preserving every detail row. This keeps the SQL aligned with one concrete business question and makes the result grain explicit before the query is reused.
See How It Works
BUSINESS QUESTION
Marketing wants each campaign compared with average spend in its channel.
| id | name | channel | spend | start_date | end_date | status | target_segment |
|---|---|---|---|---|---|---|---|
| 1 | Spring Launch | google_ads | 55000.00 | 2024-01-15 | 2024-03-31 | active | smb |
| 2 | Retention Webinar | 45000.00 | 2024-02-10 | 2024-04-15 | active | enterprise | |
| 3 | Finance Retargeting | 50000.00 | 2024-03-12 | 2024-05-31 | active | enterprise | |
| 4 | Enterprise Search | google_ads | 60000.00 | 2024-04-01 | 2024-06-30 | active | enterprise |
EXAMPLE QUERY
SELECT
id AS campaign_id,
channel,
spend,
ROUND(AVG(spend) OVER (PARTITION BY channel), 2) AS channel_avg_spend
FROM marketing.campaigns
ORDER BY channel, campaign_id;RESULT — channel average beside campaigns
| campaign_id | channel | spend | channel_avg_spend |
|---|---|---|---|
| 2 | 45000.00 | 45000.00 | |
| 1 | google_ads | 55000.00 | 57500.00 |
| 4 | google_ads | 60000.00 | 57500.00 |
| 3 | 50000.00 | 50000.00 |
The google_ads partition contains two campaigns; the other partitions contain one.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario