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

The High-Performance SQL Blog

Comparing Hourly Revenue in SQL

Comparing Hourly Revenue in SQL
October 16, 2014

For many businesses, understanding revenue per hour is as important as revenue per day. It changes throughout the day with the number of people engaging with the product.

One powerful way to track how today is trending vs. the past few days is to overlay the hourly data of each day:

Joining tables

By overlaying each day as it’s own line, we can easily see which hours get the most revenue and how each day compares the the last.

And unlike a simple daily revenue bar chart, it’s easy to understand the current day’s progress before the day is over.

Building Hourly Revenue

To make this hourly trend chart, we’ll start with daily revenue:

select [created_at:pst:date], sum(price)
from transactions
where [created_at=5days]
group by 1 order by 1

Which looks like this:

The square-bracket syntax above is a feature of Periscope. These two examples make it easier to work with time zones since the charts need to reflect PST but the database is in UTC. This database is PostgreSQL, so here’s what Periscope runs in their place:

Date Aggregation

[created_at:pst:date] converts created_at from UTC timestamp to a PST date:

date_trunc(
  'day'
  ((created_at + interval '-7 hour'))::timestamp
)::date

#### Date Restriction **`[created_at=5days]`** restricts **`created_at`** to the last 5 days in PST and maintains the sargability of the column's index by keeping all the math on the right hand side:

created_at >= (
  ((now() + interval '-7 hour')::date + interval '7 hour')
   - interval '4 day'
) and created_at < 
   (now() + interval '-7 hour')::date + interval '31 hour'

### Splitting Hourly Revenue by Day Next we split each day by hour to get the data we need for the lines:

select 
  [created_at:pst:date], 
  [created_at:pst:hour_of_day], 
  sum(price)
from transactions
where [created_at=5days]
group by 1, 2 order by 1, 2

Which looks like this:

The additional square bracket **`[created_at:pst:hour_of_day]`** extracts the hour from the PST-adjusted timestamp. It translates to:

extract(hour from ((created_at + interval '-7 hour'))::timestamp)

Rotating to Hourly Revenue by Day

The query is almost there! We want the hours to be the X-axis and the days the labels. Just switch the first two columns (or change the segmentation column) and make it a line chart:

select 
  [created_at:pst:hour_of_day],
  [created_at:pst:date], 
  sum(price)
from transactions
where [created_at=5days]
group by 1, 2 order by 1, 2

![Joining tables](//s3.amazonaws.com/periscope-blog-assets/hourly_revenue_by_day.png) That's it! The full query with Periscope syntax expanded becomes:

select 
  extract(hour from (
    (created_at + interval '-7 hour'))::timestamp
  ), 
  date_trunc('day', (
    (created_at + interval '-7 hour'))::timestamp
  )::date, 
  sum(price)
from transactions
where 
  created_at >= (
    ((now() + interval '-7 hour')::date + interval '7 hour'
    - interval '4 day')
  and created_at < (
    now() + interval '-7 hour'
  )::date + interval '31 hour'
group by 1, 2 order by 1, 2

A great companion chart to this one is the cumulative version of the same data. It shows how the the days stack up against each other and makes it obvious when the current day under or over-performing: 

Joining tables

The same method for building the hourly charts works for other intervals too! Track daily trends by month or monthly trends by year, or any interval that makes sense for your business!

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?