Dune-Calculate Over Time

In the last post, we started to dig into my wallet history over time. We picked a single point in time to evaluate.

Typically when looking at historical data it is more important to see how the data changed over time.

As an example, instead of just calculating total gas spent, we actually want to show how that value accrued over time.

Was it gradual, or a rapid increase?

Postgres has a very easy function to cover this called SUM([Value Column]) OVER (ORDER BY [Time Column]), with the [Time Column] also included in your SELECT statement.

Let’s go back to the Transactions table example of Gas spent and test out this OVER function;

Select
sum(((“gas_price”*”gas_used”)/10¹⁸) * prices.”price”)
OVER (ORDER BY date_trunc(‘day’,txs.”block_time”) ASC) AS “gas_usd_cost”,
date_trunc(‘day’,txs.”block_time”) AS “tx_day”
from ethereum.”transactions” txs
left join (
SELECT “minute”, “price”
from prices.”usd”
Where symbol = ‘WETH’) as “prices” ON prices.”minute” = date_trunc(‘minute’,txs.”block_time”)
where txs.”from” = ‘\xb9479331DF573d6BCa8b924f48e8CACB84664cE7’

I added the OVER function and transaction day. Note, I do not use the GROUP BY clause in this function. This was something that I originally did out of habit, but this is not the way this function works.

Since I am not using GROUP BY you will see I get some unintended results of a single day appearing multiple times. The very first day 2019–09–04 is listed 3X.

This is because my transaction table has 3 rows for 2019–09–14, but since I am not grouping by transaction day it lists out everything.

To fix this, I just need to reorder my query a bit. I need to first SUM and GROUP BY the day, and then I can use my OVER function on that result.

WITH “Gas_Cost” AS (
Select
sum(((“gas_price”*”gas_used”)/10¹⁸) * prices.”price”) AS “gas_usd_cost”,
date_trunc(‘day’,txs.”block_time”) AS “tx_day”
from ethereum.”transactions” txs
left join (
SELECT “minute”, “price”
from prices.”usd”
Where symbol = ‘WETH’) as “prices” ON prices.”minute” = date_trunc(‘minute’,txs.”block_time”)
where txs.”from” = ‘\xb9479331DF573d6BCa8b924f48e8CACB84664cE7’
GROUP BY date_trunc(‘day’,txs.”block_time”))
SELECT
sum(“gas_usd_cost”)
OVER ( ORDER BY “tx_day” ASC) AS “gas_usd_cost_ttl”,
“tx_day”
FROM “Gas_Cost”

To first GROUP BY transaction day I used a CTE(Common Table Expression) that I called “Gas_used”, and then selected from that CTE using by OVER function to sum the value over time.

Works like a charm.

Calculating over time can be used for other aggregation functions like AVG or maybe even more popular RANK.

RANK is really fun, because it allows you to return the row number for an ordered result. As an example, you could return the 5th most expensive transaction you ever completed. Kind of a lame example, but we will find a better way to use this later.

Next post, I will make the transition away from SQL to some visualizations. Dune has some very nice options for visuals and they are very easy to implement once you get the SQL working.