WITH fee_refund AS ( SELECT SUBSTR(topics[2], 27, 40) as aa , tx_hash FROM arbitrum.core.fact_event_logs WHERE contract_address = '0xc8ee91a54287db53897056e12d9819156d3822fb' AND topics[0] = '0x137a44067c8961cd7e1d876f4754a5a3a75989b4552f1843fc69c3b372def160' AND topics[1] = '0xe6db92bfa9c5428bfb1001511cc8b0376a77baf28e4b374949b9770bbbb799a0' AND block_timestamp >= '2023-07-04' AND block_timestamp < current_date ) , tx as ( SELECT block_timestamp, 'arbitrum' AS blockchain, tx_hash, SUBSTR(data, 91, 40) as aa -- data1 FROM arbitrum.core.fact_event_logs WHERE contract_address = lower('0x489ee077994b6658eafa855c308275ead8097c4a') --Vault AND topics[0] in ( lower('0x2fe68525253654c21998f35787a8d0f361905ef647c854092430ab65f2f15022') --IncreasePosition , lower('0x93d75d64d1f84fc6f430a64fc578bdd4c1e090e90ea2d51773e626d19de56d30') --DecreasePosition ) AND block_timestamp >= '2021-08-31' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, 'avalanche' AS blockchain, tx_hash, SUBSTR(data, 91, 40) as aa -- data1 FROM avalanche.core.fact_event_logs WHERE contract_address = lower('0x9ab2de34a33fb459b538c43f251eb825645e8595') --Vault AND topics[0] in ( lower('0x2fe68525253654c21998f35787a8d0f361905ef647c854092430ab65f2f15022') --IncreasePosition , lower('0x93d75d64d1f84fc6f430a64fc578bdd4c1e090e90ea2d51773e626d19de56d30') --DecreasePosition ) AND block_timestamp >= '2021-12-17' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, 'arbitrum' AS blockchain, tx_hash, SUBSTR(data, 27, 40) as aa -- data0 FROM arbitrum.core.fact_event_logs WHERE contract_address = lower('0x489ee077994b6658eafa855c308275ead8097c4a') --Vault AND topics[0] = lower('0x0874b2d545cb271cdbda4e093020c452328b24af12382ed62c4d00f5c26709db') --Swap AND block_timestamp >= '2021-08-31' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, 'avalanche' AS blockchain, tx_hash, SUBSTR(data, 27, 40) as aa -- data0 FROM avalanche.core.fact_event_logs WHERE contract_address = lower('0x9ab2de34a33fb459b538c43f251eb825645e8595') --Vault AND topics[0] = lower('0x0874b2d545cb271cdbda4e093020c452328b24af12382ed62c4d00f5c26709db') --Swap AND block_timestamp >= '2021-12-17' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, 'arbitrum' AS blockchain, tx_hash, SUBSTR(data, 27, 40) as aa -- data0 FROM arbitrum.core.fact_event_logs WHERE contract_address = lower('0x489ee077994b6658eafa855c308275ead8097c4a') --Vault AND topics[0] = lower('0xab4c77c74cd32c85f35416cf03e7ce9e2d4387f7b7f2c1f4bf53daaecf8ea72d') -- BuyUSDG AND block_timestamp >= '2021-08-31' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, 'arbitrum' AS blockchain, tx_hash, SUBSTR(data, 27, 40) as aa -- data0 FROM arbitrum.core.fact_event_logs WHERE contract_address = lower('0x489ee077994b6658eafa855c308275ead8097c4a') --Vault AND topics[0] = lower('0xd732b7828fa6cee72c285eac756fc66a7477e3dc22e22e7c432f1c265d40b483') -- SellUSDG AND block_timestamp >= '2021-08-31' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, 'avalanche' AS blockchain, tx_hash, SUBSTR(data, 27, 40) as aa -- data0 FROM avalanche.core.fact_event_logs WHERE contract_address = lower('0x9ab2de34a33fb459b538c43f251eb825645e8595') --Vault AND topics[0] = lower('0xab4c77c74cd32c85f35416cf03e7ce9e2d4387f7b7f2c1f4bf53daaecf8ea72d') -- BuyUSDG AND block_timestamp >= '2021-12-17' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, 'avalanche' AS blockchain, tx_hash, SUBSTR(data, 27, 40) as aa -- data0 FROM avalanche.core.fact_event_logs WHERE contract_address = lower('0x9ab2de34a33fb459b538c43f251eb825645e8595') --Vault AND topics[0] = lower('0xd732b7828fa6cee72c285eac756fc66a7477e3dc22e22e7c432f1c265d40b483') -- SellUSDG AND block_timestamp >= '2021-12-17' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, 'arbitrum' AS blockchain, tx_hash, SUBSTR(topics[2], 27, 40) as aa FROM arbitrum.core.fact_event_logs WHERE contract_address = '0xc8ee91a54287db53897056e12d9819156d3822fb' AND topics[0] = '0x137a44067c8961cd7e1d876f4754a5a3a75989b4552f1843fc69c3b372def160' --increase position AND topics[1] = '0xf94196ccb31f81a3e67df18f2a62cbfb50009c80a7d3c728a3f542e3abc5cb63' AND block_timestamp >= '2023-07-04' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, 'arbitrum' AS blockchain, tx_hash, SUBSTR(topics[2], 27, 40) as aa FROM arbitrum.core.fact_event_logs WHERE contract_address = '0xc8ee91a54287db53897056e12d9819156d3822fb' AND topics[0] = '0x137a44067c8961cd7e1d876f4754a5a3a75989b4552f1843fc69c3b372def160' --decrease position AND topics[1] = '0x07d51b51b408d7c62dcc47cc558da5ce6a6e0fd129a427ebce150f52b0e5171a' --decrease position AND block_timestamp >= '2023-07-04' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, 'avalanche' AS blockchain, tx_hash, SUBSTR(topics[2], 27, 40) as aa FROM avalanche.core.fact_event_logs WHERE contract_address = '0xdb17b211c34240b014ab6d61d4a31fa0c0e20c26' AND topics[0] = '0x137a44067c8961cd7e1d876f4754a5a3a75989b4552f1843fc69c3b372def160' --increase position AND topics[1] = '0xf94196ccb31f81a3e67df18f2a62cbfb50009c80a7d3c728a3f542e3abc5cb63' AND block_timestamp >= '2023-07-04' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, 'avalanche' AS blockchain, tx_hash, SUBSTR(topics[2], 27, 40) as aa FROM avalanche.core.fact_event_logs WHERE contract_address = '0xdb17b211c34240b014ab6d61d4a31fa0c0e20c26' AND topics[0] = '0x137a44067c8961cd7e1d876f4754a5a3a75989b4552f1843fc69c3b372def160' --decrease position AND topics[1] = '0x07d51b51b408d7c62dcc47cc558da5ce6a6e0fd129a427ebce150f52b0e5171a' --decrease position AND block_timestamp >= '2023-07-04' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, 'arbitrum' AS blockchain, a.tx_hash, b.aa as aa FROM arbitrum.core.fact_event_logs a JOIN fee_refund b ON a.tx_hash = b.tx_hash WHERE contract_address = '0xc8ee91a54287db53897056e12d9819156d3822fb' AND topics[0] = '0x137a44067c8961cd7e1d876f4754a5a3a75989b4552f1843fc69c3b372def160' --swap AND topics[1] = '0xf35c99b746450c623be607459294d15f458678f99d535718db6cfcbccb117c09' --swap AND block_timestamp >= '2023-07-04' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, 'avalanche' AS blockchain, a.tx_hash, b.aa as aa FROM avalanche.core.fact_event_logs a JOIN fee_refund b ON a.tx_hash = b.tx_hash WHERE contract_address = '0xdb17b211c34240b014ab6d61d4a31fa0c0e20c26' AND topics[0] = '0x137a44067c8961cd7e1d876f4754a5a3a75989b4552f1843fc69c3b372def160' --swap AND topics[1] = '0xf35c99b746450c623be607459294d15f458678f99d535718db6cfcbccb117c09' --swap AND block_timestamp >= '2023-07-04' AND block_timestamp < current_date ) , blockchains AS ( SELECT 'arbitrum' AS blockchain UNION ALL SELECT 'avalanche' AS blockchain ) , txaab AS ( SELECT date_trunc('day', block_timestamp) as date, blockchain, APPROX_COUNT_DISTINCT(tx_hash) as tx, APPROX_COUNT_DISTINCT(aa) as aa FROM tx WHERE block_timestamp >= current_date - 7 GROUP BY 1,2 ) , nab AS ( SELECT date, blockchain, APPROX_COUNT_DISTINCT(aa) as na FROM ( SELECT min(date_trunc('day', block_timestamp)) AS date, blockchain, aa FROM tx WHERE block_timestamp >= current_date - 730 GROUP BY blockchain, aa ) WHERE date >= current_date - 7 GROUP BY 1,2 ) , ma_dates AS ( SELECT DATEADD('second', 86399, current_date - 7)::TIMESTAMP as end_date, DATEADD('day', -30, end_date)::TIMESTAMP as start_date UNION ALL SELECT DATEADD(day, 1, end_date) as end_date, DATEADD('day', 1, start_date) as start_date FROM ma_dates WHERE DATEADD('day', 1, end_date) <= DATEADD('second', 86399, current_date - 1) ) , mab AS ( SELECT date_trunc('day', end_date) AS date, blockchain, APPROX_COUNT_DISTINCT(aa) as ma FROM ( SELECT block_timestamp, blockchain, aa FROM tx WHERE block_timestamp >= current_date - 37 ) JOIN ma_dates ON block_timestamp BETWEEN start_date AND end_date GROUP BY 1,2 ) , prices AS ( SELECT date_trunc('day', hour) AS date , 'ETH' AS chain , AVG(price) AS price FROM ethereum.price.ez_prices_hourly WHERE token_address = lower('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2') -- WETH AND hour >= current_date - 7 AND hour < current_date GROUP BY 1,2 UNION ALL SELECT date_trunc('day', hour) AS date , 'AVAX' AS chain , AVG(price) AS price FROM avalanche.price.ez_prices_hourly WHERE token_address = '0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7' -- WAVAX AND hour >= current_date - 7 AND hour < current_date GROUP BY 1,2 ) , dgb AS ( SELECT date_trunc('day', block_timestamp) AS date, blockchain, SUM(tx_fee * price) AS dg FROM ( SELECT block_timestamp, 'arbitrum' AS blockchain, 'ETH' AS chain, tx_fee FROM arbitrum.core.fact_transactions WHERE to_address IN ( lower('0xaBBc5F99639c9B6bCb58544ddf04EFA6802F4064') --router , lower('0xb87a436B93fFE9D75c5cFA7bAcFff96430b09868') --PositionRouter , lower('0x3B070aA6847bd0fB56eFAdB351f49BBb7619dbc2') --ExchangeRouter , lower('0x51e210dC8391728E2017B2Ec050e40b2f458090e') --OrderHandler ) AND block_timestamp >= current_date - 7 AND block_timestamp < current_date UNION ALL SELECT block_timestamp, 'avalanche' AS blockchain, 'AVAX' AS chain, tx_fee FROM avalanche.core.fact_transactions WHERE to_address IN ( lower('0x5F719c2F1095F7B9fc68a68e35B51194f4b6abe8') --router , lower('0xffF6D276Bc37c61A23f06410Dce4A400f66420f8') --PositionRouter , lower('0x79be2F4eC8A4143BaF963206cF133f3710856D0a') --ExchangeRouter , lower('0x5dDfaC1aA195FBfb72B06d3E4FC387BD11fce82F') --OrderHandler ) AND block_timestamp >= current_date - 7 AND block_timestamp < current_date ) t JOIN prices p ON p.date = date_trunc('day', t.block_timestamp) AND p.chain = t.chain GROUP BY 1,2 ) , arb_tokens_v1 (contract_address, symbol, decimals) AS ( SELECT '0xff970a61a04b1ca14834a43f5de4533ebddb5cc8' AS contract_address, 'USDC' AS symbol, 6::INT AS decimals UNION ALL SELECT '0xaf88d065e77c8cc2239327c5edb3a432268e5831' AS contract_address, 'USDC' AS symbol, 6::INT AS decimals UNION ALL SELECT '0x82af49447d8a07e3bd95bd0d56f35241523fbab1' AS contract_address, 'WETH' AS symbol, 18::INT AS decimals UNION ALL SELECT '0xda10009cbd5d07dd0cecc66161fc93d7c9000da1' AS contract_address, 'DAI' AS symbol, 18::INT AS decimals UNION ALL SELECT '0xf97f4df75117a78c1a5a0dbb814af92458539fb4' AS contract_address, 'LINK' AS symbol, 18::INT AS decimals UNION ALL SELECT '0xfd086bc7cd5c481dcc9c85ebe478a1c0b69fcbb9' AS contract_address, 'USDT' AS symbol, 6::INT AS decimals UNION ALL SELECT '0xfa7f8980b0f1e64a2062791cc3b0871572f1f7f0' AS contract_address, 'UNI' AS symbol, 18::INT AS decimals UNION ALL SELECT '0xfea7a6a0b346362bf88a9e4a88416b77a57d6c2a' AS contract_address, 'MIM' AS symbol, 18::INT AS decimals UNION ALL SELECT '0x17fc002b466eec40dae837fc4be5c67993ddbd6f' AS contract_address, 'FRAX' AS symbol, 18::INT AS decimals UNION ALL SELECT '0x2f2a2543b76a4166549f7aab2e75bef0aefc5b0f' AS contract_address, 'WBTC' AS symbol, 8::INT AS decimals ) , ava_tokens_v1 AS ( SELECT '0x49d5c2bdffac6ce2bfdb6640f4f80f226bc10bab' AS contract_address, 'WETH' AS symbol, 18::INT AS decimals UNION ALL SELECT '0x50b7545627a5162f82a992c33b87adc75187b218' AS contract_address, 'WBTC' AS symbol, 8::INT AS decimals UNION ALL SELECT '0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7' AS contract_address, 'AVAX' AS symbol, 18::INT AS decimals UNION ALL SELECT '0x152b9d0fdc40c096757f570a51e494bd4b943e50' AS contract_address, 'WBTC' AS symbol, 8::INT AS decimals UNION ALL SELECT '0xa7d7079b0fead91f3e65f86e8915cb59c1a4c664' AS contract_address, 'USDC' AS symbol, 6::INT AS decimals UNION ALL SELECT '0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e' AS contract_address, 'USDC' AS symbol, 6::INT AS decimals UNION ALL SELECT '0x130966628846bfd36ff31a822705796e8cb8c18d' AS contract_address, 'MIM' AS symbol, 18::INT AS decimals ) , arb_prices AS ( SELECT date_trunc('day', hour) AS date , token_address , decimals , AVG(price) AS price FROM arbitrum.price.ez_prices_hourly WHERE token_address IN (SELECT contract_address FROM arb_tokens_v1) AND hour >= current_date - 7 AND hour < current_date GROUP BY 1,2,3 ) , ava_prices AS ( SELECT date_trunc('day', hour) AS date , token_address , decimals , AVG(price) AS price FROM avalanche.price.ez_prices_hourly WHERE token_address IN (SELECT contract_address FROM ava_tokens_v1) AND hour >= current_date - 7 AND hour < current_date GROUP BY 1,2,3 ) , arb_perpetual_trades AS ( SELECT block_timestamp , 'arbitrum' AS blockchain , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*5, 64))::FLOAT /1e30 AS tv -- sizeDelta, data5 , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*8, 64))::FLOAT /1e30 AS tf -- fee, data8 FROM arbitrum.core.fact_event_logs WHERE contract_address = lower('0x489ee077994b6658eafa855c308275ead8097c4a') --Vault AND topics[0] in ( lower('0x2fe68525253654c21998f35787a8d0f361905ef647c854092430ab65f2f15022') --IncreasePosition , lower('0x93d75d64d1f84fc6f430a64fc578bdd4c1e090e90ea2d51773e626d19de56d30') --DecreasePosition ) AND block_timestamp >= current_date - 7 AND block_timestamp < current_date ) , ava_perpetual_trades AS ( SELECT block_timestamp , 'avalanche' AS blockchain , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*5, 64))::FLOAT /1e30 AS tv -- sizeDelta, data5 , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*8, 64))::FLOAT /1e30 AS tf -- fee, data8 FROM avalanche.core.fact_event_logs WHERE contract_address = lower('0x9ab2de34a33fb459b538c43f251eb825645e8595') --Vault AND topics[0] in ( lower('0x2fe68525253654c21998f35787a8d0f361905ef647c854092430ab65f2f15022') --IncreasePosition , lower('0x93d75d64d1f84fc6f430a64fc578bdd4c1e090e90ea2d51773e626d19de56d30') --DecreasePosition ) AND block_timestamp >= current_date - 7 AND block_timestamp < current_date ) , arb_swap_trades AS ( SELECT block_timestamp , 'arbitrum' AS blockchain , a.tv / pow(10, p.decimals) * p.price AS tv , a.tf / pow(10, p.decimals) * p.price AS tf , a.token_out_address AS contract_address FROM ( SELECT block_timestamp , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*5, 64))::FLOAT AS tv -- amountOutAftertf, data5 , (ethereum.public.udf_hex_to_int(substr(data, 3 + 64*4, 64))::FLOAT - ethereum.public.udf_hex_to_int(substr(data, 3 + 64*5, 64))::FLOAT) AS tf -- amountOut, data4 minus amountOutAftertf, data5 , CONCAT('0x', SUBSTR(data, 155, 40)) AS token_out_address FROM arbitrum.core.fact_event_logs WHERE contract_address = lower('0x489ee077994b6658eafa855c308275ead8097c4a') --Vault AND topics[0] = lower('0x0874b2d545cb271cdbda4e093020c452328b24af12382ed62c4d00f5c26709db') -- Swap AND block_timestamp >= current_date - 7 AND block_timestamp < current_date ) a LEFT JOIN arb_prices p ON date_trunc('day', a.block_timestamp) = p.date AND a.token_out_address = p.token_address ) , ava_swap_trades AS ( SELECT block_timestamp , 'avalanche' AS blockchain , a.tv / pow(10, p.decimals) * p.price AS tv , a.tf / pow(10, p.decimals) * p.price AS tf , a.token_out_address AS contract_address FROM ( SELECT block_timestamp , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*5, 64))::FLOAT AS tv -- amountOutAftertf, data5 , (ethereum.public.udf_hex_to_int(substr(data, 3 + 64*4, 64))::FLOAT - ethereum.public.udf_hex_to_int(substr(data, 3 + 64*5, 64))::FLOAT) AS tf -- amountOut, data4 minus amountOutAftertf, data5 , CONCAT('0x', SUBSTR(data, 155, 40)) AS token_out_address FROM avalanche.core.fact_event_logs WHERE contract_address = lower('0x9ab2de34a33fb459b538c43f251eb825645e8595') --Vault AND topics[0] = lower('0x0874b2d545cb271cdbda4e093020c452328b24af12382ed62c4d00f5c26709db') -- Swap AND block_timestamp >= current_date - 7 AND block_timestamp < current_date ) a LEFT JOIN ava_prices p ON date_trunc('day', a.block_timestamp) = p.date AND a.token_out_address = p.token_address ) , arb_glp_trading AS ( SELECT block_timestamp , 'arbitrum' AS blockchain , tv , tf FROM ( SELECT block_timestamp , (ethereum.public.udf_hex_to_int(substr(data, 3 + 64*3, 64))::FLOAT / 1e18) AS tv -- data3, usdgAmount , ((ethereum.public.udf_hex_to_int(substr(data, 3 + 64*4, 64))::FLOAT / 100) / 100 * ethereum.public.udf_hex_to_int(substr(data, 3 + 64*3, 64))::FLOAT / 1e18) AS tf -- data4, feeBasisPoints. data3, usdgAmount FROM arbitrum.core.fact_event_logs WHERE contract_address = lower('0x489ee077994b6658eafa855c308275ead8097c4a') --Vault AND topics[0] = lower('0xab4c77c74cd32c85f35416cf03e7ce9e2d4387f7b7f2c1f4bf53daaecf8ea72d') -- BuyUSDG AND block_timestamp >= current_date - 7 AND block_timestamp < current_date UNION ALL SELECT block_timestamp , (ethereum.public.udf_hex_to_int(substr(data, 3 + 64*3, 64))::FLOAT / 1e18) AS tv -- data3, usdgAmount , ((ethereum.public.udf_hex_to_int(substr(data, 3 + 64*4, 64))::FLOAT / 100) / 100 * ethereum.public.udf_hex_to_int(substr(data, 3 + 64*3, 64))::FLOAT / 1e18) AS tf -- data4, feeBasisPoints. data3, usdgAmount FROM arbitrum.core.fact_event_logs WHERE contract_address = lower('0x489ee077994b6658eafa855c308275ead8097c4a') --Vault AND topics[0] = lower('0xd732b7828fa6cee72c285eac756fc66a7477e3dc22e22e7c432f1c265d40b483') -- SellUSDG AND block_timestamp >= current_date - 7 AND block_timestamp < current_date ) ) , ava_glp_trading AS ( SELECT block_timestamp , 'avalanche' AS blockchain , tv , tf FROM ( SELECT block_timestamp , (ethereum.public.udf_hex_to_int(substr(data, 3 + 64*3, 64))::FLOAT / 1e18) AS tv -- data3, usdgAmount , ((ethereum.public.udf_hex_to_int(substr(data, 3 + 64*4, 64))::FLOAT / 100) / 100 * ethereum.public.udf_hex_to_int(substr(data, 3 + 64*3, 64))::FLOAT / 1e18) AS tf-- data4, feeBasisPoints. data3, usdgAmount FROM avalanche.core.fact_event_logs WHERE contract_address = lower('0x9ab2de34a33fb459b538c43f251eb825645e8595') --Vault AND topics[0] = lower('0xab4c77c74cd32c85f35416cf03e7ce9e2d4387f7b7f2c1f4bf53daaecf8ea72d') -- BuyUSDG AND block_timestamp >= current_date - 7 AND block_timestamp < current_date UNION ALL SELECT block_timestamp AS date , (ethereum.public.udf_hex_to_int(substr(data, 3 + 64*3, 64))::FLOAT / 1e18) AS tv -- data3, usdgAmount , ((ethereum.public.udf_hex_to_int(substr(data, 3 + 64*4, 64))::FLOAT / 100) / 100 * ethereum.public.udf_hex_to_int(substr(data, 3 + 64*3, 64))::FLOAT / 1e18) AS tf-- data4, feeBasisPoints. data3, usdgAmount FROM avalanche.core.fact_event_logs WHERE contract_address = lower('0x9ab2de34a33fb459b538c43f251eb825645e8595') --Vault AND topics[0] = lower('0xd732b7828fa6cee72c285eac756fc66a7477e3dc22e22e7c432f1c265d40b483') -- SellUSDG AND block_timestamp >= current_date - 7 AND block_timestamp < current_date ) ) , arb_liq AS ( SELECT date_trunc('day', block_timestamp) AS date , 'arbitrum' AS blockchain , tf , SUM(ethereum.public.udf_hex_to_int(substr(a.data, 3 + 64*5, 64))::FLOAT / 1e30) AS tv -- data5, size FROM arbitrum.core.fact_event_logs a LEFT JOIN ( SELECT date , SUM(tf) AS tf FROM ( SELECT date_trunc('day', block_timestamp) AS date , (ethereum.public.udf_hex_to_int(substr(data, 3 + 64*1, 64))::FLOAT / 1e30) AS tf -- data1, feeUsd FROM arbitrum.core.fact_event_logs WHERE contract_address = lower('0x489ee077994b6658eafa855c308275ead8097c4a') --Vault AND topics[0] = lower('0x5d0c0019d3d45fadeb74eff9d2c9924d146d000ac6bcf3c28bf0ac3c9baa011a') -- CollectMarginFees AND block_timestamp >= current_date - 7 AND block_timestamp < current_date UNION ALL SELECT block_timestamp AS date , -tf FROM arb_perpetual_trades ) GROUP BY 1 ) b ON date_trunc('day', a.block_timestamp) = b.date WHERE a.contract_address = lower('0x489ee077994b6658eafa855c308275ead8097c4a') --Vault AND a.topics[0] = lower('0x2e1f85a64a2f22cf2f0c42584e7c919ed4abe8d53675cff0f62bf1e95a1c676f') -- LiquidatePosition AND a.block_timestamp >= current_date - 7 AND a.block_timestamp < current_date GROUP BY 1,2,3 ) , ava_liq AS ( SELECT date_trunc('day', block_timestamp) AS date , 'avalanche' AS blockchain , tf , SUM(ethereum.public.udf_hex_to_int(substr(a.data, 3 + 64*5, 64))::FLOAT / 1e30) AS tv -- data5, size FROM avalanche.core.fact_event_logs a LEFT JOIN ( SELECT date , SUM(tf) AS tf FROM ( SELECT date_trunc('day', block_timestamp) AS date , (ethereum.public.udf_hex_to_int(substr(data, 3 + 64*1, 64))::FLOAT / 1e30) AS tf -- data1, feeUsd FROM avalanche.core.fact_event_logs WHERE contract_address = lower('0x9ab2de34a33fb459b538c43f251eb825645e8595') --Vault AND topics[0] = lower('0x5d0c0019d3d45fadeb74eff9d2c9924d146d000ac6bcf3c28bf0ac3c9baa011a') -- CollectMarginFees AND block_timestamp >= current_date - 7 AND block_timestamp < current_date UNION ALL SELECT block_timestamp AS date , -tf FROM ava_perpetual_trades ) GROUP BY 1 ) b ON date_trunc('day', a.block_timestamp) = b.date WHERE a.contract_address = lower('0x9ab2de34a33fb459b538c43f251eb825645e8595') --Vault AND a.topics[0] = lower('0x2e1f85a64a2f22cf2f0c42584e7c919ed4abe8d53675cff0f62bf1e95a1c676f') -- LiquidatePosition AND a.block_timestamp >= current_date - 7 AND a.block_timestamp < current_date GROUP BY 1,2,3 ) , all_liq AS ( SELECT date AS block_timestamp, blockchain, tv, tf FROM arb_liq UNION ALL SELECT date AS block_timestamp, blockchain, tv, tf FROM ava_liq ) , v2_trades AS ( SELECT block_timestamp , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*100, 64))::FLOAT / 1e30 AS tv , 0.0007 * ethereum.public.udf_hex_to_int(substr(data, 3 + 64*100, 64))::FLOAT / 1e30 + ABS(utils.udf_hex_to_int('s2c', substr(data, 3 + 64*123, 64))::FLOAT / 1e30) AS tf , 'arbitrum' AS blockchain FROM arbitrum.core.fact_event_logs WHERE contract_address = '0xc8ee91a54287db53897056e12d9819156d3822fb' AND topics[0] = '0x137a44067c8961cd7e1d876f4754a5a3a75989b4552f1843fc69c3b372def160' --increase position AND topics[1] = '0xf94196ccb31f81a3e67df18f2a62cbfb50009c80a7d3c728a3f542e3abc5cb63' AND block_timestamp >= current_date - 7 AND block_timestamp < current_date UNION ALL SELECT block_timestamp , 0 AS tv , price * ethereum.public.udf_hex_to_int(substr(data, 3 + 64*26, 64))::FLOAT /1e18 AS tf --execution fee , 'arbitrum' AS blockchain FROM arbitrum.core.fact_event_logs JOIN prices p ON date_trunc('day', block_timestamp) = p.date AND p.chain = 'ETH' WHERE contract_address = '0xc8ee91a54287db53897056e12d9819156d3822fb' AND topics[0] = '0x137a44067c8961cd7e1d876f4754a5a3a75989b4552f1843fc69c3b372def160' --increase position AND topics[1] = '0x57f0018c9e19829fa2f55e53969d49e96f7bc3936cd7453806b7cd0eaf5593ca' AND block_timestamp >= current_date - 7 AND block_timestamp < current_date UNION ALL SELECT block_timestamp , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*102, 64))::FLOAT / 1e30 AS tv , ABS(utils.udf_hex_to_int('s2c', substr(data, 3 + 64*129, 64))::FLOAT / 1e30) AS tf , 'arbitrum' AS blockchain FROM arbitrum.core.fact_event_logs WHERE contract_address = '0xc8ee91a54287db53897056e12d9819156d3822fb' AND topics[0] = '0x137a44067c8961cd7e1d876f4754a5a3a75989b4552f1843fc69c3b372def160' --decrease position AND topics[1] = '0x07d51b51b408d7c62dcc47cc558da5ce6a6e0fd129a427ebce150f52b0e5171a' --decrease position AND ethereum.public.udf_hex_to_int(substr(data, 3 + 64*118, 64))::INT != 7::INT AND block_timestamp >= current_date - 7 AND block_timestamp < current_date UNION ALL SELECT block_timestamp , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*100, 64))::FLOAT / 1e30 AS tv , 0.0007 * ethereum.public.udf_hex_to_int(substr(data, 3 + 64*100, 64))::FLOAT / 1e30 + ABS(utils.udf_hex_to_int('s2c', substr(data, 3 + 64*123, 64))::FLOAT / 1e30) AS tf , 'avalanche' AS blockchain FROM avalanche.core.fact_event_logs WHERE contract_address = '0xdb17b211c34240b014ab6d61d4a31fa0c0e20c26' AND topics[0] = '0x137a44067c8961cd7e1d876f4754a5a3a75989b4552f1843fc69c3b372def160' --increase position AND topics[1] = '0xf94196ccb31f81a3e67df18f2a62cbfb50009c80a7d3c728a3f542e3abc5cb63' AND block_timestamp >= current_date - 7 AND block_timestamp < current_date UNION ALL SELECT block_timestamp , 0 AS tv , price * ethereum.public.udf_hex_to_int(substr(data, 3 + 64*26, 64))::FLOAT /1e18 AS tf --execution fee , 'avalanche' AS blockchain FROM avalanche.core.fact_event_logs JOIN prices p ON date_trunc('day', block_timestamp) = p.date AND p.chain = 'AVAX' WHERE contract_address = '0xdb17b211c34240b014ab6d61d4a31fa0c0e20c26' AND topics[0] = '0x137a44067c8961cd7e1d876f4754a5a3a75989b4552f1843fc69c3b372def160' --increase position AND topics[1] = '0x57f0018c9e19829fa2f55e53969d49e96f7bc3936cd7453806b7cd0eaf5593ca' AND block_timestamp >= current_date - 7 AND block_timestamp < current_date UNION ALL SELECT block_timestamp , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*102, 64))::FLOAT / 1e30 AS tv , ABS(utils.udf_hex_to_int('s2c', substr(data, 3 + 64*129, 64))::FLOAT / 1e30) AS tf , 'avalanche' AS blockchain FROM avalanche.core.fact_event_logs WHERE contract_address = '0xdb17b211c34240b014ab6d61d4a31fa0c0e20c26' AND topics[0] = '0x137a44067c8961cd7e1d876f4754a5a3a75989b4552f1843fc69c3b372def160' --decrease position AND topics[1] = '0x07d51b51b408d7c62dcc47cc558da5ce6a6e0fd129a427ebce150f52b0e5171a' --decrease position AND ethereum.public.udf_hex_to_int(substr(data, 3 + 64*118, 64))::INT != 7::INT AND block_timestamp >= current_date - 7 AND block_timestamp < current_date ) , v2_liq AS ( SELECT block_timestamp , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*102, 64))::FLOAT / 1e30 AS size , ABS(utils.udf_hex_to_int('s2c', substr(data, 3 + 64*129, 64))::FLOAT / 1e30) AS tf , 'arbitrum' AS blockchain FROM arbitrum.core.fact_event_logs WHERE contract_address = '0xc8ee91a54287db53897056e12d9819156d3822fb' AND topics[0] = '0x137a44067c8961cd7e1d876f4754a5a3a75989b4552f1843fc69c3b372def160' AND topics[1] = '0x07d51b51b408d7c62dcc47cc558da5ce6a6e0fd129a427ebce150f52b0e5171a' AND ethereum.public.udf_hex_to_int(substr(data, 3 + 64*118, 64))::INT = 7::INT AND block_timestamp >= current_date - 7 AND block_timestamp < current_date UNION ALL SELECT block_timestamp , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*102, 64))::FLOAT / 1e30 AS size , ABS(utils.udf_hex_to_int('s2c', substr(data, 3 + 64*129, 64))::FLOAT / 1e30) AS tf , 'avalanche' AS blockchain FROM avalanche.core.fact_event_logs WHERE contract_address = '0xdb17b211c34240b014ab6d61d4a31fa0c0e20c26' AND topics[0] = '0x137a44067c8961cd7e1d876f4754a5a3a75989b4552f1843fc69c3b372def160' AND topics[1] = '0x07d51b51b408d7c62dcc47cc558da5ce6a6e0fd129a427ebce150f52b0e5171a' AND ethereum.public.udf_hex_to_int(substr(data, 3 + 64*118, 64))::INT = 7::INT AND block_timestamp >= current_date - 7 AND block_timestamp < current_date ) , arb_tokens_v2 (contract_address, symbol, decimals, price_decimals) AS ( SELECT '0xaf88d065e77c8cC2239327C5EDb3A432268e5831' AS contract_address, 'USDC' AS symbol, 6::INT AS decimals, 24::INT AS decimals UNION ALL SELECT '0x2bcc6d6cdbbdc0a4071e48bb3b969b06b3330c07' AS contract_address, 'SOL' AS symbol, 9::INT AS decimals, 21::INT AS price_decimals UNION ALL SELECT '0x912ce59144191c1204e64559fe8253a0e49e6548' AS contract_address, 'ARB' AS symbol, 18::INT AS decimals, 12::INT AS price_decimals UNION ALL SELECT '0xff970a61a04b1ca14834a43f5de4533ebddb5cc8' AS contract_address, 'USDC' AS symbol, 6::INT AS decimals, 24::INT AS price_decimals UNION ALL SELECT '0xda10009cbd5d07dd0cecc66161fc93d7c9000da1' AS contract_address, 'DAI' AS symbol, 18::INT AS decimals, 12::INT AS price_decimals UNION ALL SELECT '0x82af49447d8a07e3bd95bd0d56f35241523fbab1' AS contract_address, 'WETH' AS symbol, 18::INT AS decimals, 12::INT AS price_decimals UNION ALL SELECT '0x2f2a2543b76a4166549f7aab2e75bef0aefc5b0f' AS contract_address, 'WBTC' AS symbol, 8::INT AS decimals, 22::INT AS price_decimals UNION ALL SELECT '0xf97f4df75117a78c1a5a0dbb814af92458539fb4' AS contract_address, 'LINK' AS symbol, 18::INT AS decimals, 12::INT AS price_decimals UNION ALL SELECT '0xfd086bc7cd5c481dcc9c85ebe478a1c0b69fcbb9' AS contract_address, 'USDT' AS symbol, 6::INT AS decimals, 24::INT AS price_decimals UNION ALL SELECT '0xfa7f8980b0f1e64a2062791cc3b0871572f1f7f0' AS contract_address, 'UNI' AS symbol, 18::INT AS decimals, 12::INT ASprice_decimals ) , avax_tokens_v2 (contract_address, symbol, decimals, price_decimals) AS ( SELECT '0x49d5c2bdffac6ce2bfdb6640f4f80f226bc10bab' AS contract_address, 'WETH' AS symbol, 18::INT AS decimals, 12::INT AS decimals UNION ALL SELECT '0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7' AS contract_address, 'AVAX' AS symbol, 18::INT AS decimals, 12::INT AS price_decimals UNION ALL SELECT '0x152b9d0fdc40c096757f570a51e494bd4b943e50' AS contract_address, 'WBTC' AS symbol, 8::INT AS decimals, 22::INT AS price_decimals UNION ALL SELECT '0xa7d7079b0fead91f3e65f86e8915cb59c1a4c664' AS contract_address, 'USDC' AS symbol, 6::INT AS decimals, 24::INT AS price_decimals UNION ALL SELECT '0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e' AS contract_address, 'USDC' AS symbol, 6::INT AS decimals, 24::INT AS price_decimals UNION ALL SELECT '0xc7198437980c041c805a1edcba50c1ce5db95118' AS contract_address, 'USDT' AS symbol, 6::INT AS decimals, 24::INT AS price_decimals UNION ALL SELECT '0xfe6b19286885a4f7f55adad09c3cd1f906d2478f' AS contract_address, 'SOL' AS symbol, 9::INT AS decimals, 21::INT AS price_decimals UNION ALL SELECT '0xd586e7f844cea2f87f50152665bcbc2c279d8d70' AS contract_address, 'DAI' AS symbol, 18::INT AS decimals, 12::INT AS price_decimals ) , v2_swaps AS ( WITH v2_arb_swaps AS ( SELECT block_timestamp , token_price / POWER(10, price_decimals) AS token_price, receiver_fee/POWER(10, decimals) as receiver_fee , pool_fee/POWER(10, decimals) as pool_fee, amount_after_tf/POWER(10, decimals) as token_amount FROM ( SELECT block_timestamp , SUBSTR(data, 27 + 64*27, 40) AS token , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*41, 64)) AS token_price , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*45, 64)) AS receiver_fee , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*49, 64)) AS pool_fee , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*53, 64)) AS amount_after_tf FROM arbitrum.core.fact_event_logs WHERE contract_address = '0xc8ee91a54287db53897056e12d9819156d3822fb' AND topics[0] = '0x137a44067c8961cd7e1d876f4754a5a3a75989b4552f1843fc69c3b372def160' AND topics[1] = '0xf35c99b746450c623be607459294d15f458678f99d535718db6cfcbccb117c09' AND block_timestamp >= current_date - 7 AND block_timestamp < current_date ) AS SWAPS JOIN arb_tokens_v2 ON swaps.token = arb_tokens_v2.contract_address ) , v2_avax_swaps AS ( SELECT block_timestamp , token_price / POWER(10, price_decimals) AS token_price, receiver_fee/POWER(10, decimals) as receiver_fee , pool_fee/POWER(10, decimals) as pool_fee, amount_after_tf/POWER(10, decimals) as token_amount FROM ( SELECT block_timestamp , SUBSTR(data, 27 + 64*27, 40) AS token , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*41, 64)) AS token_price , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*45, 64)) AS receiver_fee , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*49, 64)) AS pool_fee , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*53, 64)) AS amount_after_tf FROM avalanche.core.fact_event_logs WHERE contract_address = '0xdb17b211c34240b014ab6d61d4a31fa0c0e20c26' AND topics[0] = '0x137a44067c8961cd7e1d876f4754a5a3a75989b4552f1843fc69c3b372def160' AND topics[1] = '0xf35c99b746450c623be607459294d15f458678f99d535718db6cfcbccb117c09' AND block_timestamp >= current_date - 7 AND block_timestamp < current_date ) AS SWAPS JOIN avax_tokens_v2 ON swaps.token = avax_tokens_v2.contract_address ) SELECT block_timestamp , token_price * (receiver_fee + pool_fee) as tf, token_price * token_amount as tv , 'arbitrum' AS blockchain FROM v2_arb_swaps UNION ALL SELECT block_timestamp , token_price * (receiver_fee + pool_fee) as tf, token_price * token_amount as tv , 'avalanche' AS blockchain FROM v2_avax_swaps ) , totals AS ( SELECT date_trunc('day', block_timestamp) AS date , blockchain , SUM(tv) AS dtveu , SUM(tf) AS dfu , SUM(CASE WHEN version = 'v1' THEN tf * 0.3 ELSE tf * 0.1 END) AS dru FROM ( SELECT block_timestamp, blockchain, tv, tf, 'v1' AS version FROM arb_perpetual_trades UNION ALL SELECT block_timestamp, blockchain, tv, tf, 'v1' AS version FROM ava_perpetual_trades UNION ALL SELECT block_timestamp, blockchain, tv, tf, 'v1' AS version FROM arb_swap_trades UNION ALL SELECT block_timestamp, blockchain, tv, tf, 'v1' AS version FROM ava_swap_trades UNION ALL SELECT block_timestamp, blockchain, tv, tf, 'v1' AS version FROM arb_glp_trading UNION ALL SELECT block_timestamp, blockchain, tv, tf, 'v1' AS version FROM ava_glp_trading UNION ALL SELECT block_timestamp, blockchain, tv, tf, 'v1' AS version FROM all_liq UNION ALL SELECT block_timestamp, blockchain, tv, tf, 'v2' AS version FROM v2_trades UNION ALL SELECT block_timestamp, blockchain, tv, tf, 'v2' AS version FROM v2_swaps UNION ALL SELECT block_timestamp, blockchain, size AS tv, tf, 'v2' AS version FROM v2_liq ) GROUP BY 1, 2 ) , dates AS ( SELECT (current_date - 7)::TIMESTAMP AS date UNION ALL SELECT DATEADD(DAY, 1, date)::TIMESTAMP FROM dates WHERE date < current_date - 1 ) SELECT a.date AS date , b.blockchain AS blockchain , COALESCE(tx, 0) AS dtra , COALESCE(aa, 0) AS aa , COALESCE(na, 0) AS na , COALESCE(ma, 0) AS ma , COALESCE(dg, 0) AS dg , COALESCE(dtveu, 0) AS dtveu , COALESCE(dfu, 0) AS dfu , COALESCE(dru, 0) AS dru FROM dates a CROSS JOIN blockchains b LEFT JOIN txaab c ON c.date = a.date AND c.blockchain = b.blockchain LEFT JOIN nab d ON d.date = a.date AND d.blockchain = b.blockchain LEFT JOIN mab f ON f.date = a.date AND f.blockchain = b.blockchain LEFT JOIN dgb g ON g.date = a.date AND g.blockchain = b.blockchain LEFT JOIN totals h ON h.date = a.date AND h.blockchain = b.blockchain ORDER BY 1 DESC

Query	Creator	Created
010e avalanche evm	ouznezgazkeklnz39187	May 10, 2023
532o bsc arbitrum ethereum optimism avalanche	ouznezgazkeklnz39187	Aug 2, 2023
All Chains Latency - Top-level Categories copy terra cosmos gnosis flow axelar	ouznezgazkeklnz39187	May 10, 2023
512x arbitrum ethereum avalanche l2 evm	ouznezgazkeklnz39187	May 28, 2023
127o arbitrum base ethereum avalanche polygon	ouznezgazkeklnz39187	May 14, 2023

ouznezgazkeklnz39187