alirsop-opt-04-B
Updated 2022-11-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
›
⌄
WITH price AS (
SELECT
hour::DATE AS day
,AVG(price) AS OP_price
FROM optimism.core.fact_hourly_token_prices
WHERE symbol = 'OP'
GROUP BY 1
)
SELECT
block_timestamp::date AS date,case when date>='2022-06-04' and date<='2022-08-17' then 'Before hack fake rumors'
when date>'2022-08-17' and date<='2022-08-24' then 'A few daysAfter hack fake rumors(1 Week)'
else 'After hack fake rumors' end as Time ,SYMBOL ,OP_price
,Count(DISTINCT tx_hash) AS Swap_Count
,Count(DISTINCT origin_from_address) AS Swapper_Count
,sum((CASE WHEN EVENT_INPUTS:tokenOut='0x4200000000000000000000000000000000000042' THEN EVENT_INPUTS:amountIn/1e18 END)*OP_price) as vol_to_op_usd
,row_number() over (partition by date order by Swap_Count DESC) as ranking
FROM optimism.core.fact_event_logs log JOIN price ON block_timestamp::DATE = price.day
LEFT JOIN optimism.core.dim_contracts cont ON log.EVENT_INPUTS:tokenin = cont.address
WHERE event_name = 'Swap'
and EVENT_INPUTS:tokenOut='0x4200000000000000000000000000000000000042'
AND tx_status = 'SUCCESS' and symbol is not null and date>='2022-06-04' and date<='2022-11-01'
GROUP BY 1, 2, 3,4
qualify ranking <= 10
Run a query to Download Data