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

Extrapolating to the End of the Month in SQL

December 11, 2014

Our customers often come to us for help with advanced SQL queries. Recently, two of them asked us how to do an extrapolation in SQL.

These customers wanted to estimate what their sales would be toward the end of the month, but only had sales numbers for the first few days.

We were happy to help!

The Formula

There are many ways to make predictions. When the data is relatively smooth, one approximation is to simply multiply the current daily average out to the rest of the month:

The SQL

To fill out that formula we need the current sales sum, the number of days so far, and the number of days in the month. The sum is easy and the number of days so far is today’s date.

The last date of the month requires some SQL:

Posgres

select extract('day' from (date_trunc('month', now())
  + interval '1 month - 1 day'))

MySQL

select day(date(last_day(now())))

Now that we have the number of days in the current month, we can get the daily average and multiply the two together. First, the daily average:

Postgres

select sum(amount) / extract('day' from now()) as avg 
from sales
where created_at > date_trunc('month', now())

MySQL

select sum(amount) / day(now()) as avg 
from sales
where created_at > date_format(now() ,'%Y-%m-01')

Multiplying the daily average by the number of days in the month gives us our rough approximation:

Postgres

select (sum(amount) / extract('day' from now())) * 
  extract('day' from (date_trunc('month', now())
    + interval '1 month - 1 day')) as estimate
from sales
where created_at > date_trunc('month', now())

MySQL

select sum(amount) / day(now()) *
  day(date(last_day(now()))) as estimate
from sales
where created_at > date_format(now() ,'%Y-%m-01')

Now we can extrapolate what sales will be this month. Even better, the estimate will become more accurate as the month progresses and there are more days in the average!

This is a great companion chart for dashboards that are already computing the current month’s sales totals so far. Hope you enjoyed it!

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?