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.

    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
  • \$\begingroup\$ I guess you're assuming the range will never span more than a single year? \$\endgroup\$
    – shawnt00
    Mar 15 '16 at 19:53
  • \$\begingroup\$ Not at the moment but next year...if I needed to write subscriptions.created_at <= '05/04/17', how would that change things? \$\endgroup\$
    – Nona
    Mar 15 '16 at 22:14
  • \$\begingroup\$ 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. \$\endgroup\$
    – shawnt00
    Mar 15 '16 at 22:30
  • \$\begingroup\$ Yes, so then I'd want to extract a year then and group by that eventually is what you're saying \$\endgroup\$
    – Nona
    Mar 15 '16 at 22:33
  • \$\begingroup\$ You could certainly do that. I think Paul's suggestion to use date_trunc() makes more sense. \$\endgroup\$
    – shawnt00
    Mar 15 '16 at 22:37

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 >= 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).


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.