Tracking revenue is crucial to understanding the health of your business. Whether your long-term growth is declining, has plateaued, or is heading upwards helps you define your long term strategy and goals. Many sales cycles are biased towards weekends, beginning-of-month, or end-of-quarter deals, and it can be hard to know day-to-day if your sales numbers are trending on target.
We track several KPIs as they progress over the course of the month. In this post, we are going to look at monthly recurring revenue (MRR) historically across the year, and segment each month by its progress up to the current date.

Constructing the Query
On today’s date, September 20th, we want to be able to compare our progress to July 20th and August 20th. We can add the context for the MRR at the end of the month by selecting the revenue made up to the 20th for each month, and contrast it against the total end-of-month revenue.
We have a table subscription_history with four columns
- dte: date
- mrr: MRR from the customer on date dte
- yesterday_mrr: MRR from the customer on the previous day from dte
- first_spend: true when this is the first dte for a new customer
We want to make a bar chart with the upgrade and new recurring revenue generated for each month, and segment the bars into two slices: revenue generated to the same day-of-the-month as today’s date, and revenue generated afterward.
We start off by creating two CTEs, new_cust will be revenue from new customers, and upgrades will be positive upgrades. We are only going to look at rows where MRR is greater than the previous MRR to get a snapshot of revenue without considering customers who downgraded their payment plan.
with
new_cust as (
select
dte
, sum(mrr) new_mrr
from
subscription_history
where
first_spend = true
group by
1
)
, upgrades as (
select
dte
, sum(mrr - coalesce(yesterday_mrr, 0)) upgrade_mrr
from
subscription_history
where
first_spend = false
and mrr > coalesce(yesterday_mrr, 0)
group by
1
)
We select upgrade_mrr by subtracting mrr on dte from yesterday_mrr. The yesterday_mrr value can be null, so we use the coalesce function to return 0 when yesterday_mrris null.
Next, we will combine our two CTEs into a base table, joining by date.
, base as (
select
dte
, coalesce(upgrade_mrr, 0) upgrade_mrr
, coalesce(new_mrr, 0) new_mrr
from
new_cust full outer join upgrades using (dte)
where
dte <= date_trunc('day', now())
and dte > now() - interval '5 months'
)
Now we will aggregate table base by month, taking the sum of the upgrade and new customer MRR for each month. We compute both the total for each month, and the sum for each month up to the current date using extract(‘d’ from now()).
, agg_totals as (
select
date_trunc('month', base.dte) agg
, sum(upgrade_mrr) agg_tot_upgrade_mrr
, sum(new_mrr) agg_tot_new_mrr
from
base
where
date_trunc('month', base.dte) < date_trunc('month', now())
group by
1
)
, agg_to_date_numbers as (
select
date_trunc('month', base.dte) agg
, sum(upgrade_mrr) agg_td_upgrade_mrr
, sum(new_mrr) agg_td_new_mrr
from
base
where
extract('d' from base.dte) <= extract('d' from now())
group by
1
)
We can combine the aggregation tables to create the summary table by using a full outer join on our two aggregation tables.
, pre_labels as (
select
agg
, agg_tot_upgrade_mrr
, agg_tot_new_mrr
, agg_td_upgrade_mrr
, agg_td_new_mrr
, agg_tot_upgrade_mrr - coalesce(agg_td_upgrade_mrr,0)
as agg_upgrade_mrr_final
, agg_tot_new_mrr - coalesce(agg_td_new_mrr,0)
as agg_new_mrr_final
from
agg_to_date_numbers
full outer join agg_totals using (agg)
)
Finally, we select the rows we want to plot by limiting our data to the last five months.
select
agg "Month"
, agg_upgrade_mrr_final "Upgrade MRR final"
, agg_new_mrr_final "New MRR final"
, agg_td_upgrade_mrr "Month to date upgrade MRR"
, agg_td_new_mrr "Month to date new MRR"
from
pre_labels
where
agg > date_trunc('month', now()) - interval '5 month'

We plot our table as a bar chart and get our final result!

Each bar represents the total new MRR that month. Based on this graph our monthly growth looks healthy! This month is on track with new customers and doing above average with plan upgrades. Happy growth 🙂