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:

  1. Group by period and count users
  2. 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  |