Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE activity (

id text,

userid text,

createdat timestamp,

username text

);

INSERT INTO activity VALUES

('1d658a','4957f3','20161206 21:16:35'::timestamp,'Tom Jones')

, ('3a86e3','684edf','20161203 21:16:35'::timestamp,'Harry Smith')

, ('595756','582107','20161226 21:16:35'::timestamp,'William Hanson')

, ('2c87fe','784723','20161207 21:16:35'::timestamp,'April Cordon')

, ('32509a','4957f3','20161220 21:16:35'::timestamp,'Tom Jones')

, ('72e703','582107','20170101 21:16:35'::timestamp,'William Hanson')

, ('6d658a','582107','20161206 21:16:35'::timestamp,'William Hanson')

, ('5c077c','5934c4','20161206 21:16:35'::timestamp,'Sandra Holmes')

, ('92142b','57ea5c','20161215 21:16:35'::timestamp,'Lucy Lawless')

, ('3dd0a6','5934c4','20161204 21:16:35'::timestamp,'Sandra Holmes')

, ('43509a','4957f3','20161120 21:16:35'::timestamp,'Tom Jones')

, ('85142b','57ea5c','20161115 21:16:35'::timestamp,'Lucy Lawless')

, ('7c87fe','784723','20170107 21:16:35'::timestamp,'April Cordon')

, ('9c87fe','784723','20170207 21:16:35'::timestamp,'April Cordo')

;

14 rows affected

WITH cte AS (

SELECT date_trunc('month', createdat) AS mon

, count(DISTINCT username) AS ct

FROM activity

GROUP BY 1

)

SELECT to_char(t1.mon, 'MON YYYY') AS month

, t1.ct AS users_this_month

, t2.ct AS users_previous_month

FROM cte t1

LEFT JOIN cte t2 ON t2.mon = t1.mon - interval '1 mon'

ORDER BY t1.mon;