Afonso_Diaz2024-06-01 05:06 PM copy copy copy copy copy
Updated 2024-07-23
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
prices as (
select
hour::date as date,
avg(price) as price_usd
from ethereum.price.ez_prices_hourly
where symbol = 'WETH'
group by 1
),
t as (
select
a.tx_hash,
a.block_timestamp,
a.origin_from_address as trader,
iff(a.from_address = a.origin_to_address, nvl(b.symbol, iff(c.amount is not null, 'ETH', null)), a.symbol) as symbol_in,
iff(a.from_address = a.origin_to_address, nvl(b.amount, c.amount), a.amount) as amount_in,
iff(a.from_address = a.origin_to_address, nvl(b.amount_usd, c.amount_usd), a.amount_usd) as amount_in_usd,
iff(a.from_address = a.origin_to_address, a.symbol, nvl(b.symbol, iff(c.amount is not null, 'ETH', null))) as symbol_out,
iff(a.from_address = a.origin_to_address, a.amount, nvl(b.amount, c.amount)) as amount_out,
iff(a.from_address = a.origin_to_address, a.amount_usd, nvl(b.amount_usd,c.amount_usd)) as amount_out_usd,
'Li.Fi' as platform
from ethereum.core.ez_token_transfers a
left join ethereum.core.ez_token_transfers b
on a.tx_hash = b.tx_hash
and a.block_timestamp = b.block_timestamp
and a.origin_to_address = b.origin_to_address
and b.origin_from_address in (b.from_address, b.to_address)
and b.origin_to_address in (b.from_address, b.to_address)
and a.event_index != b.event_index
left join ethereum.core.ez_native_transfers c
QueryRunArchived: QueryRun has been archived