CODEWITHSUNDEEP
mysqlsql
Sat Cit Ananda
Sat Cit Ananda

Reputation: 240

MYSQL - how to get the percentage of connected users grouped by month

I am trying to write an efficient query to get the percentage of connected users grouped by month and user_group given the following 2 tables:

I am using MySQL.

Thank you in advance.

Upvotes: 1

Views: 513

Answers (3)

Jannes Botis
Jannes Botis

Reputation: 11242

I assume connections.connection​_date & users.creation_date are of datetime.

To get the percentage of connected users for a month against the total users as of to that month, use:

SELECT u.user_group,  DATE_FORMAT(`c`.`connection​_date`, "%M %Y") AS month,
COUNT(DISTINCT u.`user_id`) / (SELECT COUNT(`user_id`) FROM users WHERE users.creation_date <= adddate(last_day(`c`.`connection​_date`), 1) AND users.user_group = u.user_group) AS percentage,
COUNT(DISTINCT u.`user_id`) as loggedThisMonth,
(SELECT COUNT(`user_id`) FROM users WHERE users.creation_date <= adddate(last_day(`c`.`connection​_date`), 1) AND users.user_group = u.user_group) AS totalRegisteredToMonth
FROM connections c LEFT JOIN users u ON c.`user_id` = u.`user_id`
GROUP BY u.user_group, DATE_FORMAT(`c`.`connection​_date`, "%M %Y")
ORDER BY DATE_FORMAT(`c`.`connection​_date`, "%Y %m"), u.user_group ASC

This works by:

  • counting the DISTINCT users.user_​id that have connected each month, thus preventing recounted users with multiple connections in a month COUNT(DISTINCT u.user_​id)
  • using a subselect to calculate the registered users of a user group till that month (SELECT COUNT(user_id) FROM users WHERE users.creation_date <= adddate(last_day(c.connection​_date), 1) AND users.user_group = u.user_group)

Reference:

adddate(last_day(`c`.`connection​_date`), 1)

return the 1st day of the next month date

Upvotes: 1

douglas.kirschman
douglas.kirschman

Reputation: 146

You basically need to join the 2 tables on the user id column. Then simply group by user_group and month of connection date. You can use DATEPART in SQL Server to do that.

Something like this:

SELECT u.user_group, DATEPART(MONTH, c.connection_date) AS month, COUNT(c.id)
FROM connections c INNER JOIN users u
  ON c.user_id = u.user_id
GROUP BY u.user_group, DATEPART(MONTH, c.connection_date);

Upvotes: 1

code lover
code lover

Reputation: 149

Check out this link: http://thisinterestsme.com/php-calculate-percentage-of-number/.

And here is the sql:

<?php

$conn = mysqli_connect("localhost", "UserName", "Password", "Database");
for($m=01;$m < 13; $m++) {

    $sql = "SELECT user_group FROM users WHERE creation_date LIKE '%" . $m . "%'";
    $result = $conn->query($sql);
    $users = [];
    if ($result->num_rows > 0) {
        while($row = $result->fetch_assoc()) {
            $users[] = $row["user_group"];
        };
    };
    $matchUserAmnt = count($users);
    $sql = "SELECT * FROM users";
    $result = $conn->query($sql);
    $user_amnt = $result->num_rows;
    //My number is the amount of users.
    $percent = round($matchUserAmnt / ($user_amnt / 100),2);
    echo "The percent of users who joined in the month " . $m . "is " . $percent;


};

Feel free to edit

Upvotes: 0

Related Questions