Blog

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!