alirsop-opt-06
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
26
27
›
⌄
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
date_trunc('day', block_timestamp) 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 vol_to_op_usd DESC) as rank
FROM optimism.core.fact_event_logs a JOIN price ON a.block_timestamp::DATE = price.day
LEFT JOIN optimism.core.dim_contracts l ON a.EVENT_INPUTS:tokenIn = l.address
WHERE event_name = 'Swap' and symbol is not null and date>='2022-06-04' and date<='2022-11-01'
AND EVENT_INPUTS:tokenOut='0x4200000000000000000000000000000000000042' -- OP TOKEN
AND tx_status = 'SUCCESS'
GROUP BY 1, 2, 3,4
qualify rank <= 10
-- ORDER by 1
Run a query to Download Data