1

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:

  • connections(id, connection​_date, user_id)
  • users(user_​id, user_group, creation_date)

I am using MySQL.

Thank you in advance.

Sat Cit Ananda
  • 230
  • 4
  • 10
  • 5
    Please provide sample data and desired results. What is the "percentage" a percentage of? – Gordon Linoff Feb 15 '19 at 15:43
  • @GordonLinoff the percentage is the percewntage of users in a user_group who connected in a given month – Sat Cit Ananda Feb 15 '19 at 16:01
  • @NicoHaase I don't know how to express the percentage of users in a user_group who connected in a given month. Specifically I don't know how to get the percentage count. – Sat Cit Ananda Feb 15 '19 at 16:03
  • 1
    Lastly, define "connected", because to achieve this you'll need to track (at least) a record per monthly connection. If you're only tracking the last active time, you'll get the results of people who connected that month (and never again after); if you're comparing to join date, it'll be incomparable to "connecting" in that month. – Rogue Feb 15 '19 at 17:04
  • @Rogue each line in the table connections is a connection event. I do not really care about the creation_date but I mostly want to group the connection events by month and user_group. – Sat Cit Ananda Feb 15 '19 at 17:09

3 Answers3

1

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);
douglas.kirschman
  • 136
  • 1
  • 7
  • 1
    hi @douglas.kirschman thanks for the answer. I am using MySQL and I don't think it supports the DATEPART. I will try using DATE_FORMAT. – Sat Cit Ananda Feb 15 '19 at 17:32
1

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

Jannes Botis
  • 11,154
  • 3
  • 21
  • 39
0

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

code lover
  • 151
  • 1
  • 2
  • 9