MLDZMNdepbr1
Updated 2023-05-25
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
›
⌄
with t1 as ( select
hour::date as day,
avg(price) as avg_price
from ethereum.core.fact_hourly_token_prices
where symbol = 'WETH'
group by 1),
dep as (select
s.block_timestamp::date as date,
sum(ethereum.public.udf_hex_to_int(substring(INPUT_DATA, 188, 15))::float/1e18) as volume_deposit,
sum((ethereum.public.udf_hex_to_int(substring(INPUT_DATA, 188, 15))::float/1e18)*avg_price) as volume_deposit_usd
from arbitrum.core.fact_event_logs s
left join arbitrum.core.fact_transactions a on s.tx_hash=a.tx_hash
left join t1 on s.block_timestamp::date=t1.day
where s.CONTRACT_ADDRESS='0xbe54bafc56b468d4d20d609f0cf17ffc56b99913'
and s.ORIGIN_FUNCTION_SIGNATURE='0x3bc1f1ed'
--and s.tx_hash='0x4dcf1ce24a0ddef9cfa3ab5434de5af9e2e9ac8b10fb5bc34003ab918eb39037'
group by 1
),
bor as (select
s.block_timestamp::date as date,
sum(ethereum.public.udf_hex_to_int(substring(INPUT_DATA, 188, 15))::float/1e18) as volume_borrow,
sum((ethereum.public.udf_hex_to_int(substring(INPUT_DATA, 188, 15))::float/1e18)*avg_price) as volume_borrow_usd
from arbitrum.core.fact_event_logs s
left join arbitrum.core.fact_transactions a on s.tx_hash=a.tx_hash
left join t1 on s.block_timestamp::date=t1.day
where s.CONTRACT_ADDRESS='0xbe54bafc56b468d4d20d609f0cf17ffc56b99913'
and s.ORIGIN_FUNCTION_SIGNATURE='0xfbf37739'
group by 1)
select
dep.date as date,
volume_deposit_usd-volume_borrow_usd as daily_net_flow,
sum(daily_net_flow) over (order by dep.date) as TVL
Run a query to Download Data