Mysql – How to get number of active users with one SQL-Query

galeragroup byMySQLselect

I am running a site similar to craigslist where users can create classifieds and write/reply messages. In order to get an idea of activity I would like to get the amount of active users per month.

The idea is to define an active user as one who has either registered, created/edited a classified or written a message to another user within the last 30 days.

There are 2 tables:

  1. classifieds

    advertiser_id | date_created | date_edited

  2. messages

    sender_user_id | recipient_user_id | timestamp | status

I am looking for an output like this:

year; month; n_active_users; n_users_created_classified; n_user_sent_msg; n_user_read_msg

A message is marked as read by status=4

The ids are all referring to the user table. So the same sender_user_id and advertiser_id indicate the same user.

I am not sure if this is even possible with just one query but I guess it is, I just don't know how.

From another post I have learned that this could be pointing into the right direction but I am still stuck:

SELECT
   EXTRACT(YEAR FROM date_created) AS yr,   -- for each year
   EXTRACT(MONTH FROM date_created) AS mth, -- & month combination 
   count(*) AS advertiser_id                     
FROM 
   classifieds c
GROUP BY yr, mth
ORDER BY yr DESC , mth DESC

I am using MySQL Galera 5.6.27-1trusty-log – (Ubuntu), wsrep_25.12

Best Answer

I don't know how to translate your tables into any of n_active_users; n_users_created_classified; n_user_sent_msg; n_user_read_msg. But here are some clues:

A simple query for the immediately preceding 30-ish days:

SELECT COUNT(DISTINCT user_id)
    FROM tbl
    WHERE timestamp > NOW() - INTERVAL 1 MONTH;

(Or maybe you would want - INTERVAL 30 DAY.)

This might work for n_user_read_msg:

SELECT COUNT(DISTINCT recipient_user_id) AS n_user_read_msg
    FROM messages
    WHERE timestamp > NOW() - INTERVAL 1 MONTH;
      AND status = 4;

If you want the last thing for each month, then do:

SELECT LEFT(timestamp, 7) AS yyyy_mm,
       COUNT(DISTINCT recipient_user_id) AS n_user_read_msg
    FROM messages
    WHERE status = 4
    GROUP BY LEFT(timestamp, 7);
Related Question