MoDeFi#opti - Optimism DEXs 2
Updated 2022-10-26
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 uniswap_swaps as (
select a.BLOCK_TIMESTAMP, 'uniswap' as PLATFORM, a.ORIGIN_FROM_ADDRESS as swapper,
a.CONTRACT_ADDRESS as TOKEN_IN, c.SYMBOL as SYMBOL_IN, (a.EVENT_INPUTS:value/pow(10,c.DECIMALS)) as AMOUNT_IN, AMOUNT_IN*c.PRICE as AMOUNT_IN_USD,
b.CONTRACT_ADDRESS as TOKEN_OUT, d.SYMBOL as SYMBOL_OUT, (b.EVENT_INPUTS:value/pow(10,d.DECIMALS)) as AMOUNT_OUT, AMOUNT_OUT*d.PRICE as AMOUNT_OUT_USD, a.tx_hash,
SYMBOL_IN||'-'||SYMBOL_OUT as pool
from optimism.core.fact_event_logs a
join optimism.core.fact_event_logs b
on a.tx_hash=b.tx_hash and b.EVENT_INPUTS:to=b.ORIGIN_FROM_ADDRESS and b.EVENT_NAME='Transfer'
left join optimism.core.fact_hourly_token_prices c
on a.CONTRACT_ADDRESS=c.TOKEN_ADDRESS and date_trunc(hour, a.BLOCK_TIMESTAMP)=date_trunc(hour, c.hour)
left join optimism.core.fact_hourly_token_prices d
on b.CONTRACT_ADDRESS=d.TOKEN_ADDRESS and date_trunc(hour, b.BLOCK_TIMESTAMP)=date_trunc(hour, d.hour)
join optimism.core.dim_labels e
on a.ORIGIN_TO_ADDRESS=e.ADDRESS
where a.EVENT_INPUTS:from=a.ORIGIN_FROM_ADDRESS and a.EVENT_NAME='Transfer'and e.ADDRESS_NAME ilike '%uniswap%' and e.LABEL_SUBTYPE='swap_contract'--)
and (a.EVENT_INDEX-1=b.EVENT_INDEX or
(a.CONTRACT_ADDRESS!=b.CONTRACT_ADDRESS and a.EVENT_INDEX-1!=b.EVENT_INDEX
and abs(AMOUNT_OUT_USD-AMOUNT_in_USD)<( case when AMOUNT_in_USD>AMOUNT_out_USD then AMOUNT_in_USD else AMOUNT_out_USD end)/2))
union all
select a.BLOCK_TIMESTAMP, 'uniswap' as PLATFORM, a.ORIGIN_FROM_ADDRESS as swapper,
a.CONTRACT_ADDRESS as TOKEN_IN, c.SYMBOL as SYMBOL_IN, (a.EVENT_INPUTS:value/pow(10,c.DECIMALS)) as AMOUNT_IN, AMOUNT_IN*c.PRICE as AMOUNT_IN_USD,
'0x' as TOKEN_OUT, 'ETH' as SYMBOL_OUT, b.AMOUNT as AMOUNT_OUT, b.AMOUNT_USD as AMOUNT_OUT_USD, a.tx_hash,
SYMBOL_IN||'-'||SYMBOL_OUT as pool
from optimism.core.fact_event_logs a
join optimism.core.ez_eth_transfers b
on a.tx_hash=b.tx_hash and b.ETH_TO_ADDRESS=a.ORIGIN_FROM_ADDRESS
left join optimism.core.fact_hourly_token_prices c
on a.CONTRACT_ADDRESS=c.TOKEN_ADDRESS and date_trunc(hour, a.BLOCK_TIMESTAMP)=date_trunc(hour, c.hour)
join optimism.core.dim_labels e
on a.ORIGIN_TO_ADDRESS=e.ADDRESS
where a.EVENT_INPUTS:from=a.ORIGIN_FROM_ADDRESS and a.EVENT_NAME='Transfer'and e.ADDRESS_NAME ilike '%uniswap%' and e.LABEL_SUBTYPE='swap_contract'--)
union all
select a.BLOCK_TIMESTAMP, 'uniswap' as PLATFORM, a.ORIGIN_FROM_ADDRESS as swapper,
'0x' as TOKEN_IN, 'ETH' as SYMBOL_IN, b.AMOUNT as AMOUNT_IN, b.AMOUNT_USD as AMOUNT_IN_USD,
a.CONTRACT_ADDRESS as TOKEN_OUT, c.SYMBOL as SYMBOL_OUT, (a.EVENT_INPUTS:value/pow(10,c.DECIMALS)) as AMOUNT_OUT, AMOUNT_OUT*c.PRICE as AMOUNT_OUT_USD, a.tx_hash,
SYMBOL_IN||'-'||SYMBOL_OUT as pool
Run a query to Download Data