Periscope Data
The world’s fastest visualization platform for data analysts.

The High-Performance SQL Blog

Day-Over-Day Changes Without Window Functions

Day-Over-Day Changes Without Window Functions
November 5, 2014

Day-Over-Day Changes

A couple of months ago, we showed you how to compute day-over-day changes using window functions. For high-growth companies, the growth rate is at least as important as the absolute value of any metric, so this technique is very handy.

Unfortunately, window functions are only available on certain databases. So we thought it was high time to come back and show how it’s done on other databases. In this post, we’re going to compute day-over-day changes with one hand tied behind our back — without window functions.

Daily Revenue

Let’s start with a handy group-and-count of revenue:

select date(created_at), sum(price)
from purchases
group by 1 

Run over a week of data and viewed in the world’s best visualization tool, that query looks like this:

Joining Revenue To Itself

To start, let’s put our revenue calculation in a with clause so we can reuse it:

with daily_revenue as (
  select date(created_at) d, sum(price) rev
  from purchases
  group by 1
)

Now let’s join daily_revenue to itself. We want one copy of the rev column that represents today’s revenue, and another copy that trails by one day. Here goes:

with daily_revenue as (
  select date(created_at) d, sum(price) rev
  from purchases
  group by 1
)
select 
  today_revenue.d, 
  today_revenue.rev, 
  yesterday_revenue.rev as prev_rev 
from daily_revenue as today_revenue 
left join daily_revenue as yesterday_revenue
  on yesterday_revenue.d = today_revenue.d - 1

The magic happens with on yesterday_revenue.d = today_revenue.d - 1. This specifies that the second copy of daily_revenue — the copy named yesterday_revenue — should be behind today_revenue by one day.

Also, notice the left join: This includes the first day in the results. Otherwise, since it has no “yesterday”, it would fail the join condition and get excluded!

Here are the results:

Perfect! For every day d, we have that day’s revenue and the previous day’s revenue in that day’s row.

Putting It All Together

Now that we have daily revenue and the previous day’s revenue together in each row, calculating growth is a simple matter of calculating the difference, and dividing that difference by the previous day’s revenue!

Here’s the full SQL:

with daily_revenue as (
  select date(created_at) d, sum(price) rev
  from purchases
  group by 1
)
select 
  d, 
  rev, 
  (rev - prev_rev)::float / prev_rev as growth_rate 
from (
  select 
    today_revenue.d, 
    today_revenue.rev, 
    yesterday_revenue.rev as prev_rev 
  from daily_revenue as today_revenue 
  left join daily_revenue as yesterday_revenue
    on yesterday_revenue.d = today_revenue.d - 1
) revenue_by_day

Notice how we subselect the previous calculation, and wrap it in the day-over-day growth calculation.

Now we’ve got daily revenue and its growth rate together on one graph:

Voilà! Even at tens of millions of dollars in daily revenue, we’re maintaining a 25-45% growth rate. This company is doing quite well.

Haven't tried Periscope Data yet?
Start a trial and we’ll send you one of our famous coffee mugs.
Read More
Haven’t tried Periscope Data yet?