SQL Percentage Change
How much a value grew or shrank relative to where it started — (new - old) / old * 100. The sign tells you the direction.
What & Why
Percentage change measures relative movement between two values: (new_value - old_value) / old_value * 100. A positive result means growth; a negative result means decline. This needs LAG (a window function from the Advanced tier) in a real query to access "last month's" value alongside the current row — this lesson focuses on the formula itself.
See How It Works
BUSINESS QUESTION
Growth wants the day-over-day percentage change in daily active users.
| date_day | dau | new_users | returning_users |
|---|---|---|---|
| 2024-04-01 | 1240 | 180 | 1060 |
| 2024-04-02 | 1315 | 205 | 1110 |
| 2024-04-03 | 1288 | 164 | 1124 |
| 2024-04-04 | 1392 | 221 | 1171 |
Watch change use the previous value as its baselineExample 1 of 4
(current - previous) / previous × 100
| date_day | dau | previous_dau | change_pct |
|---|---|---|---|
| 2024-04-01 | 1240 | NULL | NULL |
| 2024-04-02 | 1315 | ? | ? |
| 2024-04-03 | 1288 | ? | ? |
| 2024-04-04 | 1392 | ? | ? |
The first ordered day has no previous DAU value.
EXAMPLE QUERY
WITH compared AS (
SELECT
date_day,
dau,
LAG(dau) OVER (ORDER BY date_day) AS previous_dau
FROM growth.daily_active_users
)
SELECT
date_day,
dau,
previous_dau,
ROUND((dau - previous_dau)::numeric / NULLIF(previous_dau, 0) * 100, 2) AS change_pct
FROM compared
ORDER BY date_day;Now You Try
Practice this concept
Growth wants the day-over-day percentage change in daily active users.
Available schema
growthPrefix tables with growth.table_name.
date_daydauprevious_dauchange_pctgrowth.daily_active_users| Column | Type |
|---|---|
| date_day | date |
| dau | integer |
| new_users | integer |
| returning_users | integer |
| internal_tracking_code | text |
query.sql
Sign up free to try it on a real business scenario