How do I get average user count based on each day?

[imgrooot]

I have a "Users" table. I would like to find out the average users sign up in total. This table has a "joined date" column so I can track how many users sign up in a single day.

For e.g.

August 16 - 10 users

August 17 - 20 users

Auguest 18 - 30 users

The total average of user sign ups would be 20 users based on the above results.

So I am wondering how can I create this function?

This is my starting query.

$get_users = $db->prepare("SELECT user_id FROM users");
$get_users->execute();
$result_users = $get_users->fetchAll(PDO::FETCH_ASSOC);
if(count($result_users) > 0) {
  foreach($result_users as $row) {
    $user_id = $row['user_id'];
  }
}

 

[requinix]

First of all, pulling every single user in the table is silly. Your starting query needs to be one that returns to you the join date and the number of users from that date. It'll involve a GROUP BY. Possibly a DATE_FORMAT too, if your join date is actually a join date/time.

Once that's ready,

Do you care about having the individual day counts too? If so then feed all these numbers into an array and average it.
If you don't care about the individual counts then take the query that gets each day, wrap it in a subquery, and in the outer query do an average over it. So the final query is an average of another query.

[Strider64]

If I was tackling the problem I would do something like this:

        $stmt = static::pdo()->prepare("SELECT count(user_id) FROM users WHERE joined_date = ?");
        $stmt->execute(['joined_date']);
        $result = $stmt->fetchColumn();
        return $result;

then I would either cycle through the database table with some kind of loop or set up a daily maintenance routine where I store the results. Of course you can do averages or what have you as it's just simple math in either case.

The first option is what I would do as I wouldn't have to go about storing and setting up additional stuff.  

Edited August 17, 2020 by Strider64
corrected something

[requinix]

3 hours ago, Strider64 said:

If I was tackling the problem I would do something like this:

Are you saying you would set up a loop in PHP of all the dates, and execute that query for each one?

[Strider64]

5 hours ago, requinix said:

Are you saying you would set up a loop in PHP of all the dates, and execute that query for each one?

I probably should had explain better. I would just take a range of dates (for example of week in an array) and loop through the dates. Unless it's important to save the data (which I personally don't there would be) then just store that data in another database table. 

Edited August 17, 2020 by Strider64

[Barand]

Given this data, for example

+---------+----------+------------+             +---------+----------+------------+
| user_id | username | created    |             | user_id | username | created    |
+---------+----------+------------+             +---------+----------+------------+
|       1 | Kim      | 2020-08-12 |             |      21 | Emma     | 2020-08-10 |
|       2 | Gerald   | 2020-08-12 |             |      22 | Fenella  | 2020-08-13 |
|       3 | Irene    | 2020-08-11 |             |      23 | Charles  | 2020-08-12 |
|       4 | Naomi    | 2020-08-11 |             |      24 | Martin   | 2020-08-10 |
|       5 | Sally    | 2020-08-12 |             |      25 | Roy      | 2020-08-13 |
|       6 | Olivia   | 2020-08-11 |             |      26 | Carol    | 2020-08-11 |
|       7 | Kate     | 2020-08-10 |             |      27 | Graham   | 2020-08-10 |
|       8 | Wanda    | 2020-08-13 |             |      28 | Keith    | 2020-08-13 |
|       9 | Debbie   | 2020-08-12 |             |      29 | Jenny    | 2020-08-10 |
|      10 | Cuthbert | 2020-08-11 |             |      30 | Barbara  | 2020-08-13 |
|      11 | David    | 2020-08-13 |             |      31 | Len      | 2020-08-10 |
|      12 | Emily    | 2020-08-11 |             |      32 | Ken      | 2020-08-10 |
|      13 | Kevin    | 2020-08-12 |             |      33 | Andrew   | 2020-08-13 |
|      14 | John     | 2020-08-12 |             |      34 | William  | 2020-08-10 |
|      15 | Adam     | 2020-08-12 |             |      35 | Oscar    | 2020-08-12 |
|      16 | Henry    | 2020-08-11 |             |      36 | Neil     | 2020-08-10 |
|      17 | Liz      | 2020-08-13 |             |      37 | Daniel   | 2020-08-12 |
|      18 | Glynn    | 2020-08-12 |             |      38 | Victor   | 2020-08-11 |
|      19 | Georgina | 2020-08-13 |             |      39 | Amanda   | 2020-08-13 |
|      20 | Harry    | 2020-08-10 |             |      40 | Sarah    | 2020-08-10 |
+---------+----------+------------+             +---------+----------+------------+

... we can get the daily totals with this query

mysql> SELECT created
    ->      , COUNT(*) as total_joined
    -> FROM user
    -> GROUP BY created;
+------------+--------------+
| created    | total_joined |
+------------+--------------+
| 2020-08-10 |           11 |
| 2020-08-11 |            8 |
| 2020-08-12 |           11 |
| 2020-08-13 |           10 |
+------------+--------------+

To get the average, we make this query a table subquery (so it behaves like a temporary table), so...

mysql> SELECT AVG(total_joined) as av_per_day
    -> FROM (
    ->         SELECT created
    ->              , COUNT(*) as total_joined
    ->         FROM user
    ->         GROUP BY created
    ->      ) tots;
+------------+
| av_per_day |
+------------+
|    10.0000 |
+------------+