Sandeshnekodex swaps markets
Updated 2025-01-13
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 query analyzes cross-chain token swaps across multiple blockchain networks (Optimism, Ethereum, Base, and Arbitrum).
It decodes event logs to extract swap details, combines data from different chains, and calculates trading volumes and user counts.
Key features:
1. Decodes swap events from multiple blockchains
2. Identifies both intra-chain and inter-chain swaps
3. Joins swap data with token price information
4. Calculates trading volumes in USD
5. Aggregates data by trading pairs
*/
WITH decoded_trades AS
(
-- Extract swap data from different chains (Optimism, Ethereum, Base, Arbitrum)
-- and decode relevant fields (token addresses, user addresses, values)
WITH optimism_swaps AS
(
-- Decode Optimism intra-chain swaps
WITH intra_chain_swaps AS
(
SELECT
tx_hash
, block_timestamp
, regexp_substr_all(SUBSTR(data, 3), '.{64}') AS segmented_data
, concat('0x', substr(topics[1], -40)) AS swap_user
, 10 AS from_chain_id -- Hardcoding chain ID for Optimism
, 10 AS to_chain_id -- Hardcoding chain ID for Optimism
, concat('0x', substr(segmented_data[0], -40)) AS from_token
, concat('0x', substr(segmented_data[2], -40)) AS from_token_value
, utils.udf_hex_to_int(from_token_value) AS from_token_value_int
, concat('0x', substr(segmented_data[1], -40)) AS to_token
, concat('0x', substr(segmented_data[3], -40)) AS to_token_value
, utils.udf_hex_to_int(to_token_value) AS to_token_value_int
FROM
optimism.core.fact_event_logs
QueryRunArchived: QueryRun has been archived