SQL report of subscribers and their associated revenue A user can have many subscriptions, and a subscription belongs to one plan.  The plans table tells us a subscription's cost.  Is the query below the correct one for figuring out the number of subscriptions purchased by a user with an "ongoing" status and the total revenue brought in each day  between Jan 1 and Mar 4, 2016? 

Essentially, I'm creating a business report via SQL.  Each "day" bucket should contain the number of ongoing subscriptions created that day and the the total revenue brought in by those subscriptions that day.

select
    extract(MONTH from subscriptions.created_at) as mth,
    extract(DAY from subscriptions.created_at) as dy,
    count(subscriptions.id) as subs,
    round(sum(plans.cost)::numeric, 2) as revenue
from users
JOIN subscriptions ON subscriptions.user_id = users.id
JOIN plans ON subscriptions.plan_id = plans.id
where subscriptions.subscription_status = 'ongoing'
and subscriptions.created_at &gt;= '01/01/16'
and subscriptions.created_at &lt;= '03/04/16'
group by MTH, DY order by MTH, DY
I think this looks very good, but I would make a few small changes:


Instead of getting separate columns for month and day, use one column with date_trunc('day', subscriptions.created_at). Then you still have a timestamp that you can format, sort by, etc. Also you will avoid problems when someone reports on a range crossing New Years.
Be aware that binning timestamps into days involves time zones. Right now you're cutting days using UTC, which may be fine, but just be aware of it if you want to do something else.
You don't seem to be using the users table at all, so you could just leave that out completely.
Watch out for your edge conditions. Your query will include all of 1/1 but only the first instant of 3/4. It is more common and probably better to do &gt;= and &lt;. Otherwise if something happens in that first instant it will be double-counted with the next range (e.g. 3/4 to 6/1).