Afonso_DiazOvertime
Updated 2024-12-28
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
pricet as (
select
hour::date as date,
token_address,
case token_address
when '0x9a29fcbd94e7b214b65978c0a5837fc705f51c42' then 6
when '0x7f27352d5f83db87a5a3e00f4b07cc2138d8ee52' then 6
else 18
end as decimals,
avg(price) as token_price_usd
from
crosschain.price.ez_prices_hourly
where
blockchain = 'flow evm'
group by 1, 2, 3
),
main as (
select
tx_hash,
block_timestamp,
lower(origin_from_address) as swapper,
c.contract_address as token_in_contract,
b.contract_address as token_out_contract,
greatest(utils.udf_hex_to_int(substr(substr(a.data, 3), 1, 64)), utils.udf_hex_to_int(substr(substr(a.data, 3), 65, 64))) AS amount_in_unadj,
greatest(utils.udf_hex_to_int(substr(substr(a.data, 3), 129, 64)), utils.udf_hex_to_int(substr(substr(a.data, 3), 193, 64))) as amount_out_unadj,
amount_in_unadj / pow(10, t1.decimals) as amount_in,
amount_out_unadj / pow(10, t2.decimals) as amount_out,
amount_in * t1.token_price_usd as amount_in_usd,
amount_out * t2.token_price_usd as amount_out_usd,
nvl(amount_in_usd, amount_out_usd) as amount_usd
from
flow.core_evm.fact_event_logs a
join
QueryRunArchived: QueryRun has been archived