snowmanUntitled Query
Updated 2022-11-15Copy Reference Fork
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 roo as (
select date_trunc('hour', hour)
as hour,
avg(price) as price
from ethereum.core.fact_hourly_token_prices
--from ethereum.core.fact_hourly_token_prices
where token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' -- WETH
and hour::date >=
current_date - 30
group by 1
),
ethereum_data as (
select 1000000 * (sum(tx_fee*price) / sum(eth_value*price))
as transfered
from ethereum.core.fact_transactions
inner join roo on date_trunc('hour', block_timestamp) =
hour
and block_timestamp::date >=
current_date - 30
),
sol_price as (
select date_trunc('hour', recorded_hour) as hour,
avg(close) as price
from solana.core.fact_token_prices_hourly
where symbol = 'SOL'
and recorded_hour::date >= current_date - 30
group by 1
),
solana_data as (
select 1000000 * (sum(price*fee/pow(10,9)) /sum(price*CASE WHEN PRE_BALANCES[0] - POST_BALANCES[0] > 0 THEN PRE_BALANCES[0] - POST_BALANCES[0]
ELSE POST_BALANCES[0] - PRE_BALANCES[0] END/pow(10,9))) as transfered
from solana.core.fact_transactions
inner join sol_price on date_trunc('hour', block_timestamp) =
Run a query to Download Data