with ethereum_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as price from Ethereum.price.ez_hourly_token_prices group by 1,2,3,4 ) , ETH_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as ETH_price from ethereum.price.ez_hourly_token_prices where symbol = 'WETH' group by 1,2,3,4 ) , base_from_Ethereum as ( select distinct tx_hash from Ethereum.core.fact_token_transfers t where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE and to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , from_Ethereum as ( select block_timestamp::date as date, 'From' as type, 'Ethereum' as chain, p.symbol, raw_amount/pow(10,p.decimals) as amount, amount*price as volume_usd, origin_from_address as user_address, tx_hash from Ethereum.core.fact_token_transfers t join ethereum_price p ON (contract_address = token_address and block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE and tx_hash in (select tx_hash from base_from_Ethereum) qualify row_number() over (partition by tx_hash order by event_index) = 1 ) , from_Ethereum_ETH as ( select block_timestamp::date as date, 'From' as type, 'Ethereum' as chain, p.symbol, amount - 0.001 as real_amount, real_amount*eth_price as volume_usd, origin_from_address as user_address, tx_hash from Ethereum.core.ez_eth_transfers t join eth_price p ON (block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and eth_to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and real_amount != 0 ) , to_Ethereum as ( select block_timestamp::date as date, 'To' as type, 'Ethereum' as chain, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from Ethereum.core.fact_token_transfers t join ethereum_price p ON (contract_address = token_address and block_timestamp::date = price_date) where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and ( from_address in ('0x555ce236c0220695b68341bc48c68d52210cc35b', '0x55f6265a266f7202835b5661c1da045f39f2e587') or origin_to_address = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' ) and to_address != '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' ) , to_Ethereum_ETH as ( select block_timestamp::date as date, 'To' as type, 'Ethereum' as chain, 'ETH' as symbol, amount, amount_usd as volume_usd, ETH_to_address as user_address, tx_hash from Ethereum.core.ez_ETH_transfers t where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and ETH_from_address = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' and ETH_to_address != '0x43de2d77bf8027e25dbd179b491e8d64f38398aa' ) , to_Ethereum_others as ( select block_timestamp::date as date, 'To' as type, 'Ethereum' as chain, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from Ethereum.core.fact_token_transfers t join ethereum_price p ON (contract_address = token_address and block_timestamp::date = price_date) where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE and origin_to_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' and from_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' and tx_hash not in (select tx_hash from to_Ethereum) and tx_hash not in (select tx_hash from to_Ethereum_ETH) qualify row_number() over (partition by tx_hash order by event_index desc) = 1 ) , base_Ethereum as ( -- select * from to_Ethereum -- UNION -- select * from to_Ethereum_ETH -- UNION -- select * from to_Ethereum_others -- UNION select * from from_Ethereum UNION select * from from_Ethereum_ETH ) , final_Ethereum as ( select date, type, chain, -- symbol, zeroifnull(sum(case when type = 'From' then 0.001 * ETH_price end)) as fee_usd, sum(volume_usd) as amount_usd, count(distinct user_address) as user_count, count(distinct tx_hash) as tx_count, sum(amount_usd) over (partition by type order by date) as cumu_usd, sum(tx_count) over (partition by type order by date) as cumu_tx, sum(fee_usd) over (partition by type order by date) as cumu_fee from base_Ethereum join ETH_price on (date = price_date) group by 1,2,3 ) --------------------------------------------------- Polygon , polygon_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as price from polygon.price.ez_hourly_token_prices group by 1,2,3,4 ) , matic_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as matic_price from polygon.price.ez_hourly_token_prices where symbol = 'MATIC' group by 1,2,3,4 ) , base_from_polygon as ( select distinct tx_hash from polygon.core.fact_token_transfers t where 1 = 1 and block_timestamp >= '2022-12-01' and to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , from_polygon as ( select block_timestamp::date as date, 'From' as type, 'Polygon' as chain, p.symbol, raw_amount/pow(10,p.decimals) as amount, amount*price as volume_usd, origin_from_address as user_address, tx_hash from polygon.core.fact_token_transfers t join polygon_price p ON (contract_address = token_address and block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE and tx_hash in (select tx_hash from base_from_polygon) qualify row_number() over (partition by tx_hash order by event_index) = 1 ) , from_polygon_matic as ( select block_timestamp::date as date, 'From' as type, 'Polygon' as chain, p.symbol, amount - 0.05 as real_amount, real_amount*matic_price as volume_usd, origin_from_address as user_address, tx_hash from polygon.core.ez_matic_transfers t join matic_price p ON (block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and matic_to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and real_amount != 0 ) , to_polygon as ( select block_timestamp::date as date, 'To' as type, 'Polygon' as chain, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from polygon.core.fact_token_transfers t join polygon_price p ON (contract_address = token_address and block_timestamp::date = price_date) where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and ( from_address in ('0x555ce236c0220695b68341bc48c68d52210cc35b', '0x55f6265a266f7202835b5661c1da045f39f2e587') or origin_to_address = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' ) and to_address != '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' ) , to_polygon_matic as ( select block_timestamp::date as date, 'To' as type, 'Polygon' as chain, 'MATIC' as symbol, amount, amount_usd as volume_usd, matic_to_address as user_address, tx_hash from polygon.core.ez_matic_transfers t where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and matic_from_address = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' and matic_to_address != '0x43de2d77bf8027e25dbd179b491e8d64f38398aa' ) , to_polygon_others as ( select block_timestamp::date as date, 'To' as type, 'Polygon' as chain, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from polygon.core.fact_token_transfers t join polygon_price p ON (contract_address = token_address and block_timestamp::date = price_date) where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE and origin_to_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' and from_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' and tx_hash not in (select tx_hash from to_polygon) and tx_hash not in (select tx_hash from to_polygon_matic) qualify row_number() over (partition by tx_hash order by event_index desc) = 1 ) , base_polygon as ( -- select * from to_polygon -- UNION -- select * from to_polygon_matic -- UNION -- select * from to_polygon_others -- UNION select * from from_polygon UNION select * from from_polygon_matic ) , final_polygon as ( select date, type, chain, -- symbol, zeroifnull(sum(case when type = 'From' then 0.5 * matic_price end)) as fee_usd, sum(volume_usd) as amount_usd, count(distinct user_address) as user_count, count(distinct tx_hash) as tx_count, sum(amount_usd) over (partition by type order by date) as cumu_usd, sum(tx_count) over (partition by type order by date) as cumu_tx, sum(fee_usd) over (partition by type order by date) as cumu_fee from base_polygon join matic_price on (date = price_date) group by 1,2,3 ) ------------------------------------------------ Solana , base_sol_tx as ( select distinct tx_id, index from solana.core.fact_events where block_timestamp >= '2023-07-01' and block_timestamp < CURRENT_DATE and program_id = 'src5qyZHqTqecJV4aY6Cb6zDZLMDzrDKKezs22MPHr4' ) , base_sol_to as ( select distinct tx_id, index from solana.core.fact_events where block_timestamp >= '2023-07-01' and block_timestamp < CURRENT_DATE and program_id = 'dst5MGcFPoBeREFAA5E3tU5ij8m5uVYwkzkSAbsLbNo' and signers[0] = '7FfB2zQRYUQwpPzkRxAeg2mCBGeCRKp4PCEeULJA9xTo' ) , solana_price as ( select recorded_hour::date as price_date, upper(symbol) as symbol, token_address, avg(close) as price from solana.price.ez_token_prices_hourly group by 1,2,3 ) , sol_price as ( select recorded_hour::date as price_date, upper(symbol) as symbol, token_address, avg(close) as sol_price from solana.price.ez_token_prices_hourly where upper(symbol) = 'SOL' group by 1,2,3 ) , from_solana as ( select block_timestamp::date as date, 'From' as type, 'Solana' as chain, upper(symbol) as symbol, amount, amount*price as volume_usd, tx_from as user_address, t.tx_id from solana.core.fact_transfers t join base_sol_tx b on (t.tx_id = b.tx_id) join solana_price sp on (mint = token_address and date = price_date) where block_timestamp >= '2023-07-01' and block_timestamp < CURRENT_DATE and amount != 0.01735944 qualify row_number() over (partition by t.tx_id order by t.index) = 1 ) , to_solana as ( select block_timestamp::date as date, 'To' as type, 'Solana' as chain, upper(symbol) as symbol, amount, amount*price as volume_usd, tx_to as user_address, t.tx_id from solana.core.fact_transfers t join base_sol_to b on (t.tx_id = b.tx_id) join solana_price sp on (mint = token_address and date = price_date) where block_timestamp >= '2023-07-01' and block_timestamp < CURRENT_DATE and tx_from = '7FfB2zQRYUQwpPzkRxAeg2mCBGeCRKp4PCEeULJA9xTo' qualify row_number() over (partition by t.tx_id order by t.index desc) = 1 ) , base_solana as ( select * from from_solana -- UNION -- select * from to_solana ) , final_solana as ( select date, type, chain, -- symbol, zeroifnull(sum(case when type = 'From' then 0.015 * sol_price end)) as fee_usd, sum(volume_usd) as amount_usd, count(distinct user_address) as user_count, count(distinct tx_id) as tx_count, sum(amount_usd) over (partition by type order by date) as cumu_usd, sum(tx_count) over (partition by type order by date) as cumu_tx, sum(fee_usd) over (partition by type order by date) as cumu_fee from base_solana join sol_price on (date = price_date) group by 1,2,3 ) -------------------------------------------------- BSC , bsc_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as price from BSC.price.ez_hourly_token_prices group by 1,2,3,4 ) , BNB_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as BNB_price from BSC.price.ez_hourly_token_prices where symbol = 'WBNB' group by 1,2,3,4 ) , base_from_BSC as ( select distinct tx_hash from BSC.core.fact_token_transfers t where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE and to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , from_BSC as ( select block_timestamp::date as date, 'From' as type, 'BSC' as chain, p.symbol, raw_amount/pow(10,p.decimals) as amount, amount*price as volume_usd, origin_from_address as user_address, tx_hash from BSC.core.fact_token_transfers t join bsc_price p ON (contract_address = token_address and block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE and tx_hash in (select tx_hash from base_from_BSC) qualify row_number() over (partition by tx_hash order by event_index) = 1 ) , from_BSC_BNB as ( select block_timestamp::date as date, 'From' as type, 'BSC' as chain, p.symbol, amount - 0.001 as real_amount, real_amount*BNB_price as volume_usd, origin_from_address as user_address, tx_hash from BSC.core.ez_BNB_transfers t join BNB_price p ON (block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and BNB_to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and real_amount != 0 ) , to_BSC as ( select block_timestamp::date as date, 'To' as type, 'BSC' as chain, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from BSC.core.fact_token_transfers t join bsc_price p ON (contract_address = token_address and block_timestamp::date = price_date) where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and ( from_address in ('0x555ce236c0220695b68341bc48c68d52210cc35b', '0x55f6265a266f7202835b5661c1da045f39f2e587') or origin_to_address = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' ) and to_address != '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' ) , to_BSC_BNB as ( select block_timestamp::date as date, 'To' as type, 'BSC' as chain, 'BNB' as symbol, amount, amount_usd as volume_usd, BNB_to_address as user_address, tx_hash from BSC.core.ez_BNB_transfers t where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and BNB_from_address = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' and BNB_to_address != '0x43de2d77bf8027e25dbd179b491e8d64f38398aa' ) , to_BSC_others as ( select block_timestamp::date as date, 'To' as type, 'BSC' as chain, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from BSC.core.fact_token_transfers t join bsc_price p ON (contract_address = token_address and block_timestamp::date = price_date) where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE and origin_to_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' and from_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' and tx_hash not in (select tx_hash from to_BSC) and tx_hash not in (select tx_hash from to_BSC_BNB) qualify row_number() over (partition by tx_hash order by event_index desc) = 1 ) , base_BSC as ( -- select * from to_BSC -- UNION -- select * from to_BSC_BNB -- UNION -- select * from to_BSC_others -- UNION select * from from_BSC UNION select * from from_BSC_BNB ) , final_BSC as ( select date, type, chain, -- symbol, zeroifnull(sum(case when type = 'From' then 0.005 * BNB_price end)) as fee_usd, sum(volume_usd) as amount_usd, count(distinct user_address) as user_count, count(distinct tx_hash) as tx_count, sum(amount_usd) over (partition by type order by date) as cumu_usd, sum(tx_count) over (partition by type order by date) as cumu_tx, sum(fee_usd) over (partition by type order by date) as cumu_fee from base_BSC join BNB_price on (date = price_date) group by 1,2,3 ) ---------------------------------------- Avalanche , avalanche_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as price from avalanche.price.ez_hourly_token_prices group by 1,2,3,4 ) , avax_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as avax_price from avalanche.price.ez_hourly_token_prices where symbol = 'WAVAX' group by 1,2,3,4 ) , base_from_avalanche as ( select distinct tx_hash from avalanche.core.fact_token_transfers t where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE and to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , from_avalanche as ( select block_timestamp::date as date, 'From' as type, 'Avalanche' as chain, p.symbol, raw_amount/pow(10,p.decimals) as amount, amount*price as volume_usd, origin_from_address as user_address, tx_hash from avalanche.core.fact_token_transfers t join avalanche_price p ON (contract_address = token_address and block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE and tx_hash in (select tx_hash from base_from_avalanche) qualify row_number() over (partition by tx_hash order by event_index) = 1 ) , from_avalanche_avax as ( select block_timestamp::date as date, 'From' as type, 'Avalanche' as chain, p.symbol, amount - 0.05 as real_amount, real_amount*avax_price as volume_usd, origin_from_address as user_address, tx_hash from avalanche.core.ez_avax_transfers t join avax_price p ON (block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and avax_to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and real_amount != 0 ) , to_avalanche as ( select block_timestamp::date as date, 'To' as type, 'Avalanche' as chain, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from avalanche.core.fact_token_transfers t join avalanche_price p ON (contract_address = token_address and block_timestamp::date = price_date) where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and ( from_address in ('0x555ce236c0220695b68341bc48c68d52210cc35b', '0x55f6265a266f7202835b5661c1da045f39f2e587') or origin_to_address = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' ) and to_address != '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' ) , to_avalanche_avax as ( select block_timestamp::date as date, 'To' as type, 'Avalanche' as chain, 'WAVAX' as symbol, amount, amount_usd as volume_usd, avax_to_address as user_address, tx_hash from avalanche.core.ez_avax_transfers t where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and avax_from_address = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' and avax_to_address != '0x43de2d77bf8027e25dbd179b491e8d64f38398aa' ) , to_avalanche_others as ( select block_timestamp::date as date, 'To' as type, 'Avalanche' as chain, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from avalanche.core.fact_token_transfers t join avalanche_price p ON (contract_address = token_address and block_timestamp::date = price_date) where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE and origin_to_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' and from_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' and tx_hash not in (select tx_hash from to_avalanche) and tx_hash not in (select tx_hash from to_avalanche_avax) qualify row_number() over (partition by tx_hash order by event_index desc) = 1 ) , base_avalanche as ( -- select * from to_avalanche -- UNION -- select * from to_avalanche_avax -- UNION -- select * from to_avalanche_others -- UNION select * from from_avalanche UNION select * from from_avalanche_avax ) , final_avalanche as ( select date, type, chain, -- symbol, zeroifnull(sum(case when type = 'From' then 0.05 * avax_price end)) as fee_usd, sum(volume_usd) as amount_usd, count(distinct user_address) as user_count, count(distinct tx_hash) as tx_count, sum(amount_usd) over (partition by type order by date) as cumu_usd, sum(tx_count) over (partition by type order by date) as cumu_tx, sum(fee_usd) over (partition by type order by date) as cumu_fee from base_avalanche join avax_price on (date = price_date) group by 1,2,3 ) -------------------------------------------- Optimism , optimism_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as price from Optimism.price.ez_hourly_token_prices group by 1,2,3,4 ) , base_from_optimism as ( select distinct tx_hash from Optimism.core.fact_token_transfers t where 1 = 1 and block_timestamp >= '2023-08-01' and block_timestamp < CURRENT_DATE and to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , from_Optimism as ( select block_timestamp::date as date, 'From' as type, 'Optimism' as chain, p.symbol, raw_amount/pow(10,p.decimals) as amount, amount*price as volume_usd, origin_from_address as user_address, tx_hash from Optimism.core.fact_token_transfers t join optimism_price p ON (contract_address = token_address and block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= '2023-08-01' and block_timestamp < CURRENT_DATE and tx_hash in (select tx_hash from base_from_optimism) qualify row_number() over (partition by tx_hash order by event_index) = 1 ) , from_Optimism_ETH as ( select block_timestamp::date as date, 'From' as type, 'Optimism' as chain, p.symbol, amount - 0.001 as real_amount, real_amount*eth_price as volume_usd, origin_from_address as user_address, tx_hash from Optimism.core.ez_eth_transfers t join eth_price p ON (block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= '2023-08-01' and block_timestamp < CURRENT_DATE and eth_to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and real_amount != 0 ) , to_Optimism as ( select block_timestamp::date as date, 'To' as type, 'Optimism' as chain, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from Optimism.core.fact_token_transfers t join optimism_price p ON (contract_address = token_address and block_timestamp::date = price_date) where 1 = 1 and block_timestamp >= '2023-08-01' and block_timestamp < CURRENT_DATE and ( from_address in ('0x555ce236c0220695b68341bc48c68d52210cc35b', '0x55f6265a266f7202835b5661c1da045f39f2e587') or origin_to_address = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' ) and to_address != '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' ) , to_Optimism_ETH as ( select block_timestamp::date as date, 'To' as type, 'Optimism' as chain, 'ETH' as symbol, amount, amount_usd as volume_usd, ETH_to_address as user_address, tx_hash from Optimism.core.ez_ETH_transfers t where 1 = 1 and block_timestamp >= '2023-08-01' and block_timestamp < CURRENT_DATE and ETH_from_address = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' and ETH_to_address != '0x43de2d77bf8027e25dbd179b491e8d64f38398aa' ) , to_Optimism_others as ( select block_timestamp::date as date, 'To' as type, 'Optimism' as chain, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from Optimism.core.fact_token_transfers t join optimism_price p ON (contract_address = token_address and block_timestamp::date = price_date) where 1 = 1 and block_timestamp >= '2023-08-01' and block_timestamp < CURRENT_DATE and origin_to_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' and from_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' and tx_hash not in (select tx_hash from to_Optimism) and tx_hash not in (select tx_hash from to_Optimism_ETH) qualify row_number() over (partition by tx_hash order by event_index desc) = 1 ) , base_Optimism as ( -- select * from to_Optimism -- UNION -- select * from to_Optimism_ETH -- UNION -- select * from to_Optimism_others -- UNION select * from from_Optimism UNION select * from from_Optimism_ETH ) , final_Optimism as ( select date, type, chain, -- symbol, zeroifnull(sum(case when type = 'From' then 0.001 * ETH_price end)) as fee_usd, sum(volume_usd) as amount_usd, count(distinct user_address) as user_count, count(distinct tx_hash) as tx_count, sum(amount_usd) over (partition by type order by date) as cumu_usd, sum(tx_count) over (partition by type order by date) as cumu_tx, sum(fee_usd) over (partition by type order by date) as cumu_fee from base_Optimism join ETH_price on (date = price_date) group by 1,2,3 ) ------------------------------------------------------- Arbitrum , arbitrum_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as price from arbitrum.price.ez_hourly_token_prices group by 1,2,3,4 ) , base_from_arbitrum as ( select distinct tx_hash from arbitrum.core.fact_token_transfers t where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE and to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , from_arbitrum as ( select block_timestamp::date as date, 'From' as type, 'Arbitrum' as chain, p.symbol, raw_amount/pow(10,p.decimals) as amount, amount*price as volume_usd, origin_from_address as user_address, tx_hash from arbitrum.core.fact_token_transfers t join arbitrum_price p ON (contract_address = token_address and block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE and tx_hash in (select tx_hash from base_from_arbitrum) qualify row_number() over (partition by tx_hash order by event_index) = 1 ) , from_arbitrum_ETH as ( select block_timestamp::date as date, 'From' as type, 'Arbitrum' as chain, p.symbol, amount - 0.001 as real_amount, real_amount*ETH_price as volume_usd, origin_from_address as user_address, tx_hash from arbitrum.core.ez_ETH_transfers t join ETH_price p ON (block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and ETH_to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and real_amount != 0 ) , to_arbitrum as ( select block_timestamp::date as date, 'To' as type, 'Arbitrum' as chain, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from arbitrum.core.fact_token_transfers t join arbitrum_price p ON (contract_address = token_address and block_timestamp::date = price_date) where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and ( from_address in ('0x555ce236c0220695b68341bc48c68d52210cc35b', '0x55f6265a266f7202835b5661c1da045f39f2e587') or origin_to_address = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' ) and to_address != '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' ) , to_arbitrum_ETH as ( select block_timestamp::date as date, 'To' as type, 'Arbitrum' as chain, 'ETH' as symbol, amount, amount_usd as volume_usd, ETH_to_address as user_address, tx_hash from arbitrum.core.ez_ETH_transfers t where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and ETH_from_address = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' and ETH_to_address != '0x43de2d77bf8027e25dbd179b491e8d64f38398aa' ) , to_arbitrum_others as ( select block_timestamp::date as date, 'To' as type, 'Arbitrum' as chain, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from arbitrum.core.fact_token_transfers t join arbitrum_price p ON (contract_address = token_address and block_timestamp::date = price_date) where 1 = 1 and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE and origin_to_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' and from_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' and tx_hash not in (select tx_hash from to_arbitrum) and tx_hash not in (select tx_hash from to_arbitrum_ETH) qualify row_number() over (partition by tx_hash order by event_index desc) = 1 ) , base_arbitrum as ( -- select * from to_arbitrum -- UNION -- select * from to_arbitrum_ETH -- UNION -- select * from to_arbitrum_others -- UNION select * from from_arbitrum UNION select * from from_arbitrum_ETH ) , final_arbitrum as ( select date, type, chain, -- symbol, zeroifnull(sum(case when type = 'From' then 0.001 * ETH_price end)) as fee_usd, sum(volume_usd) as amount_usd, count(distinct user_address) as user_count, count(distinct tx_hash) as tx_count, sum(amount_usd) over (partition by type order by date) as cumu_usd, sum(tx_count) over (partition by type order by date) as cumu_tx, sum(fee_usd) over (partition by type order by date) as cumu_fee from base_arbitrum join ETH_price on (date = price_date) group by 1,2,3 ) ---------------------------------------------------------------- Base , base_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as price from Base.price.ez_hourly_token_prices group by 1,2,3,4 ) , Base_from_Base as ( select distinct tx_hash from Base.core.fact_token_transfers t where 1 = 1 and block_timestamp >= '2022-12-01' and block_timestamp < CURRENT_DATE and to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , from_Base as ( select block_timestamp::date as date, 'From' as type, 'Base' as chain, p.symbol, raw_amount/pow(10,p.decimals) as amount, amount*price as volume_usd, origin_from_address as user_address, tx_hash from Base.core.fact_token_transfers t join base_price p ON (contract_address = token_address and block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= '2022-12-01' and block_timestamp < CURRENT_DATE and tx_hash in (select tx_hash from Base_from_Base) qualify row_number() over (partition by tx_hash order by event_index) = 1 ) , from_Base_ETH as ( select block_timestamp::date as date, 'From' as type, 'Base' as chain, p.symbol, amount - 0.001 as real_amount, real_amount*ETH_price as volume_usd, origin_from_address as user_address, tx_hash from Base.core.ez_ETH_transfers t join ETH_price p ON (block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= '2022-12-01' and block_timestamp < CURRENT_DATE and tx_hash != '0x8a1cfba8abbba2780f51287ec7ef4db3b0b832047a5db07efcba672a246d2656' and ETH_to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and real_amount != 0 ) , to_Base as ( select block_timestamp::date as date, 'To' as type, 'Base' as chain, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from Base.core.fact_token_transfers t join base_price p ON (contract_address = token_address and block_timestamp::date = price_date) where 1 = 1 and block_timestamp >= '2022-12-01' and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and ( from_address in ('0x555ce236c0220695b68341bc48c68d52210cc35b', '0x55f6265a266f7202835b5661c1da045f39f2e587') or origin_to_address = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' ) and to_address != '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' ) , to_Base_ETH as ( select block_timestamp::date as date, 'To' as type, 'Base' as chain, 'ETH' as symbol, amount, amount_usd as volume_usd, ETH_to_address as user_address, tx_hash from Base.core.ez_ETH_transfers t where 1 = 1 and block_timestamp >= '2022-12-01' and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and ETH_from_address = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' and ETH_to_address != '0x43de2d77bf8027e25dbd179b491e8d64f38398aa' ) , to_Base_others as ( select block_timestamp::date as date, 'To' as type, 'Base' as chain, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from Base.core.fact_token_transfers t join base_price p ON (contract_address = token_address and block_timestamp::date = price_date) where 1 = 1 and block_timestamp >= '2022-12-01' and block_timestamp < CURRENT_DATE and origin_to_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' and from_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' and tx_hash not in (select tx_hash from to_Base) and tx_hash not in (select tx_hash from to_Base_ETH) qualify row_number() over (partition by tx_hash order by event_index desc) = 1 ) , Base_Base as ( -- select * from to_Base -- UNION -- select * from to_Base_ETH -- UNION -- select * from to_Base_others -- UNION select * from from_Base UNION select * from from_Base_ETH ) , final_Base as ( select date, type, chain, -- symbol, zeroifnull(sum(case when type = 'From' then 0.001 * ETH_price end)) as fee_usd, sum(volume_usd) as amount_usd, count(distinct user_address) as user_count, count(distinct tx_hash) as tx_count, sum(amount_usd) over (partition by type order by date) as cumu_usd, sum(tx_count) over (partition by type order by date) as cumu_tx, sum(fee_usd) over (partition by type order by date) as cumu_fee from Base_Base join ETH_price on (date = price_date) group by 1,2,3 ) , final_all as ( select * from final_ethereum UNION select * from final_polygon UNION select * from final_solana UNION select * from final_bsc UNION select * from final_avalanche UNION select * from final_optimism UNION select * from final_arbitrum UNION select * from final_Base ) select * from final_all

0x9Cb16F99eB162bF6F970791Ba90bbf30C1CD1929