MLDZMNdxa6
Updated 2023-02-22
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 price as (select
HOUR::date as day,
SYMBOL,
decimals,
avg(price) as token_price
from ethereum.core.fact_hourly_token_prices
group by 1,2,3
),
t1 AS (
SELECT
swaps.event_index,
swaps.block_timestamp,
swaps.tx_hash AS tx,
CASE
WHEN swaps.origin_to_address = lower('0x1b02dA8Cb0d097eB8D57A175b88c7D8b47997506') THEN 'SushiSwap'
WHEN swaps.origin_to_address in('0x68b3465833fb72a70ecdf485e0e4c7bd8665fc45','0xe592427a0aece92de3edee1f18e0157c05861564') THEN 'Uniswap'
END AS dex,
swaps.origin_from_address AS swapper,
swaps.contract_address AS token_contract,
p.symbol AS token,
(swaps.event_inputs:value / POW(10, a.decimals) * p.token_price) AS amount
FROM arbitrum.core.fact_event_logs swaps
LEFT JOIN arbitrum.core.dim_labels labels ON swaps.origin_to_address = labels.address
left join arbitrum.core.dim_contracts a on swaps.contract_address=a.ADDRESS
JOIN price p ON swaps.block_timestamp::date = p.day AND a.SYMBOL = p.SYMBOL
WHERE
swaps.tx_hash IN (
SELECT DISTINCT tx_hash
FROM arbitrum.core.fact_event_logs
WHERE tx_status = 'SUCCESS' AND event_name = 'Swap'
)
AND swaps.event_name = 'Transfer'
and a.NAME not ilike '%lp token%' and a.SYMBOL not ilike '%/USD%' and a.SYMBOL not ilike 'LP-%'
and amount<1e6
),
Run a query to Download Data