'Creation' as tx_type, as d0b03cc-86c9-45d3-873e-d50c0cd091cf with ethereum_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as price from Ethereum.price.ez_prices_hourly 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_prices_hourly 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 >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE and to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , ethereum_cancelled_tx as ( select tx_hash FROM ethereum.core.fact_event_logs where 1 = 1 and block_timestamp > CURRENT_DATE - 90 and topics[0] = '0x7d7d1c5b3eadbe275ceb358e65cd57410b35997187258dbaaae42ab6e1405fd8' ) , from_Ethereum as ( select block_timestamp::date as date, 'From' as route_type, 'Ethereum' as chain, 'Creation' as tx_type, 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 >= CURRENT_DATE - 90 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 route_type, 'Ethereum' as chain, 'Creation' as tx_type, 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_native_TRANSFERS t join eth_price p ON (block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and TO_ADDRESS = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and real_amount != 0 ) , to_Ethereum as ( select block_timestamp::date as date, 'To' as route_type, 'Ethereum' as chain, 'Creation' as tx_type, 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 >= CURRENT_DATE - 90 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 route_type, 'Ethereum' as chain, 'Creation' as tx_type, 'ETH' as symbol, amount, amount_usd as volume_usd, TO_ADDRESS as user_address, tx_hash from Ethereum.core.EZ_native_TRANSFERS t where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and FROM_ADDRESS = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' and TO_ADDRESS != '0x43de2d77bf8027e25dbd179b491e8d64f38398aa' ) , to_Ethereum_others as ( select block_timestamp::date as date, 'To' as route_type, 'Ethereum' as chain, 'Creation' as tx_type, 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 >= CURRENT_DATE - 90 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 ) , cancel_ethereum as ( select block_timestamp::date as date, 'From' as route_type, 'Ethereum' as chain, 'Cancel' as tx_type, 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 join ethereum_price p ON (contract_address = token_address and block_timestamp::date = price_date) where block_timestamp > CURRENT_DATE - 90 and tx_hash in (select tx_hash from ethereum_cancelled_tx) and from_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , cancel_ethereum_eth as ( select block_timestamp::date as date, 'From' as route_type, 'Ethereum' as chain, 'Cancel' as tx_type, p.symbol, amount, amount*eth_price as volume_usd, TO_ADDRESS as user_address, tx_hash from ethereum.core.EZ_native_TRANSFERS join eth_price p ON (block_timestamp::date = p.price_date) where block_timestamp > CURRENT_DATE - 90 and tx_hash in (select tx_hash from ethereum_cancelled_tx) and FROM_ADDRESS = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and amount != 0.001 ) , 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 -- UNION -- select * from cancel_ethereum -- UNION -- select * from cancel_ethereum_eth ) , final_Ethereum as ( select date, chain, zeroifnull(sum(case when (route_type = 'From' and tx_type != 'Cancel') then 0.001 * ETH_price when (route_type = 'From' and tx_type = 'Cancel') then -0.001 * ETH_price end)) as fee_usd, sum(case when tx_type != 'Cancel' then volume_usd else -volume_usd end) as amount_usd, case when fee_usd != 0 then amount_usd *0.04 * 0.01 else 0 end as variable_fee, fee_usd + variable_fee as total_fee, count(distinct user_address) as user_count, count(distinct case when tx_type = 'Cancel' then tx_hash end) as cancel_txs, count(distinct tx_hash) as all_txs, all_txs - cancel_txs*2 as tx_count, -- deducted creation txs as well sum(amount_usd) over (partition by chain order by date) as cumu_usd, sum(tx_count) over (partition by chain order by date) as cumu_tx, sum(total_fee) over (partition by chain order by date) as cumu_fee from base_Ethereum join ETH_price on (date = price_date) group by 1,2 ) --------------------------------------------------- Polygon , polygon_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as price from polygon.price.ez_prices_hourly 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_prices_hourly 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 >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE and to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , polygon_cancelled_tx as ( select tx_hash FROM polygon.core.fact_event_logs where 1 = 1 and block_timestamp > CURRENT_DATE - 90 and topics[0] = '0x7d7d1c5b3eadbe275ceb358e65cd57410b35997187258dbaaae42ab6e1405fd8' ) , from_polygon as ( select block_timestamp::date as date, 'From' as route_type, 'polygon' as chain, 'Creation' as tx_type, 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 >= CURRENT_DATE - 90 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 route_type, 'polygon' as chain, 'Creation' as tx_type, p.symbol, amount - 0.5 as real_amount, real_amount*MATIC_price as volume_usd, origin_from_address as user_address, tx_hash from polygon.core.EZ_native_TRANSFERS t join MATIC_price p ON (block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and TO_ADDRESS = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and real_amount != 0 ) , to_polygon as ( select block_timestamp::date as date, 'To' as route_type, 'polygon' as chain, 'Creation' as tx_type, 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 >= CURRENT_DATE - 90 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 route_type, 'polygon' as chain, 'Creation' as tx_type, 'MATIC' as symbol, amount, amount_usd as volume_usd, TO_ADDRESS as user_address, tx_hash from polygon.core.EZ_native_TRANSFERS t where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and FROM_ADDRESS = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' and TO_ADDRESS != '0x43de2d77bf8027e25dbd179b491e8d64f38398aa' ) , to_polygon_others as ( select block_timestamp::date as date, 'To' as route_type, 'polygon' as chain, 'Creation' as tx_type, 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 >= CURRENT_DATE - 90 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 ) , cancel_polygon as ( select block_timestamp::date as date, 'From' as route_type, 'polygon' as chain, 'Cancel' as tx_type, 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 join polygon_price p ON (contract_address = token_address and block_timestamp::date = price_date) where block_timestamp > CURRENT_DATE - 90 and tx_hash in (select tx_hash from polygon_cancelled_tx) and from_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , cancel_polygon_MATIC as ( select block_timestamp::date as date, 'From' as route_type, 'polygon' as chain, 'Cancel' as tx_type, p.symbol, amount, amount*MATIC_price as volume_usd, TO_ADDRESS as user_address, tx_hash from polygon.core.EZ_native_TRANSFERS join MATIC_price p ON (block_timestamp::date = p.price_date) where block_timestamp > CURRENT_DATE - 90 and tx_hash in (select tx_hash from polygon_cancelled_tx) and FROM_ADDRESS = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and amount != 0.5 ) , 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 -- UNION -- select * from cancel_polygon -- UNION -- select * from cancel_polygon_MATIC ) , final_polygon as ( select date, chain, zeroifnull(sum(case when (route_type = 'From' and tx_type != 'Cancel') then 0.5 * MATIC_price when (route_type = 'From' and tx_type = 'Cancel') then -0.5 * MATIC_price end)) as fee_usd, sum(case when tx_type != 'Cancel' then volume_usd else -volume_usd end) as amount_usd, case when fee_usd != 0 then amount_usd *0.04 * 0.01 else 0 end as variable_fee, fee_usd + variable_fee as total_fee, count(distinct user_address) as user_count, count(distinct case when tx_type = 'Cancel' then tx_hash end) as cancel_txs, count(distinct tx_hash) as all_txs, all_txs - cancel_txs*2 as tx_count, -- deducted creation txs as well sum(amount_usd) over (partition by chain order by date) as cumu_usd, sum(tx_count) over (partition by chain order by date) as cumu_tx, sum(total_fee) over (partition by chain order by date) as cumu_fee from base_polygon join MATIC_price on (date = price_date) -- where route_type = 'From' group by 1,2 ) ------------------------------------------------ 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 hour::date as price_date, upper(symbol) as symbol, token_address, avg(price) as price from solana.price.ez_prices_hourly where upper(symbol) not in ('BOO', 'SFI', 'COPE', 'PEPE', 'KITTY', 'ICE', 'SCS', 'BULL', 'MEME', 'WHEY', 'BSAMO', 'GXE', 'KS', 'COW', 'XENO', 'SMRT', 'GEAR', 'ZEE', 'BIRD', 'INU', 'VRS', 'AVAX', 'MMAPS', 'STAR', 'PIPI', 'TRUMP', 'ROSE', 'JESUS', 'GARY', 'JEET', '$HEGE', 'JUNGLE', 'TRUMPCAT', 'SOLCAT', 'ETHEREUM', 'BOZO', 'HARAMBE', 'SBF', 'MEDIA', 'TPX', 'GME', 'NOS', '$WEN', 'MAGA', 'MONKE') -- Causing ERROR group by 1,2,3 ) , sol_price as ( select hour::date as price_date, upper(symbol) as symbol, token_address, avg(price) as sol_price from solana.price.ez_prices_hourly where token_address = 'So11111111111111111111111111111111111111112' group by 1,2,3 ) , from_solana as ( select block_timestamp::date as date, 'From' as type, 'Solana' as chain, 'Creation' as tx_type, 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 and volume_usd < 500000 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, 'Creation' as tx_type, 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' and volume_usd < 500000 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, amount_usd *0.04 * 0.01 as variable_fee, fee_usd + variable_fee as total_fee, count(distinct user_address) as user_count, 0 as cancel_txs, count(distinct tx_id) as all_txs, all_txs - cancel_txs*2 as tx_count, -- deducted creation txs as well sum(amount_usd) over (partition by chain order by date) as cumu_usd, sum(tx_count) over (partition by chain order by date) as cumu_tx, sum(total_fee) over (partition by chain order by date) as cumu_fee from base_solana join sol_price on (date = price_date) group by 1,2 ) -------------------------------------------------- BSC , bsc_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as price from BSC.price.ez_prices_hourly 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_prices_hourly 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 >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE and to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , bsc_cancelled_tx as ( select tx_hash FROM BSC.core.fact_event_logs where 1 = 1 and block_timestamp > CURRENT_DATE - 90 and topics[0] = '0x7d7d1c5b3eadbe275ceb358e65cd57410b35997187258dbaaae42ab6e1405fd8' ) , from_BSC as ( select block_timestamp::date as date, 'From' as route_type, 'BSC' as chain, 'Creation' as tx_type, 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 >= CURRENT_DATE - 90 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 route_type, 'BSC' as chain, 'Creation' as tx_type, p.symbol, amount - 0.005 as real_amount, real_amount*BNB_price as volume_usd, origin_from_address as user_address, tx_hash from BSC.core.EZ_native_TRANSFERS t join BNB_price p ON (block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and TO_ADDRESS = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and real_amount != 0 ) , to_BSC as ( select block_timestamp::date as date, 'To' as route_type, 'BSC' as chain, 'Creation' as tx_type, 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 >= CURRENT_DATE - 90 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 route_type, 'BSC' as chain, 'Creation' as tx_type, 'BNB' as symbol, amount, amount_usd as volume_usd, TO_ADDRESS as user_address, tx_hash from BSC.core.EZ_native_TRANSFERS t where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and FROM_ADDRESS = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' and TO_ADDRESS != '0x43de2d77bf8027e25dbd179b491e8d64f38398aa' ) , to_BSC_others as ( select block_timestamp::date as date, 'To' as route_type, 'BSC' as chain, 'Creation' as tx_type, 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 >= CURRENT_DATE - 90 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 ) , cancel_BSC as ( select block_timestamp::date as date, 'From' as route_type, 'BSC' as chain, 'Cancel' as tx_type, 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 join bsc_price p ON (contract_address = token_address and block_timestamp::date = price_date) where block_timestamp > CURRENT_DATE - 90 and tx_hash in (select tx_hash from bsc_cancelled_tx) and from_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , cancel_BSC_BNB as ( select block_timestamp::date as date, 'From' as route_type, 'BSC' as chain, 'Cancel' as tx_type, p.symbol, amount, amount*BNB_price as volume_usd, TO_ADDRESS as user_address, tx_hash from BSC.core.EZ_native_TRANSFERS join BNB_price p ON (block_timestamp::date = p.price_date) where block_timestamp > CURRENT_DATE - 90 and tx_hash in (select tx_hash from bsc_cancelled_tx) and FROM_ADDRESS = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and amount != 0.005 ) , 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 -- UNION -- select * from cancel_BSC -- UNION -- select * from cancel_BSC_BNB ) , final_BSC as ( select date, chain, zeroifnull(sum(case when (route_type = 'From' and tx_type != 'Cancel') then 0.005 * BNB_price when (route_type = 'From' and tx_type = 'Cancel') then -0.005 * BNB_price end)) as fee_usd, sum(case when tx_type != 'Cancel' then volume_usd else -volume_usd end) as amount_usd, case when fee_usd != 0 then amount_usd *0.04 * 0.01 else 0 end as variable_fee, fee_usd + variable_fee as total_fee, count(distinct user_address) as user_count, count(distinct case when tx_type = 'Cancel' then tx_hash end) as cancel_txs, count(distinct tx_hash) as all_txs, all_txs - cancel_txs*2 as tx_count, -- deducted creation txs as well sum(amount_usd) over (partition by chain order by date) as cumu_usd, sum(tx_count) over (partition by chain order by date) as cumu_tx, sum(total_fee) over (partition by chain order by date) as cumu_fee from base_BSC join BNB_price on (date = price_date) -- where route_type = 'From' group by 1,2 ) ---------------------------------------- Avalanche , avalanche_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as price from Avalanche.price.ez_prices_hourly 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_prices_hourly 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 >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE and to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , avalanche_cancelled_tx as ( select tx_hash FROM Avalanche.core.fact_event_logs where 1 = 1 and block_timestamp > CURRENT_DATE - 90 and topics[0] = '0x7d7d1c5b3eadbe275ceb358e65cd57410b35997187258dbaaae42ab6e1405fd8' ) , from_Avalanche as ( select block_timestamp::date as date, 'From' as route_type, 'Avalanche' as chain, 'Creation' as tx_type, 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 >= CURRENT_DATE - 90 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 route_type, 'Avalanche' as chain, 'Creation' as tx_type, p.symbol, amount - (case when date >= '2023-10-16' then 0.05 else 0.01 end) as real_amount, real_amount*AVAX_price as volume_usd, origin_from_address as user_address, tx_hash from Avalanche.core.EZ_native_TRANSFERS t join AVAX_price p ON (block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and TO_ADDRESS = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and real_amount != 0 ) , to_Avalanche as ( select block_timestamp::date as date, 'To' as route_type, 'Avalanche' as chain, 'Creation' as tx_type, 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 >= CURRENT_DATE - 90 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 route_type, 'Avalanche' as chain, 'Creation' as tx_type, 'AVAX' as symbol, amount, amount_usd as volume_usd, TO_ADDRESS as user_address, tx_hash from Avalanche.core.EZ_native_TRANSFERS t where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and FROM_ADDRESS = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' and TO_ADDRESS != '0x43de2d77bf8027e25dbd179b491e8d64f38398aa' ) , to_Avalanche_others as ( select block_timestamp::date as date, 'To' as route_type, 'Avalanche' as chain, 'Creation' as tx_type, 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 >= CURRENT_DATE - 90 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 ) , cancel_Avalanche as ( select block_timestamp::date as date, 'From' as route_type, 'Avalanche' as chain, 'Cancel' as tx_type, 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 join avalanche_price p ON (contract_address = token_address and block_timestamp::date = price_date) where block_timestamp > CURRENT_DATE - 90 and tx_hash in (select tx_hash from avalanche_cancelled_tx) and from_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , cancel_Avalanche_AVAX as ( select block_timestamp::date as date, 'From' as route_type, 'Avalanche' as chain, 'Cancel' as tx_type, p.symbol, amount, amount*AVAX_price as volume_usd, TO_ADDRESS as user_address, tx_hash from Avalanche.core.EZ_native_TRANSFERS join AVAX_price p ON (block_timestamp::date = p.price_date) where block_timestamp > CURRENT_DATE - 90 and tx_hash in (select tx_hash from avalanche_cancelled_tx) and FROM_ADDRESS = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and amount != (case when date >= '2023-10-16' then 0.05 else 0.01 end) ) , 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 -- UNION -- select * from cancel_Avalanche -- UNION -- select * from cancel_Avalanche_AVAX ) , final_Avalanche as ( select date, chain, zeroifnull(sum(case when (route_type = 'From' and tx_type != 'Cancel') then (case when date >= '2023-10-16' then 0.05 else 0.01 end) * AVAX_price when (route_type = 'From' and tx_type = 'Cancel') then (case when date >= '2023-10-16' then -0.05 else -0.01 end) * AVAX_price end)) as fee_usd, sum(case when tx_type != 'Cancel' then volume_usd else -volume_usd end) as amount_usd, case when fee_usd != 0 then amount_usd *0.04 * 0.01 else 0 end as variable_fee, fee_usd + variable_fee as total_fee, count(distinct user_address) as user_count, count(distinct case when tx_type = 'Cancel' then tx_hash end) as cancel_txs, count(distinct tx_hash) as all_txs, all_txs - cancel_txs*2 as tx_count, -- deducted creation txs as well sum(amount_usd) over (partition by chain order by date) as cumu_usd, sum(tx_count) over (partition by chain order by date) as cumu_tx, sum(total_fee) over (partition by chain order by date) as cumu_fee from base_Avalanche join AVAX_price on (date = price_date) -- where route_type = 'From' group by 1,2 ) -------------------------------------------- Optimism , Optimism_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as price from Optimism.price.ez_prices_hourly group by 1,2,3,4 ) , Optimism_cancelled_tx as ( select tx_hash FROM Optimism.core.fact_event_logs where 1 = 1 and block_timestamp > CURRENT_DATE - 90 and topics[0] = '0x7d7d1c5b3eadbe275ceb358e65cd57410b35997187258dbaaae42ab6e1405fd8' ) , 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 route_type, 'Optimism' as chain, 'Creation' as tx_type, 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 route_type, 'Optimism' as chain, 'Creation' as tx_type, 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_native_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 TO_ADDRESS = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and real_amount != 0 ) , to_Optimism as ( select block_timestamp::date as date, 'To' as route_type, 'Optimism' as chain, 'Creation' as tx_type, 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 route_type, 'Optimism' as chain, 'Creation' as tx_type, 'ETH' as symbol, amount, amount_usd as volume_usd, TO_ADDRESS as user_address, tx_hash from Optimism.core.EZ_native_TRANSFERS t where 1 = 1 and block_timestamp >= '2023-08-01' and block_timestamp < CURRENT_DATE and FROM_ADDRESS = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' and TO_ADDRESS != '0x43de2d77bf8027e25dbd179b491e8d64f38398aa' ) , to_Optimism_others as ( select block_timestamp::date as date, 'To' as route_type, 'Optimism' as chain, 'Creation' as tx_type, 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 ) , cancel_Optimism as ( select block_timestamp::date as date, 'From' as route_type, 'Optimism' as chain, 'Cancel' as tx_type, 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 join Optimism_price p ON (contract_address = token_address and block_timestamp::date = price_date) where block_timestamp > CURRENT_DATE - 90 and tx_hash in (select tx_hash from Optimism_cancelled_tx) and from_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , cancel_Optimism_ETH as ( select block_timestamp::date as date, 'From' as route_type, 'Optimism' as chain, 'Cancel' as tx_type, p.symbol, amount, amount*ETH_price as volume_usd, TO_ADDRESS as user_address, tx_hash from Optimism.core.EZ_native_TRANSFERS join ETH_price p ON (block_timestamp::date = p.price_date) where block_timestamp > CURRENT_DATE - 90 and tx_hash in (select tx_hash from Optimism_cancelled_tx) and FROM_ADDRESS = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and amount != 0.001 ) , 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 -- UNION -- select * from cancel_Optimism -- UNION -- select * from cancel_Optimism_ETH ) , final_Optimism as ( select date, chain, zeroifnull(sum(case when (route_type = 'From' and tx_type != 'Cancel') then 0.001 * ETH_price when (route_type = 'From' and tx_type = 'Cancel') then -0.001 * ETH_price end)) as fee_usd, sum(case when tx_type != 'Cancel' then volume_usd else -volume_usd end) as amount_usd, case when fee_usd != 0 then amount_usd *0.04 * 0.01 else 0 end as variable_fee, fee_usd + variable_fee as total_fee, count(distinct user_address) as user_count, count(distinct case when tx_type = 'Cancel' then tx_hash end) as cancel_txs, count(distinct tx_hash) as all_txs, all_txs - cancel_txs*2 as tx_count, -- deducted creation txs as well sum(amount_usd) over (partition by chain order by date) as cumu_usd, sum(tx_count) over (partition by chain order by date) as cumu_tx, sum(total_fee) over (partition by chain order by date) as cumu_fee from base_Optimism join ETH_price on (date = price_date) -- where route_type = 'From' group by 1,2 ) ------------------------------------------------------- Arbitrum , arbitrum_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as price from Arbitrum.price.ez_prices_hourly 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 >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE and to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , arbitrum_cancelled_tx as ( select tx_hash FROM Arbitrum.core.fact_event_logs where 1 = 1 and block_timestamp > CURRENT_DATE - 90 and topics[0] = '0x7d7d1c5b3eadbe275ceb358e65cd57410b35997187258dbaaae42ab6e1405fd8' ) , from_Arbitrum as ( select block_timestamp::date as date, 'From' as route_type, 'Arbitrum' as chain, 'Creation' as tx_type, 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 >= CURRENT_DATE - 90 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 route_type, 'Arbitrum' as chain, 'Creation' as tx_type, 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_native_TRANSFERS t join eth_price p ON (block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and TO_ADDRESS = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and real_amount != 0 ) , to_Arbitrum as ( select block_timestamp::date as date, 'To' as route_type, 'Arbitrum' as chain, 'Creation' as tx_type, 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 >= CURRENT_DATE - 90 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 route_type, 'Arbitrum' as chain, 'Creation' as tx_type, 'ETH' as symbol, amount, amount_usd as volume_usd, TO_ADDRESS as user_address, tx_hash from Arbitrum.core.EZ_native_TRANSFERS t where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and FROM_ADDRESS = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' and TO_ADDRESS != '0x43de2d77bf8027e25dbd179b491e8d64f38398aa' ) , to_Arbitrum_others as ( select block_timestamp::date as date, 'To' as route_type, 'Arbitrum' as chain, 'Creation' as tx_type, 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 >= CURRENT_DATE - 90 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 ) , cancel_Arbitrum as ( select block_timestamp::date as date, 'From' as route_type, 'Arbitrum' as chain, 'Cancel' as tx_type, 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 join arbitrum_price p ON (contract_address = token_address and block_timestamp::date = price_date) where block_timestamp > CURRENT_DATE - 90 and tx_hash in (select tx_hash from arbitrum_cancelled_tx) and from_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , cancel_Arbitrum_eth as ( select block_timestamp::date as date, 'From' as route_type, 'Arbitrum' as chain, 'Cancel' as tx_type, p.symbol, amount, amount*eth_price as volume_usd, TO_ADDRESS as user_address, tx_hash from Arbitrum.core.EZ_native_TRANSFERS join eth_price p ON (block_timestamp::date = p.price_date) where block_timestamp > CURRENT_DATE - 90 and tx_hash in (select tx_hash from arbitrum_cancelled_tx) and FROM_ADDRESS = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and amount != 0.001 ) , 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 -- UNION -- select * from cancel_Arbitrum -- UNION -- select * from cancel_Arbitrum_eth ) , final_Arbitrum as ( select date, chain, zeroifnull(sum(case when (route_type = 'From' and tx_type != 'Cancel') then 0.001 * ETH_price when (route_type = 'From' and tx_type = 'Cancel') then -0.001 * ETH_price end)) as fee_usd, sum(case when tx_type != 'Cancel' then volume_usd else -volume_usd end) as amount_usd, case when fee_usd != 0 then amount_usd *0.04 * 0.01 else 0 end as variable_fee, fee_usd + variable_fee as total_fee, count(distinct user_address) as user_count, count(distinct case when tx_type = 'Cancel' then tx_hash end) as cancel_txs, count(distinct tx_hash) as all_txs, all_txs - cancel_txs*2 as tx_count, -- deducted creation txs as well sum(amount_usd) over (partition by chain order by date) as cumu_usd, sum(tx_count) over (partition by chain order by date) as cumu_tx, sum(total_fee) over (partition by chain order by date) as cumu_fee from base_Arbitrum join ETH_price on (date = price_date) group by 1,2 ) ---------------------------------------------------------------- Base , base_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as price from Base.price.ez_prices_hourly 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 >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE and to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , base_cancelled_tx as ( select tx_hash FROM Base.core.fact_event_logs where 1 = 1 and block_timestamp > CURRENT_DATE - 90 and topics[0] = '0x7d7d1c5b3eadbe275ceb358e65cd57410b35997187258dbaaae42ab6e1405fd8' ) , from_Base as ( select block_timestamp::date as date, 'From' as route_type, 'Base' as chain, 'Creation' as tx_type, 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 >= CURRENT_DATE - 90 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 route_type, 'Base' as chain, 'Creation' as tx_type, 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_native_TRANSFERS t join eth_price p ON (block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and TO_ADDRESS = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and real_amount != 0 ) , to_Base as ( select block_timestamp::date as date, 'To' as route_type, 'Base' as chain, 'Creation' as tx_type, 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 >= CURRENT_DATE - 90 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 route_type, 'Base' as chain, 'Creation' as tx_type, 'ETH' as symbol, amount, amount_usd as volume_usd, TO_ADDRESS as user_address, tx_hash from Base.core.EZ_native_TRANSFERS t where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and FROM_ADDRESS = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' and TO_ADDRESS != '0x43de2d77bf8027e25dbd179b491e8d64f38398aa' ) , to_Base_others as ( select block_timestamp::date as date, 'To' as route_type, 'Base' as chain, 'Creation' as tx_type, 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 >= CURRENT_DATE - 90 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 ) , cancel_Base as ( select block_timestamp::date as date, 'From' as route_type, 'Base' as chain, 'Cancel' as tx_type, 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 join base_price p ON (contract_address = token_address and block_timestamp::date = price_date) where block_timestamp > CURRENT_DATE - 90 and tx_hash in (select tx_hash from base_cancelled_tx) and from_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , cancel_Base_eth as ( select block_timestamp::date as date, 'From' as route_type, 'Base' as chain, 'Cancel' as tx_type, p.symbol, amount, amount*eth_price as volume_usd, TO_ADDRESS as user_address, tx_hash from Base.core.EZ_native_TRANSFERS join eth_price p ON (block_timestamp::date = p.price_date) where block_timestamp > CURRENT_DATE - 90 and tx_hash in (select tx_hash from base_cancelled_tx) and FROM_ADDRESS = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and amount != 0.001 ) , 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 -- UNION -- select * from cancel_Base -- UNION -- select * from cancel_Base_eth ) , final_Base as ( select date, chain, zeroifnull(sum(case when (route_type = 'From' and tx_type != 'Cancel') then 0.001 * ETH_price when (route_type = 'From' and tx_type = 'Cancel') then -0.001 * ETH_price end)) as fee_usd, sum(case when tx_type != 'Cancel' then volume_usd else -volume_usd end) as amount_usd, case when fee_usd != 0 then amount_usd *0.04 * 0.01 else 0 end as variable_fee, fee_usd + variable_fee as total_fee, count(distinct user_address) as user_count, count(distinct case when tx_type = 'Cancel' then tx_hash end) as cancel_txs, count(distinct tx_hash) as all_txs, all_txs - cancel_txs*2 as tx_count, -- deducted creation txs as well sum(amount_usd) over (partition by chain order by date) as cumu_usd, sum(tx_count) over (partition by chain order by date) as cumu_tx, sum(total_fee) over (partition by chain order by date) as cumu_fee from base_Base join ETH_price on (date = price_date) group by 1,2 ) , final as ( select * from base_ethereum UNION select * from base_polygon UNION select * from base_solana UNION select * from base_bsc UNION select * from base_avalanche UNION select * from base_optimism UNION select * from base_arbitrum UNION select * from base_Base ) , final_all as ( select chain, count(distinct tx_hash) as txs, count(distinct user_address) as user_count, sum(case when tx_type != 'Cancel' then volume_usd else -volume_usd end) as amount_usd from final group by 1 order by txs desc ) select * from final_all basebasebasebasebasebasebasebase

Query	Creator	Created
all 2 bridge to bsc arbitrum base ethereum optimism	h4wk	Feb 8, 2024
all 1 bridge to bsc arbitrum base ethereum optimism	h4wk	Feb 8, 2024
All Avalanche DLN TXs avalanche evm	h4wk	Nov 20, 2023

h4wk