
<h2 class="wp-block-heading"><strong>Day/Day Deltas</strong></h2>



<p>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:</p>



<div class="table-responsive"><figure class="wp-block-table table table-bordered"><table class=""><tbody><tr><td>dt</td><td>ct </td><td>ct_yesterday</td><td>daily_delta </td></tr><tr><td>2014-07-01</td><td>6917 </td><td>200816 </td><td>-96.56% </td></tr><tr><td>2014-06-30  &nbsp;&nbsp;</td><td>200816 </td><td>93729 </td><td>114.25%</td></tr><tr><td>2014-06-29</td><td>93729 </td><td>115240 </td><td>-18.67% </td></tr><tr><td>2014-06-28</td><td>115240 </td><td>243014 </td><td>-52.58% </td></tr><tr><td>2014-06-27 </td><td>243014 </td><td>219843 </td><td>10.54% </td></tr><tr><td>2014-06-26</td><td>219843 </td><td>184825  </td><td>18.95%  </td></tr><tr><td>2014-06-25  </td><td>184825 </td><td>239193 </td><td>-22.73% </td></tr><tr><td>2014-06-24</td><td>239193 </td><td>234194 </td><td>2.13% </td></tr><tr><td>2014-06-23</td><td>234194 </td><td>79145 </td><td>195.90% </td></tr><tr><td>2014-06-22</td><td>79145  </td><td>131561 </td><td>-39.84% </td></tr></tbody></table></figure></div>



<p> To start, let’s count the daily events for the last 10 days: </p>



<pre class="wp-block-code"><code>select
  date(created_at) dt,
  count(1) ct
from events
group by 1 order by 1 desc
limit 10</code></pre>



<h2 class="wp-block-heading"><strong>The Good Kind of Lag</strong></h2>



<p>In Postgres and Redshift, we’ll use the lag() window function to add our daily_delta column. lag(a, N) grabs the value in column a that is N rows below the current row. For our query, we want yesterday’s ct:</p>



<pre class="wp-block-code"><code>lag(count(1), 1) over (order by dt) as ct_yesterday</code></pre>



<p>The order by dt tells the query planner how to order the rows in the window, count(1) is the count of events for each dt, and the 1 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 7 instead of 1. Here’s the full query so far:</p>



<pre class="wp-block-code"><code>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</code></pre>



<p>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 (ct &#8211; ct_yesterday) / ct_yesterday. We can simply add that to an outer query:</p>



<pre class="wp-block-code"><code>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</code></pre>



<p>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.&nbsp;</p>



<pre class="wp-block-code"><code>round(
  100.0 * (ct - ct_yesterday) / ct_yesterday, 2
) || '%' as daily_delta</code></pre>



<p>Here’s the full query:</p>



<pre class="wp-block-code"><code>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
) tf</code></pre>



<p> Now, to visualize that day-over-day change, create a simple line chart and you’re done: </p>



<figure class="wp-block-image"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/Day_Day-delta-computing-blog.png" alt="Day day delta" class="wp-image-74175"/></figure>


Computing Day-Over-Day Changes With Window Functions

LinkedIn

Twitter

GitHub

curve-image-unique-image-unique

curve

3-dark-2-image-unique-image-unique

3 DARK 2

Get the latest in analytics right in your inbox.

Article