SQL PARTITION BY
Splits the table into independent groups for the window function — without merging any rows together.
What & Why
PARTITION BY divides the rows into groups, and the window function is computed separately within each group. It's conceptually similar to GROUP BY — except every row survives, grouped or not.
See How It Works
BUSINESS QUESTION
Marketing wants every campaign beside total spend for its own 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 |
Watch PARTITION BY calculate one total per channelStep 1 of 3
SUM(spend) OVER (PARTITION BY channel)
| campaign_id | name | channel | spend | channel_spend |
|---|---|---|---|---|
| 2 | Retention Webinar | 45,000.00 | 45,000.00 | |
| 4 | Enterprise Search | google_ads | 60,000.00 | 115,000.00 |
| 1 | Spring Launch | google_ads | 55,000.00 | 115,000.00 |
| 3 | Finance Retargeting | 50,000.00 | 50,000.00 |
The email partition contains one campaign, so its channel total is 45,000.00.
EXAMPLE QUERY
SELECT
id AS campaign_id,
name,
channel,
spend,
SUM(spend) OVER (PARTITION BY channel) AS channel_spend
FROM marketing.campaigns
ORDER BY channel, spend DESC, campaign_id;This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario