andreafiandroComparison: TVB vs. TVL I Scroll Network copy
Updated 2023-10-23
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- forked from tkvresearch / Comparison: TVB vs. TVL I Scroll Network @ https://flipsidecrypto.xyz/tkvresearch/q/k8il3PkViuU_/comparison-tvb-vs.-tvl-i-scroll-network
with
P as( select price
from ethereum.price.ez_hourly_token_prices
where token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
order by hour DESC
limit 1),
-- ETH
A as( select amount, eth_to_address as bridge, tx_hash, BLOCK_TIMESTAMP from ethereum.core.ez_eth_transfers union all
select 0 - amount, eth_from_address as bridge, tx_hash, BLOCK_TIMESTAMP from ethereum.core.ez_eth_transfers),
B as( select date_trunc('hour',BLOCK_TIMESTAMP) as time, 'ETH' as symbol,
sum(amount) as amount, sum(amount*price) as value
from A , P
where bridge = '0x6774bcbd5cecef1336b5300fb5186a12ddd8b367'
group by 1,2),
-- altscoins
AA as( select symbol, amount, amount_usd, to_address as bridge, tx_hash, BLOCK_TIMESTAMP from ethereum.core.ez_token_transfers union all
select symbol, 0 - amount, amount_usd, from_address as bridge, tx_hash, BLOCK_TIMESTAMP from ethereum.core.ez_token_transfers),
BB as( select date_trunc('hour',BLOCK_TIMESTAMP) as time, symbol ,
sum(amount) as amount, sum(amount_usd) as value
from AA
where bridge = '0xd8a791fe2be73eb6e6cf1eb0cb3f36adc9b3f8f9'
group by 1,2 ),
-- all
C as( select * from B union all select * from BB),
TT as( select DISTINCT symbol from C),
T as( select DISTINCT time , b.symbol
from C as a
cross join TT as b),
Run a query to Download Data