Jump to content

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


imgrooot

Recommended Posts

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'];
  }
}

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Strider64
corrected something
Link to comment
Share on other sites

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 by Strider64
Link to comment
Share on other sites

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 |
+------------+

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.