## Computing Day-Over-Day Changes With Window Functions

### Want to make data analysis fast for everyone?

Join Us!### Day/Day Deltas

In most sophisticated analysis, the rate of change is at least as important as the raw values. This makes life tough for a SQL analyst, where adding daily deltas to your result set can be difficult. In this post, we’ll show you how to pull results like these:

dt | ct | ct_yesterday | daily_delta ------------+--------+--------------+------------- 2014-07-01 | 6917 | 200816 | -96.56% 2014-06-30 | 200816 | 93729 | 114.25% 2014-06-29 | 93729 | 115240 | -18.67% 2014-06-28 | 115240 | 243014 | -52.58% 2014-06-27 | 243014 | 219843 | 10.54% 2014-06-26 | 219843 | 184825 | 18.95% 2014-06-25 | 184825 | 239193 | -22.73% 2014-06-24 | 239193 | 234194 | 2.13% 2014-06-23 | 234194 | 79145 | 195.90% 2014-06-22 | 79145 | 131561 | -39.84%

To start, let’s count the daily events for the last 10 days:

```
select
date(created_at) dt,
count(1) ct
from events
group by 1 order by 1 desc
limit 10
```

### The Good Kind of Lag

In Postgres and Redshift, we’ll use the ** lag()** window function to add our

**column.**

`daily_delta`

**grabs the value in column**

`lag(a, N)`

**that is**

`a`

**rows below the current row. For our query we want yesterday’s**

`N`

**:**

`ct`

`lag(count(1), 1) over (order by dt) as ct_yesterday`

The ** order by dt** tells the query planner how to order the rows in the window,

**is the count of events for each**

`count(1)`

**, and the**

`dt`

**says to grab one row below the current row. If you wanted to compare to the same day of the week last week, just use**

`1`

**instead of**

`7`

**. Here’s the full query so far:**

`1`

```
select
date(created_at) dt,
count(1) ct,
lag(count(1), 1) over (order by dt) as ct_yesterday
from events
group by 1 order by 1 desc
limit 10
```

Now we have ** ct_yesterday**, yesterday’s count, in every row. But we really want the change between today and yesterday. To do that, we calculate

**. We can simply add that to an outer query:**

`(ct - ct_yesterday) / ct_yesterday`

```
select
dt,
ct,
ct_yesterday,
(ct - ct_yesterday) / ct_yesterday as daily_delta
from (
select
date(created_at) dt,
count(1) ct,
lag(count(1), 1) over (order by dt) as ct_yesterday
from events
group by 1 order by 1 desc
limit 10
) t
```

That gives us a decimal between 0 and 1, so we’ll multiply by 100, round to two decimal places, and tack on a percent for style. (We would be remiss if we didn’t mention that these formatting steps are unnecessary in Periscope!)

```
round(
100.0 * (ct - ct_yesterday) / ct_yesterday, 2
) || '%' as daily_delta
```

Here’s the full query:

```
select
dt, ct, ct_yesterday,
round(
100.0 * (ct - ct_yesterday) / ct_yesterday, 2
) || '%' as daily_delta
from (
select
date(created_at) dt,
count(1) ct,
lag(count(1), 1) over (order by dt) as ct_yesterday
from events
group by 1
order by 1 desc
limit 10
) t
```

Now, to visualize that day-over-day change, sign up for Periscope and it’ll be a breeze to make these kinds of delta charts: