WITH user_transactions AS ( SELECT ORIGIN_FROM_ADDRESS as user_address, REGEXP_SUBSTR_ALL(SUBSTR(DATA, 3), '.{64}') AS seg, topics[1] as pair, CASE WHEN topics[1] IN ( '0x574254432f555344430000000000000000000000000000000000000000000000', -- WBTC/USDC '0x4254432f55534443000000000000000000000000000000000000000000000000', -- BTC/USDC '0x574254432f455448000000000000000000000000000000000000000000000000' -- WBTC/ETH ) THEN TRY_CAST(livequery.utils.udf_hex_to_int('s2c', seg[2]::string) AS DECIMAL(38,8)) / 1e8 WHEN topics[1] IN ( '0x455552432f555344430000000000000000000000000000000000000000000000', -- EURC/USDC '0x555344542f555344430000000000000000000000000000000000000000000000', -- USDT/USDC '0x555344432f555344540000000000000000000000000000000000000000000000' -- USDC/USDT ) THEN TRY_CAST(livequery.utils.udf_hex_to_int('s2c', seg[2]::string) AS DECIMAL(38,6)) / 1e6 ELSE TRY_CAST(livequery.utils.udf_hex_to_int('s2c', seg[2]::string) AS DECIMAL(38,18)) / 1e18 END AS amount, CASE WHEN topics[1] IN ( '0x434f512f41564158000000000000000000000000000000000000000000000000', -- COQ/AVAX '0x73415641582f4156415800000000000000000000000000000000000000000000', -- sAVAX/AVAX '0x574254432f455448000000000000000000000000000000000000000000000000' -- WBTC/ETH ) THEN TRY_CAST(livequery.utils.udf_hex_to_int('s2c', seg[1]::string) AS DECIMAL(38,18)) / 1e18 ELSE TRY_CAST(livequery.utils.udf_hex_to_int('s2c', seg[1]::string) AS DECIMAL(38,6)) / 1e6 END AS trade_price, BLOCK_TIMESTAMP FROM avalanche.dexalot.fact_event_logs WHERE BLOCK_TIMESTAMP >= DATEADD(day, -30, CURRENT_DATE()) AND ORIGIN_FUNCTION_SIGNATURE = '0xae233dd3' AND CONTRACT_ADDRESS = '0x09383137c1eee3e1a8bc781228e4199f6b4a9bbf' AND topics[0] = '0x648b9a97caebd197770242b0387f3f5b8d22f9aef528a011296219238cd75adf'), market_prices AS ( SELECT SYMBOL, HOUR, PRICE as market_price FROM avalanche.price.ez_prices_hourly WHERE INSERTED_TIMESTAMP >= DATEADD(day, -30, CURRENT_DATE()) AND SYMBOL IN ('AVAX', 'ETH') ), user_total_volume AS ( SELECT user_address, SUM(CASE WHEN ut.pair IN ( '0x4554482f55534443000000000000000000000000000000000000000000000000', -- ETH/USDC '0x4554482f55534454000000000000000000000000000000000000000000000000', -- ETH/USDT '0x415641582f555344430000000000000000000000000000000000000000000000', -- AVAX/USDC '0x415641582f555344540000000000000000000000000000000000000000000000', -- AVAX/USDT '0x414c4f542f555344430000000000000000000000000000000000000000000000', -- ALOT/USDC '0x4152422f55534443000000000000000000000000000000000000000000000000', -- ARB/USDC '0x474d582f55534443000000000000000000000000000000000000000000000000', -- GMX/USDC '0x51492f5553444300000000000000000000000000000000000000000000000000', -- QI/USDC '0x574254432f555344430000000000000000000000000000000000000000000000', -- WBTC/USDC '0x4254432f55534443000000000000000000000000000000000000000000000000' -- BTC/USDC ) THEN NULLIF(ut.amount * ut.trade_price, 0) WHEN ut.pair IN ( '0x434f512f41564158000000000000000000000000000000000000000000000000', -- COQ/AVAX '0x73415641582f4156415800000000000000000000000000000000000000000000' -- sAVAX/AVAX ) THEN NULLIF(ut.amount * ut.trade_price * mp.market_price, 0) WHEN ut.pair = '0x574254432f455448000000000000000000000000000000000000000000000000' -- WBTC/ETH THEN NULLIF(ut.amount * ut.trade_price * mp.market_price, 0) ELSE 0 END) as total_volume FROM user_transactions ut LEFT JOIN market_prices mp ON CASE WHEN ut.pair IN ( '0x434f512f41564158000000000000000000000000000000000000000000000000', '0x73415641582f4156415800000000000000000000000000000000000000000000' ) THEN mp.SYMBOL = 'AVAX' WHEN ut.pair = '0x574254432f455448000000000000000000000000000000000000000000000000' THEN mp.SYMBOL = 'ETH' END AND DATE_TRUNC('hour', ut.BLOCK_TIMESTAMP) = mp.HOUR WHERE ut.amount > 0 AND ut.trade_price > 0 GROUP BY user_address HAVING total_volume > 0 ), volume_ranges AS ( SELECT CASE WHEN total_volume <= 10 THEN '0-10 USD' WHEN total_volume <= 100 THEN '11-100 USD' WHEN total_volume <= 500 THEN '101-500 USD' WHEN total_volume <= 1000 THEN '501-1,000 USD' WHEN total_volume <= 5000 THEN '1,001-5,000 USD' WHEN total_volume <= 10000 THEN '5,001-10,000 USD' WHEN total_volume <= 50000 THEN '10,001-50,000 USD' WHEN total_volume <= 100000 THEN '50,001-100,000 USD' ELSE 'Over 100,000 USD' END as volume_range, COUNT(DISTINCT user_address) as user_count, SUM(total_volume) as total_volume_in_range FROM user_total_volume GROUP BY CASE WHEN total_volume <= 10 THEN '0-10 USD' WHEN total_volume <= 100 THEN '11-100 USD' WHEN total_volume <= 500 THEN '101-500 USD' WHEN total_volume <= 1000 THEN '501-1,000 USD' WHEN total_volume <= 5000 THEN '1,001-5,000 USD' WHEN total_volume <= 10000 THEN '5,001-10,000 USD' WHEN total_volume <= 50000 THEN '10,001-50,000 USD' WHEN total_volume <= 100000 THEN '50,001-100,000 USD' ELSE 'Over 100,000 USD' END)SELECT volume_range, user_count, ROUND(user_count * 100.0 / SUM(user_count) OVER (), 2) AS percentage FROM volume_ranges ORDER BY CASE volume_range WHEN '0-10 USD' THEN 1 WHEN '11-100 USD' THEN 2 WHEN '101-500 USD' THEN 3 WHEN '501-1,000 USD' THEN 4 WHEN '1,001-5,000 USD' THEN 5 WHEN '5,001-10,000 USD' THEN 6 WHEN '10,001-50,000 USD' THEN 7 WHEN '50,001-100,000 USD' THEN 8 ELSE 9 END;

Query	Creator	Created
CONTRACT TOTAL TRADE STATUS copy 6 avalanche evm	yasmin	Dec 12, 2024
WEEKLY BRIDGE copy copy arbitrum base avalanche l2 evm	yasmin	Oct 3, 2024
ALL DATA copy copy avalanche evm	yasmin	Sep 21, 2024
Avalanche BenQi Tokens Stats copy avalanche evm	yasmin	Oct 3, 2024
traders based on AVAX trading volume avalanche evm	yasmin	Nov 12, 2024
Daily volume 7 avalanche evm	yasmin	Dec 12, 2024

yasmin