We recently hired someone to help us with some data analysis and reporting requirements. We're not really big enough to claim that what we needed was a Data Scientist, but that is effectively what we were looking for. And as is our wont, we created a simple test for applicants, designed to show some ability to extract meaning from data (by creating a histogram), and a working knowledge of SQL. We do most of our running reporting through Periscope, so being able to generate flat datasets from our relational database is an essential part of the job.

Testing candidates is always revealing, but in this case especially so - out of perhaps 20 candidates, only two nailed it - the rest either got the wrong answer, or the right answer the wrong way (for example, joining the two tables in Excel, using a vlookup function.) This got me thinking - is raw SQL becoming a lost art?

In this post, I'm going to demonstrate how to generate some fairly simple, but important, business indicators directly in SQL, in an attempt to show how useful it is, and why Excel is not always the answer (All of the data, and SQL scripts are available as a Gist.)

The raw data in this case is monthly sales - sitting neatly in a table with two columns - period and sales (the sales data was generated using a random percentage change over the previous month of between 80% and 150%).

The stats I'll produce are as follows:

1. Month-on-month growth (e.g. Jan-2014 to Feb-2014)
2. Year-on-year monthly growth (e.g. Jan-2014 to Jan-2015)
3. Annual Compound Monthly Growth Rate (CMGR) - the compound growth rate measured over the preceding 12-month period.
4. The total sales in the calendar year.

All of these are trivial to produce in a spreadsheet, which is why people do it this way. But even if it's trivial, it still involves extracting the data, loading it into Excel, and adding the functions. If you want to produce charts straight off of the data, you're going to want to calculate it in one query.

The table looks like this:

The output we are looking for looks like this:

Extract monthly sales

This one I am presuming we can all agree on:

SELECT period, sales FROM sales

Month-on-month growth

In order to get the MoM growth, we need to compare the current row with the value of sales in the previous row. This is done using a Window function (the docs are from Postgresql, but Window functions are also available in MSSQL and Oracle, apparently not in MySQL though?).

I'm not going to explain how exactly this works, as the docs above do a far more comprehensive job than I could, but suffice to say, a Window function consists of two parts - the function itself, and the Window over which it operates. A Window is a set of rows related to the current row. The following expression will return the value of sales in the preceding row to the current row:

LAG(sales, 1) OVER (ORDER BY period)

In this case, the function is LAG(sales, 1) and the Window is (ORDER BY period).

If you want the percentage change, it would be:

(sales - LAG(sales, 1) OVER (ORDER BY period))/LAG(sales, 1) OVER (ORDER BY period)

Overall, our query now looks like this:

SELECT
  period
  ,sales
  ,(sales - LAG(sales, 1) OVER (ORDER BY period))/LAG(sales, 1) OVER (ORDER BY period) as "MoM"
FROM sales

Note: we can simplify this a little, by putting the Window definition ((ORDER BY period)) at the end of the statement:

SELECT
  period
  ,sales
  ,(sales - LAG(sales, 1) OVER w)/LAG(sales, 1) OVER w as "MoM"
FROM sales
WINDOW w as (ORDER BY period)

Year-on-year growth

Using the technique above, we do exactly the same, but using 12 as the LAG value - because we want the 12th month preceding the current one. Adding this to the query we get:

SELECT
  period
  ,sales
  ,(sales - LAG(sales, 1) OVER w)/LAG(sales, 1) OVER w as "MoM"
  ,(sales - LAG(sales, 12) OVER w)/LAG(sales, 12) OVER w as "YoY"
FROM sales
WINDOW w as (ORDER BY period)

CMGR

This one is a little more complicated, but only because the equation itself is complicated:

((last_period_sales / first_period_sales) ^ (1 / periods)) – 1

In our case, we are calculating a running 12-month period CMGR, so we can hard-code periods as 12, and the first_period_sales as the LAG(sales, 12) value:

((sales / LAG(sales, 12) OVER w) ^ (1.0/12) - 1

Note: use 1.0/12 otherwise you'll get an integer/integer division, which will be rounded out to zero.

SELECT
  period
  ,sales
  ,(sales - LAG(sales, 1) OVER w)/LAG(sales, 1) OVER w as "MoM"
  ,(sales - LAG(sales, 12) OVER w)/LAG(sales, 12) OVER w as "YoY"
  ,(sales / LAG(sales, 12) OVER w) ^ (1.0/12) - 1 as "CMGR"
FROM sales
WINDOW w as (ORDER BY period)

Calendar year total

For this column, we want to calculate the total sales within the same calendar year as the current row, and that involves a new keyword - PARTITION.

If we were to use the sum aggregate window function over our existing Window ((ORDER BY period)), we would calculate the sum of all rows in the table, but this is not what we want - we want just those rows that match the year of the current row.

In order to extract the year, we use the date_trunc function:

date_trunc('year', period)

Now we want to extend our Window to restrict the rows to this value - which we do using PARTITION. This defines the Window set. The following expression creates a Window that contains only those rows that have the same year as the current row. This is what we want to sum:

WINDOW y as (PARTITION BY date_trunc('year', period))

The full expression now looks like this:

SELECT
  period
  ,sales
  ,(sales - LAG(sales, 1) OVER w)/LAG(sales, 1) OVER w as "MoM"
  ,(sales - LAG(sales, 12) OVER w)/LAG(sales, 12) OVER w as "YoY"
  ,(sales / LAG(sales, 12) OVER w) ^ (1.0/12) - 1 as "CMGR"
  ,SUM(sales) over y
FROM sales
WINDOW
  w as (ORDER BY period)
  ,y as (PARTITION BY date_trunc('year', period))

This has been a very rapid demonstration of Window functions in SQL - you'll have to Google the docs yourself if you want to dive in, but hopefully that has shown that it is possible to produce data directly from the database without resorting to PivotTables!