# SQL Window Functions to Pass a Data Analytics Interview (Opinionated SQL Series Part 2/N)

I’ve phone screened and interviewed a lot of candidates that have gone through data analytics or data science coursework, and rate themselves quite highly on their SQL skills, but then manage to not be able to construct a simple Top N query. This is typically an automatic pass.

SQL is, and has been, the lingua franca of data. If you’re in Finance, you know Excel like the back of your hand, or better. If you’re “in data”, you should know SQL the same way.

Oh, you say SQL isn’t as important anymore in a world of machine learning and ~~noodle~~ neural networks? Apparently you haven’t had an interview with companies such as Netflix or Facebook or Amazon in a while then.

Let’s look at this post as a kind of cheat sheet to at least get past that stage of the data analytics interview. Or, if you’re a hiring manager, feel free to use this is a tech screen checklist.

## Setup

All of these examples are drawn from real examples I’ve used in projects. Many times doing this in your database is the easiest, fastest and best way to do this in production. Sorry, pandas.

(If you’re looking for more SQL for data warehouse queries or a refresher, I have something here for you.)

For all examples, let’s assume we’re working with a Kimball style dimensional data warehouse, where we have some sales data in a table called `fct_sales`

, product data in `dim_product`

and store data in `dim_store`

.

`fct_sales`

sample data:

```
| txn_date | product_key | store_key | sales_amt | sales_qty |
|------------|-------------|-----------|-----------|-----------|
| 2018-01-01 | 12 | 6 | 125 | 5 |
| 2018-01-01 | 23 | 5 | 154 | 4 |
| 2018-01-01 | 19 | 1 | 178 | 5 |
...
| 2018-05-31 | 8 | 2 | 186 | 6 |
| 2018-05-31 | 18 | 4 | 165 | 8 |
| 2018-05-31 | 12 | 4 | 87 | 12 |
```

`dim_product`

sample data:

```
| product_key | product_name |
|-------------|--------------|
| 8 | Product 8 |
| 9 | Product 9 |
| 10 | Product 10 |
...
| 22 | Product 22 |
| 23 | Product 23 |
```

`dim_store`

sample data:

```
| store_key | store_name |
|-----------|------------|
| 1 | Store 1 |
| 2 | Store 2 |
| 3 | Store 3 |
| 4 | Store 4 |
| 5 | Store 5 |
| 6 | Store 6 |
```

## Top N by Something

As handy as it is to do Top N in the BI layer, it’s not always an option on every platform (looking at you *Mode*), so it’s good to know how to do this in pure SQL.

### Let’s first look for the Top 20 Products in terms of Sales:

```
-- first we need to aggregate our large fact table to the grain of interest,
-- in this case sales qty by product, over some time frame
-- (because we always want a date in DW World)
with sales as
(
select
p.product_name,
sum(f.sales_qty) as sales_qty
from
fct_sales f
join
dim_product p on f.product_key = p.product_key
where
f.txn_date between
'some_date' and
'some_other_date'
group
1
),
-- then we rank the products using a dense_rank() to avoid ties
-- (experiment with different ranking functions here, use your own business rules)
ranked as
(
select
product_name,
sales_qty,
dense_rank() over(order by sales_qty desc) as sales_rank
from
sales
)
-- then pick out the top N products (you could even do this in the BI layer)
select
sales_rank,
product_name,
sales_qty
from
ranked
where sales_rank <= 20
```

Most candidates I talk to approach this without using a ranking function and end up doing something using `limit`

, which, in my opinion, is a somewhat lazy and definitely not scalable solution - as we’ll see below when we extend this question.

### How about the Top 20 Products in terms of Sales by Store?

This is where the `limit N`

approach usually falls apart, but using the more generalizable approach above we can just extend what we were doing, like this:

```
-- first we need to aggregate our large fact table to the grain of interest,
-- in this case sales qty by product by store, over some time frame
-- (because we always want a date in DW World)
with sales as
(
select
p.product_name,
s.store_name,
sum(f.sales_qty) as sales_qty
from
fct_sales f
join
dim_product p on f.product_key = p.product_key
join
dim_store s on f.store_key = s.store_key
where
f.txn_date between
'some_date' and
'some_other_date'
group
1,2
),
-- then we rank the products for each store using a dense_rank() to avoid ties
-- (use your own business rules here)
ranked as
(
select
product_name,
store_name,
sales_qty,
dense_rank() over(
partition by store_name
order by sales_qty desc) as sales_rank
from
sales
)
-- then pick out the top N products (you could even do this in the BI layer)
select
store_name,
sales_rank,
product_name,
sales_qty
from
ranked
where sales_rank <= 20
```

