RamaharHOP OP > ETH
Updated 2022-10-22Copy Reference Fork
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
›
⌄
-- OP > ETH
with token_price as (select
hour::date as dt,
symbol,
AVG(price) as avg_price
from optimism.core.fact_hourly_token_prices
where symbol IN ('USDC', 'USDT', 'DAI', 'HOP', 'WBTC', 'WETH', 'SNX')
group by 1, 2),
hops as (select
BLOCK_TIMESTAMP::date as dayz,
tx_hash,
origin_from_address,
case
when contract_address = lower('0xa81D244A1814468C734E5b4101F7b9c0c577a8fC') then 'USDC'
when contract_address = lower('0x46ae9BaB8CEA96610807a275EBD36f8e916b5C61') then 'USDT'
when contract_address = lower('0x7191061D5d4C60f598214cC6913502184BAddf18') then 'DAI'
when contract_address = lower('0x83f6244Bd87662118d96D9a6D44f09dffF14b30E') then 'WETH'
when contract_address = lower('0xB1ea9FeD58a317F81eEEFC18715Dd323FDEf45c4') then 'WBTC'
when contract_address = lower('0x03D7f750777eC48d39D080b020D83Eb2CB4e3547') then 'HOP'
when contract_address = lower('0x16284c7323c35F4960540583998C98B1CfC581a7') then 'SNX'
when contract_address = lower('0x33Fe5bB8DA466dA55a8A32D6ADE2BB104E2C5201') then 'sUSD'
end as symbol,
event_inputs:amount as amount,
CASE
when symbol IN ('WETH', 'DAI', 'HOP') then amount / POW(10,18)
when symbol IN ('USDC', 'USDT') then amount / POW(10,6)
when symbol = 'WBTC' then amount / POW(10,8)
end as adjusted_amount
from optimism.core.fact_event_logs l
--join optimism.core.fact_token_transfers t ON l.tx_hash = t.tx_hash AND l.block_timestamp = t.block_timestamp
where
l.contract_address IN ( lower('0xa81D244A1814468C734E5b4101F7b9c0c577a8fC') --USDC 10e6
,lower('0x46ae9BaB8CEA96610807a275EBD36f8e916b5C61') --USDT 10e6
-- ,lower('0x22B1Cbb8D98a01a3B71D034BB899775A76Eb1cc2') --MATIC 10e18 --
Run a query to Download Data