jackguysg Optimism 1
Updated 2023-05-03
999
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 tab0 as (
SELECT *
FROM VALUES
('optimism', 'USDC', lower('0xDecC0c09c3B5f6e92EF4184125D5648a66E35298'), lower('0x7F5c764cBc14f9669B88837ca1490cCa17c31607')),
('optimism', 'ETH', lower('0xd22363e3762cA7339569F3d33EADe20127D5F98C'), lower('')),
('arbitrum', 'USDC', lower('0x892785f33CdeE22A30AEF750F285E18c18040c3e'), lower('0xFF970A61A04b1cA14834A43f5dE4533eBDDB5CC8')),
('arbitrum', 'USDT', lower('0xB6CfcF89a7B22988bfC96632aC2A9D6daB60d641'), lower('0xFd086bC7CD5C481DCC9C85ebE478A1C0b69FCbb9')),
('arbitrum', 'ETH', lower('0x915A55e36A01285A14f05dE6e81ED9cE89772f8e'), lower('')),
('polygon', 'USDC', lower('0x1205f31718499dBf1fCa446663B532Ef87481fe1'), lower('0x2791Bca1f2de4661ED88A30C99A7a9449Aa84174')),
('polygon', 'USDT', lower('0x29e38769f23701A2e4A8Ef0492e19dA4604Be62c'), lower('0xc2132D05D31c914a87C6611C10748AEb04B58e8F')),
('avalanche', 'USDC', lower('0x1205f31718499dBf1fCa446663B532Ef87481fe1'), lower('0xB97EF9Ef8734C71904D8002F8b6Bc66Dd9c48a6E')),
('avalanche', 'USDT', lower('0x29e38769f23701A2e4A8Ef0492e19dA4604Be62c'), lower('0xc7198437980c041c805A1EDcbA50c1Ce5db95118')),
-- ('bsc', 'USDT', lower('0x9aA83081AA06AF7208Dcc7A4cB72C94d057D2cda'), lower('')),
-- ('bsc', 'BUSD', lower('0x98a5737749490856b401DB5Dc27F522fC314A4e1'), lower('')),
-- ('bsc', 'USDD', lower('0x4e145a589e4c03cBe3d28520e4BF3089834289Df'), lower('')),
('ethereum', 'USDC', lower('0xdf0770dF86a8034b3EFEf0A1Bb3c889B8332FF56'), lower('0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48')),
('ethereum', 'USDT', lower('0x38EA452219524Bb87e18dE1C24D3bB59510BD783'), lower('0xdAC17F958D2ee523a2206206994597C13D831ec7')),
('ethereum', 'USDD', lower('0x692953e758c3669290cb1677180c64183cEe374e'), lower('0x0C10bF8FcB7Bf5412187A595ab97a3609160b5c6')),
('ethereum', 'ETH', lower('0x101816545F6bd2b1076434B54383a1E633390A2E'), lower(''))
), tx_labels_op_t as (
SELECT
tx_hash as tx1,
CASE WHEN to_a = 1 and txns = 1 THEN 'Bridge from'
WHEN from_a = 1 and txns = 1 THEN 'Bridge to'
WHEN from_a = 1 and txns = 2 THEN 'Withdraw Liquidity'
WHEN to_a = 1 and txns = 2 THEN 'Deposit Liquidity' end as event
FROM (
SELECT
tx_hash,
COUNT(CASE WHEN to_address IN (SELECT column3 from tab0 WHERE column1 LIKE 'optimism') then 1 END) as to_a,
COUNT(CASE WHEN from_address IN (SELECT column3 from tab0 WHERE column1 LIKE 'optimism') THEN 1 END) as from_a,
count(*) as txns
FROM optimism.core.fact_token_transfers
WHERE tx_hash in (
SELECT
tx_hash
Run a query to Download Data