No items found.

# Day-Over-Day Changes Without Window Functions

### 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

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.

Tags: