blank-tan

Fug603blank-tan

Updated 2024-08-19

-- Query Summary:

-- This query aggregates native token transfers from multiple blockchain networks, focusing on transfers originating from a specific address across different chains. It then calculates the number of transfers, unique receivers, and total volume in USD for transactions after April 29, 2024.

-- Optimization Summary:

-- 1. Use of Common Table Expression (CTE) is good for readability and maintainability.

-- 2. The UNION ALL operations are appropriate for combining data from different chains.

-- 3. The WHERE clause in the main query can be pushed down into the CTE for earlier filtering.

-- 4. Consider using partitioned tables if available for each chain to improve query performance.

-- Recommended Indexes:

-- For each chain's ez_native_transfers table:

-- 1. Index on ORIGIN_FROM_ADDRESS

-- 2. Composite index on (ORIGIN_FROM_ADDRESS, BLOCK_TIMESTAMP)

-- 3. If frequently querying by specific date ranges, consider a partitioned index on BLOCK_TIMESTAMP

WITH combined_data AS (

SELECT

'arbitrum' AS chain,

AMOUNT_USD,

TO_ADDRESS,

TX_HASH

FROM arbitrum.core.ez_native_transfers

WHERE ORIGIN_FROM_ADDRESS = '0x76dd65529dc6c073c1e0af2a5ecc78434bdbf7d9'

AND BLOCK_TIMESTAMP >= '2024-04-29'

UNION ALL

SELECT

'avalanche' AS chain,