user3906778 user3906778 - 3 months ago 8
SQL Question

joining three different tables with date columns and group by date

I'm currently working with three different tables tha look like this

user:

user_id|createtime
-----------------------------
001 2016-07-20
002 2016-08-15
003 2016-08-05
004 2016-08-23

object:

objc_id|createtime|user_id
-----------------------------
001 2016-07-20 001
002 2016-07-15 001
003 2016-08-05 002
004 2016-08-23 001
005 2016-08-19 003
006 2016-08-21 004
007 2016-08-22 004

event:

event_id|createtime|objc_id
-----------------------------
001 2016-08-25 001
002 2016-08-26 004
003 2016-08-28 002
004 2016-08-27 005
005 2016-08-30 003


I've already made select statements which tell me how many new users, objects or events were created on a certain date:

select createtime, count(user_id) as new_user from
user where createtime >= CURRENT_DATE - INTERVAL '30 days' group by
createtime;

select createtime, count(objc_id) as new_objc from
object createtime >= CURRENT_DATE - INTERVAL '30 days' group by createtime;

select createtime, count (event_id) from
event where createtime >= CURRENT_DATE - INTERVAL '30 days'
group by createtime;


Now I want make one select statement, where all the numbers of new objects, users, and events are shown and group them by their creationtime.

However I cant get a solution for this. The goal to achieve would look something like this:

createtime|new_user|new_objc|new_event
---------------------------------------
2016-07-15 1
2016-07-20 1 1
2016-07-22
2016-07-24
2016-08-05 1 1
2016-08-15 1
2016-08-19 1
2016-08-21 1
2016-08-22 1
2016-08-23 1 1
2016-08-25 1
2016-08-26 1
2016-08-27 1
2016-08-28 1
2016-08-30 1


... aso, of course some dates could appear more than once for one group, so that the count will be 1 or higher.

The goal is to see if marketing increases the number of new users, buying of objects and attending to an event over time (you need the object for the event).

Has anybody a solution for this? Or do I have to go with seperate result tables?

Thank you very much in advance.

Answer
SELECT
    Createtime
    ,COUNT(DISTINCT user_id) as new_user
    ,COUNT(DISTINCT objc_id) as new_objc
    ,COUNT(DISTINCT new_event) as new_objc
FROM (
    SELECT Createtime, user_id, CAST(NULL AS INT) as objc_id, CAST(NULL as INT) as event_id
    FROM
       users
    WHERE createtime >= CURRENT_DATE - INTERVAL '30 days'
    UNION ALL
    SELECT Createtime, CAST(NULL AS INT) as user_id, objc_id, CAST(NULL as INT) event_id
    FROM
       object
    WHERE createtime >= CURRENT_DATE - INTERVAL '30 days'
    UNION ALL
    SELECT Createtime, CAST(NULL AS INT) as user_id, CAST(NULL AS INT) as objc_id, event_id
    FROM
       event
    WHERE createtime >= CURRENT_DATE - INTERVAL '30 days'
) t
GROUP BY
    Createtime
ORDER BY
    Createtime

When you first look at your question it looks like you need a join but the reality is you need to union all and count(). I am counting DISTINCT which should be redundant because user_id, objc_id, event_id appear to be unique primary keys.

Comments