## Cumulative Total

Cumulative Totals, or *Running Sums* are one of the most helpful analytical building blocks, whether it’s in sales forecasting or marketing cohort analysis. Like most SQL window functions, they can be partitioned so that we can calculate running sums within (or *along*) some subset/partition of data.

Let’s try a simple case first:

### Cumulative Sales YTD:

```
-- Again, first we want to pre-aggregate to the grain we're interested in:
with daily_sales as
(
select
txn_date,
sum(sales_amt) as sales_amt
from
fct_sales
where
txn_date >= '2018-01-01'
group by
1
)
select
txn_date,
sales_amt,
sum(sales_amt) over(
order by txn_date rows unbounded preceding) as cumulative_sales_amt
from
daily_sales
```

The `sum()`

window function here includes a key statement: `rows unbounded preceding`

, which tells our db to to sum over *all* (`unbounded`

) rows `preceding`

the current one. The `order by`

clause makes sure we’re tallying up sales cumulatively in chronological order.

Since we added a `where`

clause specifying we’re only interested in sales data from `Jan 1, 2018`

on, this would constitute a YTD cumulative total, assuming we’re still in 2018.
[Note that there are better, more robust ways to construct that date logic, which we will “leave as an exercise for the reader”.]

If we wanted to compare YTD performance by day **by store**, we can specify `store_name`

as a partition, like so:

### Cumulative Sales YTD by Store:

```
-- As always, first we pre-aggregate to the grain we're interested in:
with daily_sales as
(
select
f.txn_date,
s.store_name,
sum(f.sales_amt) as sales_amt
from
fct_sales f
join
dim_store s on f.store_key = s.store_key
where
f.txn_date >= '2018-01-01'
group by
1,2
)
select
txn_date,
store_name,
sales_amt,
sum(sales_amt) over(
partition by store_name
order by txn_date rows unbounded preceding) as cumulative_sales_amt
from
daily_sales
```

This time, the `sum()`

window function contains a `partition by`

clause that tells us we want cumulative totals **for each store** over the transaction date.

## Cumulative %

Many times, comparing absolute running totals only gets us so far, and what we really want is a cumulative % of total. For example, we may be interested in the trajectory of how a certain marketing cohort reached its ultimate conversion rate. (Btw, we’ll have a series of related blog posts on this soon, stay tuned!)

For this, we’ll need to *combine* window functions:

- one to calculate the cumulative total
- a second to calculate the grand total so that we can then divide the cumulative total by the grand total

Let’s stick with our example of cumulative totals by store and see if each store followed a similar sales trajectory so far this year.

### Cumulative Sales % YTD by Store:

```
-- As always, first we pre-aggregate to the grain we're interested in:
with daily_sales as
(
select
f.txn_date,
s.store_name,
sum(f.sales_amt) as sales_amt
from
fct_sales f
join
dim_store s on f.store_key = s.store_key
where
f.txn_date >= '2018-01-01'
group by
1,2
)
select
txn_date,
store_name,
sales_amt,
-- Cumulative Total by Store
sum(sales_amt) over(
partition by store_name
order by txn_date rows unbounded preceding)::float/
-- Grand Total by Store
sum(sales_amt) over(partition by store_name)
as cumulative_sales_percentage
from
daily_sales
```

Let’s break down the last few lines:

```
sum(sales_amt) over(
partition by store_name
order by txn_date rows unbounded preceding)
```

This gets us the cumulative total of sales for each store over the entire date range, just like we calculated earlier.

The next line gets us the grand total for each store over the chosen date range:

```
sum(sales_amt) over(partition by store_name)
```

We then apply a lazy `::float`

conversion to the numerator to avoid integer division issues, before we divide to get the % contribution.

```
sum(sales_amt) over(
partition by store_name
order by txn_date rows unbounded preceding)::float/
sum(sales_amt) over(partition by store_name)
```

So, effectively, this tells how each day incrementally contributed to the store’s total sales over time since Jan 1.

## Moving Averages

A moving average is one of the best ways to smooth out a time series and to remove noise from short-term observations. For example, you may have weekly data and you’re interested in whether or not your data is trending on a larger time frame. A moving average can help you with that by giving you an additional, zoomed out view of your data with which you can now compare your weekly time series data. For similar reasons, moving averages also provide the basis for well-known time series forecast models, such as ARIMA.

