How to Use MySQL User Variables for Cumulative Daily User Counts

01 Introduction

The product team needed a growth curve showing the cumulative number of users over time, i.e., each day’s total plus the previous days’ totals, based on a simple user_info table with user_id and reg_time.

02 Basic Daily Count

A straightforward query can count daily new users:

select reg_time, count(user_id) daily_quantity
from user_info
group by reg_time

This returns the daily new‑user numbers but not the cumulative totals required.

03 Desired Cumulative Result

The required output should show the running total, e.g., 1, 3, 5, 6 for the sample data.

04 Java‑style Pseudo Code

One way to compute the running total is to use a loop in application code:

public static void main(String[] args) {
    int[] arr = {1, 2, 2, 1};
    int[] ints = dailyQuantityArr(0, arr);
    for (int i : ints) {
        System.out.println("i = " + i);
    }
}

public static int[] dailyQuantityArr(int base, int[] dailyIncrQuantity) {
    int[] result = new int[dailyIncrQuantity.length];
    // cumulative fill
    for (int i = 0; i < dailyIncrQuantity.length; i++) {
        base += dailyIncrQuantity[i];
        result[i] = base;
    }
    return result;
}

While this works, the requirement was to obtain the result directly in SQL.

05 MySQL User Variables

MySQL provides user variables (prefixed with @) that persist for the duration of a connection. They can be used without prior declaration and are assigned with :=.

Example of generating a row number:

select (@i:=@i+1) as rownum, user_id
from user_info, (select @i:=0) r;

06 Cumulative Calculation with Variables

Using a user variable to accumulate the daily counts yields the desired running total:

select a.reg_time,
       a.daily,
       @i:=@i + a.daily as daily_quantity
from (
    select reg_time, count(user_id) daily
    from user_info
    group by reg_time
) a,
(select @i:=0) b;

The query returns:

reg_time   daily   daily_quantity
2019-09-03   1          1
2019-09-04   2          3
2019-09-05   2          5
2019-09-06   1          6

Note that if @i is initialized to a non‑zero value (e.g., when the period starts after an earlier date), the running total will be offset accordingly, so the initialization must reflect any prior accumulated count.

07 Summary

By leveraging MySQL user variables, we can perform cumulative calculations directly in a query, avoiding extra application‑side processing while being aware of variable scope and initialization nuances.