Using CTEs and Unions to Compute Running Totals

Segmented totals

A common pattern in SQL analysis is to use multiple groupings to show grouped totals over time. For example, here’s a simple query that computes total sales by salesperson over time:

select 
  date_trunc('month', plan_start), 
  users.name,
  sum(purchase_price)
from payment_plans join users 
  on users.id = payment_plans.sales_owner_id
group by 1, 2

In your favorite visualization tool, this will give a sales graph that looks like this:

This is a great graph for measuring salesperson performance, but it makes you work hard to figure out whether the overall business is growing. There ought to be a way to see both the individual and total performance from the same query in the same graph.

Bundling the query in a CTE

To get there, let’s start by taking our original query and wrapping it in a CTE:

with individual_performance as (
  select 
    date('month', plan_start) m, 
    users.name salesperson,
    sum(purchase_price) revenue
  from payment_plans join users 
    on users.id = payment_plans.sales_owner_id
  group by 1, 2
)
select 
  m, 
  salesperson, 
  revenue 
from individual_performance

For now, this will give the same results. Note that we’ve named each column and made sure they’re selected in a certain order. That’ll matter as we proceed to the next step.

Adding in the totals

Now let’s add in the totals:

with individual_performance as (
  select 
    date('month', plan_start) m, 
    users.name salesperson,
    sum(purchase_price) revenue
  from payment_plans join users 
    on users.id = payment_plans.sales_owner_id
  group by 1, 2
)
select 
  m, 
  salesperson, 
  revenue 
from individual_performance
union all 
select 
  m, 
  'Total', 
  sum(revenue) 
from individual_performance 
group by 1

The last line goes back to our individual_performance CTE a second time, and this time selects the sum of the revenue each month. Using union all, we append that to the end of the resulting table.

A sampling of lines from the result set looks like this:

For each month, in addition to each salesperson’s number, we now have the total number as well!

With a few tweaks in our visualization suite, we can visualize the individual performance and the total performance together like so:

Voilà! You can use this trick with any segmented total query to add a running total.

Tags: data team