In SQL, moving averages can be constructed by adding a window to the `avg()`

function that specifies a rolling window over the rows. For instance, for a 4-week moving average (4WMA), we’re interested in the average over the last 4 rows of our, previously aggregated, weekly data.

```
-- As always, first we pre-aggregate to the grain we're interested in:
with weekly_sales as
(
select
d.week_start_date,
sum(f.sales_amt) as sales_amt
from
fct_sales f
join
dim_date d on f.txn_date = d.calendar_date
where
f.txn_date >= '2018-01-01'
group by
1
)
select
week_start_date,
sales_amt,
avg(sales_amt) over(
order by week_start_date
rows between 3 preceding and current row) as sales_4wma
from
weekly_sales
```

This example includes the current week in the calculation of the moving average, hence we start with the `3`

preceding rows.

However, often, you’re interested in the moving average of the N weeks *before* the current period, in which case we’d rewrite the `avg()`

over a window of the prior 4 through 1 row:

```
...
avg(sales_amt) over(
order by week_start_date
rows between 4 preceding and 1 preceding) as sales_4wma
...
```

Note: on the **Snowflake** data warehouse platform, the `avg()`

function currently does not yet support adding a `window`

, so we have to resort to a little hack - dividing a `sum()`

by a `count()`

function over the same window.

E.g.:

```
...
sum(sales_amt) over(
order by week_start_date
rows between 4 preceding and 1 preceding)::float/
count(*) over(
order by week_start_date
rows between 4 preceding and 1 preceding)
as sales_4wma
...
```

## Moving Confidence Intervals

Lastly, it’s very handy to be able to measure variance or standard deviation over a time series and construct a moving confidence interval (or at least approximating a confidence interval by assuming the variance in our data is normally distributed). We’ve already seen an application of moving confidence bands in our post on Confidence Intervals for Proportions where we looked at Bollinger Bands around a weekly order exchange rate example.
To construct such a confidence band, we’ll need both a **moving average** and a **moving standard deviation**.

Luckily, on most data warehouse platforms, the `stddev()`

function is also available as a window function, and we can simply apply what we’ve learned so far to `stddev`

as well.

[Note: on **Snowflake** window support for `stddev`

is still sadly missing, but I’ve been told this, along with other enhancements to their ANSI SQL support, is underway.]

Let’s start with the full query, then break it down later:

```
-- As always, first we pre-aggregate to the grain we're interested in:
with weekly_sales as
(
select
d.week_start_date,
sum(f.sales_amt) as sales_amt
from
fct_sales f
join
dim_date d on f.txn_date = d.calendar_date
where
f.txn_date >= '2018-01-01'
group by
1
),
moving_functions as
(
select
week_start_date,
sales_amt,
avg(sales_amt) over(
order by week_start_date
rows between 4 preceding and 1 preceding) as sales_4wma,
stddev(sales_amt) over(
order by week_start_date
rows between 4 preceding and 1 preceding) as sales_4wstd
from
weekly_sales
)
select
week_start_date,
sales_amt,
sales_4wma,
sales_4wma + 1.96*sales_4wstd as sales_upper_bound,
sales_4wma - 1.96*sales_4wstd as sales_lower_bound
from
moving_functions
```

#### Breaking this down a bit:

First, along with the moving average, we add another window function to capture the 4-week moving standard deviation of our sales metric (in this example, not including the current week).

```
...
stddev(sales_amt) over(
order by week_start_date
rows between 4 preceding and 1 preceding) as sales_4wstd
...
```

Then, we construct both upper and lower bounds by adding the moving standard deviation, multiplied a `z-score`

(we chose `1.96`

for a 95% confidence interval, see here) to the 4-week moving average. This essentially constructs a 95% confidence interval (approximated) around each 4-week moving slice of data.

```
...
sales_4wma + 1.96*sales_4wstd as sales_upper_bound,
sales_4wma - 1.96*sales_4wstd as sales_lower_bound
...
```

## So, what have we learned

There are many other ways to employ the power of window functions on a modern data warehouse platform that take advantage of the processing power of distributed/parallel systems. I really encourage new and experienced analysts to explore their SQL options before defaulting to using Python or R.

Using SQL as a structurally simple and easy to put-into-production technology to do advanced analytics, or to preprocess engineered features for your machine learning, is an essential tool in the analyst tool belt.

## Comments