@devinwalsh / .ZRX Staked Over Time
@devinwalshAbout queries and results
Dune Analytics lets you explore, create, and share Ethereum analytics. You can find an endless amount of great queries and dashboards on Dune.
We have decoded Ethereum smart contract data so you can do powerful analysis with simple SQL queries and visualise the query results into beautiful graphs.
Dune Analytics is free for everyone forever. If you want extra features like private queries, export your results and more check out our Pro plan.
SQL query results
SQL query
1WITH zrx_staking_changes_with_timestamp AS
2 (SELECT b.time,
3 zsc.*
4 FROM zeroex.view_zrx_staking_changes zsc
5 LEFT JOIN ethereum.blocks b ON b.number = zsc.block_number),
6 zrx_usd_prices AS
7 (SELECT DISTINCT MINUTE::DATE AS dt,
8 LAST_VALUE(price) OVER (PARTITION BY MINUTE::DATE
9 ORDER BY MINUTE ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS price
10 FROM prices.usd_e41d2489571d322189246dafa5ebde1f4699f498
11 WHERE -- make sure this covers the whole time period
12 MINUTE > (CURRENT_DATE - '180 days'::INTERVAL) )
13SELECT c.dt,
14 SUM(zsc.amount) AS zrx_staked,
15 SUM(zsc.amount * p.price) AS zrx_staked_usd
16FROM dune_user_generated.calendar c
17LEFT JOIN zrx_staking_changes_with_timestamp zsc ON zsc.time::DATE <= (c.dt + '1 day'::INTERVAL)
18LEFT JOIN zrx_usd_prices p ON p.dt = c.dt
19WHERE c.dt BETWEEN (CURRENT_DATE - '120 days'::INTERVAL) AND (CURRENT_DATE - '1 day'::INTERVAL)
20GROUP BY 1;
21
22