Calculate period-to-period change in SQL
Method 1
This assumes that a single row exist for each user and each period (by day, in our example). In this case, the calculation can be made in one step with LAG()
:
-- One step: count distinct users and use LAG()
SELECT
date,
COUNT(DISTINCT user_id) AS users,
LAG(COUNT(DISTINCT user_id)) OVER (ORDER BY date) AS prev_users,
COUNT(DISTINCT user_id) / LAG(COUNT(DISTINCT user_id)) OVER (ORDER BY date) -1 AS change
FROM `training.ga_users`
GROUP BY 1
ORDER BY date
LIMIT 10
| date | users | prev_users | change |
|------------|-------|------------|-----------|
| 2017-01-01 | 134 | NaN | NaN |
| 2017-01-02 | 138 | 134.0 | 0.029851 |
| 2017-01-03 | 207 | 138.0 | 0.500000 |
| 2017-01-04 | 220 | 207.0 | 0.062802 |
| 2017-01-05 | 184 | 220.0 | -0.163636 |
| 2017-01-06 | 191 | 184.0 | 0.038043 |
| 2017-01-07 | 131 | 191.0 | -0.314136 |
| 2017-01-08 | 144 | 131.0 | 0.099237 |
| 2017-01-09 | 200 | 144.0 | 0.388889 |
| 2017-01-10 | 214 | 200.0 | 0.070000 |
Method 2
If there is not a single row for each period, then a self join should be used:
- Group by period and count users
- Self join on offset period, and calculate change over previous period
-- Step 1: count users at period level
WITH sub1 AS (
SELECT
date,
COUNT(*) AS users
FROM `training.ga_users`
GROUP BY 1
)
-- Step 2: self-join and calculate change over previous period
SELECT
curr.date,
curr.users AS users,
prev.users AS prev_users,
curr.users/prev.users-1 AS change
FROM sub1 AS curr
LEFT JOIN sub1 AS prev
ON prev.date = DATE_ADD(curr.date, INTERVAL -1 DAY)
ORDER BY date
LIMIT 10
| date | users | prev_users | change |
|------------|-------|------------|-----------|
| 2017-01-01 | 134 | NaN | NaN |
| 2017-01-02 | 138 | 134.0 | 0.029851 |
| 2017-01-03 | 207 | 138.0 | 0.500000 |
| 2017-01-04 | 220 | 207.0 | 0.062802 |
| 2017-01-05 | 184 | 220.0 | -0.163636 |
| 2017-01-06 | 191 | 184.0 | 0.038043 |
| 2017-01-07 | 131 | 191.0 | -0.314136 |
| 2017-01-08 | 144 | 131.0 | 0.099237 |
| 2017-01-09 | 200 | 144.0 | 0.388889 |
| 2017-01-10 | 214 | 200.0 | 0.070000 |