# 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 >= '01/01/16'
and subscriptions.created_at <= '03/04/16'
group by MTH, DY order by MTH, DY

• I guess you're assuming the range will never span more than a single year?
– shawnt00
Mar 15 '16 at 19:53
• Not at the moment but next year...if I needed to write subscriptions.created_at <= '05/04/17', how would that change things?
– Nona
Mar 15 '16 at 22:14
• Once you have a range that spans more than a year you'll start having multiple dates that fall into the same "MTH and DY" bucket. Also by stripping out the year you can no longer order the results reliably: Dec 31, 2015 comes before Mar 4, 2016, but your order by won't handle it.
– shawnt00
Mar 15 '16 at 22:30
• Yes, so then I'd want to extract a year then and group by that eventually is what you're saying
– Nona
Mar 15 '16 at 22:33
• You could certainly do that. I think Paul's suggestion to use date_trunc() makes more sense.
– shawnt00
Mar 15 '16 at 22:37

• 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.
• 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 >= and <. Otherwise if something happens in that first instant it will be double-counted with the next range (e.g. 3/4 to 6/1).