adambalaUntitled Query
Updated 2022-09-05
999
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
gg as (
select hour::date as date,
avg (price) as usdprice
from ethereum.core.fact_hourly_token_prices
where symbol ='WETH'
group by 1),
ANKR as (select date_trunc (week,block_timestamp) as week,'ANKR' as title ,
count (distinct tx_hash) as TX_Count,
count (distinct origin_from_address) as Users,
sum (event_inputs:value/1e18) as Total_ETH,
sum (event_inputs:value/1e18*usdprice) as Total_USD,
avg (event_inputs:value/1e18) as Average_ETH,
avg (event_inputs:value/1e18*usdprice) as Average_USD,
sum (Total_ETH) over (order by week) as Cumulative_ETH,
sum (total_usd) over (order by week) as Cumulative_USD
from ethereum.core.fact_event_logs t1 join gg t2 on t1.block_timestamp::date = t2.date
where origin_to_address = lower('0x84db6eE82b7Cf3b47E8F19270abdE5718B936670')
--and origin_function_signature = '0xd0e30db0' -- Deposit
and event_name = 'Transfer'
group by 1,2
order by 1),
LIDO as (select date_trunc (week,block_timestamp) as week,'LIDO' as title ,
count (distinct tx_hash) as TX_Count,
count (distinct origin_from_address) as Users,
sum (event_inputs:value/1e18) as Total_ETH,
sum (event_inputs:value/1e18*usdprice) as Total_USD,
avg (event_inputs:value/1e18) as Average_ETH,
avg (event_inputs:value/1e18*usdprice) as Average_USD,
sum (Total_ETH) over (order by week) as Cumulative_ETH,
sum (total_usd) over (order by week) as Cumulative_USD