Sandeshnekodex swaps base
Updated 2024-09-17
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
›
⌄
⌄
/*
This SQL query analyzes cross-chain and intra-chain token swaps across multiple blockchains
(Optimism, Ethereum, Base, and Arbitrum) over the past 30 days. It focuses on swaps
initiated by contracts created by a specific address, calculates token amounts and
USD values, and provides a comprehensive view of these transactions.
The query structure:
1. Gather swap data from each blockchain
2. Combine and filter the data
3. Add human-readable chain names
4. Join with price data to calculate USD values
5. Present the final results
*/
WITH optimism_swaps AS
-- Capture Optimism swaps, including both intra-chain and cross-chain transactions
(
-- Intra-chain swaps on Optimism (source and destination both on Optimism)
WITH intrachain_swaps AS
(
SELECT
tx_hash
, block_timestamp
, regexp_substr_all(SUBSTR(data, 3), '.{64}') AS decoded_data
, concat('0x', substr(topics[1], -40)) AS user_address
, 10 AS from_chain_id -- Hardcoding chain ID for Optimism
, 10 AS to_chain_id -- Both from and to chain are Optimism
, concat('0x', substr(decoded_data[0], -40)) AS from_token_address
, concat('0x', substr(decoded_data[2], -40)) AS from_token_amount_hex
, utils.udf_hex_to_int(from_token_amount_hex) AS from_token_amount
, concat('0x', substr(decoded_data[1], -40)) AS to_token_address
, concat('0x', substr(decoded_data[3], -40)) AS to_token_amount_hex
, utils.udf_hex_to_int(to_token_amount_hex) AS to_token_amount
FROM optimism.core.fact_event_logs
WHERE 1=1
AND contract_address = '0x6cca65f0bc3140019a81a1619aa75a6925c2601e'
QueryRunArchived: QueryRun has been archived