MufasaOP token Price Vs Count of Swappers
Updated 2022-10-03
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_of_op AS (
SELECT date_trunc('day', hour) as time_date,
avg(price) as average_price_of_op
FROM optimism.core.fact_hourly_token_prices
WHERE token_address = '0x4200000000000000000000000000000000000042'
GROUP BY time_date
), swappers_information AS (
( SELECT logs.block_timestamp::date as time_date,
count (distinct logs.tx_hash) as swappers_number,
count (distinct logs.origin_from_address) as swappers_count,
sum(swappers_number) over (order by time_date) as cum_swappers_number,
sum(swappers_count) over (order by time_date) as cum_swappers_count,
sum(transfer.raw_amount * price.price / power(10, price.decimals)) as volume_of_trades,
'UNISWAP' as symbol
FROM optimism.core.fact_event_logs logs
JOIN optimism.core.fact_token_transfers transfer
ON logs.tx_hash = transfer.tx_hash
JOIN optimism.core.fact_hourly_token_prices price
ON transfer.contract_address = price.token_address AND date_trunc('hour', transfer.block_timestamp) = price.hour
WHERE logs.origin_to_address IN ('0xe592427a0aece92de3edee1f18e0157c05861564','0x68b3465833fb72a70ecdf485e0e4c7bd8665fc45') and
logs.tx_status ='SUCCESS' and logs.event_name = 'Swap' AND transfer.contract_address = '0x4200000000000000000000000000000000000042'
GROUP BY 1)
UNION
(SELECT block_timestamp::date as time_date,
count (distinct tx_hash) as swappers_number,
count (distinct origin_from_address) as swappers_count,
sum(swappers_number) over (order by time_date) as cum_swappers_number,
sum(swappers_count) over (order by time_date) as cum_swappers_count,
sum(amount_in_usd) as volume_of_trades,
'SUSHISWAP' as symbol
FROM optimism.sushi.ez_swaps
WHERE token_in = '0x4200000000000000000000000000000000000042' or token_out = '0x4200000000000000000000000000000000000042'
GROUP BY 1)
UNION
(SELECT block_timestamp::date as time_date,
count (distinct tx_hash) as swappers_number,
Run a query to Download Data