Arnab Arnab - 21 days ago 7
SQL Question

monthly grouping users who have performed an action

I have two tables..

1)Users
With Columns:

userid createddate
1 01/01/2016
2 02/01/2016
3 03/01/2016


2)Plays
With Columns:

userid playdate
1 07/03/2016
2 06/04/2016


Dates shown above are in MM/dd/yyyy format
From table Plays I got a table enrichedplays which has only one top row per userid

The users table has some 5 mil rows and enrichedplays has around 3mil rows.
There are a lot of users who though are in Users are not in Plays.

I need to group monthly Users (on MONTH(createddate)) with userid who are present in plays.

What I mean is in month January there were 10 users in Users table.
Of these 10 users only 5 are present in Plays table and it does not matter for which month they are present.

So, in my resultant data i need 5 against January.

So, for above sample data the result would be

Jan-2016 1
Feb-2016 1
March-2016 0


Though, I'm using SQL SERVER, it might change and so would appreciate answers which are standard sql.

Answer

Try below query,

SELECT createddate, SUM(CASE WHEN p.userid IS NOT NULL THEN 1 ELSE 0 END) sum_value
FROM users u
 LEFT JOIN plays p
  ON u.userid = p.userid
GROUP BY createddate;

Note: Kindly convert the createddate to your required format.

Comments