hessAverage Fee uni
Updated 2022-10-26
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 tx as ( select *
from optimism.core.fact_event_logs
where event_name = 'Swap')
,
label as (
select ADDRESS as token, PROJECT_NAME
from optimism.core.dim_labels
where label_type = 'dex'
)
,
from_token as ( select date(BLOCK_TIMESTAMP) as date , tx_hash as tx , origin_from_address as swapper, token , PROJECT_NAME as project, symbol as token_in , decimals as decimal_in , EVENT_INPUTS:value as amount_i
from optimism.core.fact_event_logs a join label b on a.ORIGIN_TO_ADDRESS = b.token
join optimism.core.dim_contracts c on a.contract_address = c.address
where tx_hash in ( select tx_hash from tx) and origin_from_address = EVENT_INPUTS:from
)
,
to_token as ( select date(BLOCK_TIMESTAMP) as date_ , tx_hash , origin_from_address , token , PROJECT_NAME , symbol as token_out , decimals as decimal_out , EVENT_INPUTS:value as amount_ou
from optimism.core.fact_event_logs a join label b on a.ORIGIN_TO_ADDRESS = b.token
join optimism.core.dim_contracts c on a.contract_address = c.address
where tx_hash in ( select tx_hash from tx) and origin_from_address = EVENT_INPUTS:to)
,
final as ( select date , tx_hash, project_name , swapper , token_in , token_out , amount_i/pow(10,decimal_in) as amount_in , amount_ou/pow(10,decimal_out) as amount_out
from from_token a left outer join to_token b on a.tx = b.tx_hash and date = date_
)
,
price as ( select date(hour) as p_date ,symbol , avg(price) as prices
from optimism.core.fact_hourly_token_prices
group by 1,2 )
,
tb1 as ( select date , tx_hash, project_name , swapper , token_in , token_out ,amount_in , amount_in*b.prices as amount_in_usd, amount_out,
amount_out*c.prices as amount_out_usd
from final a left outer join price b on a.token_in = b.symbol and date = b.p_date
left outer join price c on a.token_out = c.symbol and date = c.p_date)
,
optimism as ( select date,'Optimism' as chain, tx_hash , project_name, swapper, token_in , amount_in_usd, token_out, amount_out_usd
from tb1
Run a query to Download Data