Skip to content

Instantly share code, notes, and snippets.

@pdc
Last active August 29, 2015 14:08
Show Gist options
  • Save pdc/294d4e0d728ca1476db6 to your computer and use it in GitHub Desktop.
Save pdc/294d4e0d728ca1476db6 to your computer and use it in GitHub Desktop.
How many users added per day, including rows for days with no registrations

Wanted to make a table of net user growth for further analysis in some spreadsheet or other.

The obvious query (in the snippet it is the inner query) only generates rows for days with at least one registration. For this to make nice charts, we need to add blank rows with the missing dates. The set returning function generate_series creates the equivalent of a temporary table to join with.

select generate_series date, users_added
from
generate_series('2013-03-05'::timestamp, '2014-10-29', '1 day')
left join (
select date_trunc('day', date_joined) date_joined, count(id) users_added
from registration_registereduser
where is_active
group by 1
) xs on generate_series = date_joined
order by 1;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment