winnie-fsOP Price vs Swapper Activity - Part 1 - OP Price Daily - Sushi copy
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
›
⌄
-- forked from Mojtaba-Banaei / OP Price vs Swapper Activity - Part 1 - OP Price Daily - Sushi @ https://flipsidecrypto.xyz/Mojtaba-Banaei/q/op-airdrop-2-how-many-claimed-eEVTYt
WITH op_price AS (
SELECT
distinct date_trunc('day', hour) AS day,
AVG(price) over (partition by day) AS op_price
FROM optimism.core.fact_hourly_token_prices
WHERE token_address = '0x4200000000000000000000000000000000000042'
and hour >= '{{start_date}}'
) ,
op_sushi_pools as (
select
distinct pool_name
, contract_address
, symbol_in
, symbol_out
from
optimism.sushi.ez_swaps
where
pool_name ilike '%OP%' -- OP-USDC WETH-OP OP-DAI OP-sUSD
and block_timestamp >= '{{start_date}}'
) , daily_usdc_op_swaps as (
select
block_timestamp
, case
when symbol_in = 'OP' then amount_in_usd
else amount_out_usd
end as op_in_usd
, case
when symbol_in = 'OP' then amount_in
else amount_out
end as op_amount
, (op_in_usd / op_amount) * 2 as op_price
, origin_from_address as user
Run a query to Download Data