-- forked from cristinatinto / binance all l2 @ https://flipsidecrypto.xyz/cristinatinto/q/F92AvgP7P04u/binance-all-l2 with ins as ( select date_trunc('day',block_timestamp) as day, sum(amount_usd) as volume from arbitrum.core.ez_eth_transfers x where block_timestamp between current_date-30 and current_date-1 and amount_usd<1e9 and origin_to_address in (select distinct address from arbitrum.core.dim_labels where project_name ilike '%binance%') group by 1 union select date_trunc('day',block_timestamp) as day, sum(amount_usd) as volume from arbitrum.core.ez_token_transfers x where block_timestamp between current_date-30 and current_date-1 and amount_usd<1e9 and origin_to_address in (select distinct address from arbitrum.core.dim_labels where project_name ilike '%binance%') group by 1 order by 1 asc ), outs as ( select date_trunc('day',block_timestamp) as day, sum(amount_usd) as volume from arbitrum.core.ez_eth_transfers x where block_timestamp between current_date-30 and current_date-1 and amount_usd<1e9 and origin_from_address in (select distinct address from arbitrum.core.dim_labels where project_name ilike '%binance%') group by 1 union select date_trunc('day',block_timestamp) as day, sum(amount_usd) as volume from arbitrum.core.ez_token_transfers x where block_timestamp between current_date-30 and current_date-1 and amount_usd<1e9 and origin_from_address in (select distinct address from arbitrum.core.dim_labels where project_name ilike '%binance%') group by 1 order by 1 asc ), arbitrum as ( SELECT ifnull(x.day,y.day) as date, ifnull(x.volume,0) as volume_in, ifnull(-y.volume,0) as volume_out, x.volume-y.volume as netflow, sum(netflow) over (order by date) as accumulated_netflow from ins x join outs y on x.day=y.day order by 1 asc ), ins2 as ( select date_trunc('day',block_timestamp) as day, sum(amount_usd) as volume from optimism.core.ez_eth_transfers x where block_timestamp between current_date-30 and current_date-1 and amount_usd<1e9 and origin_to_address in (select distinct address from optimism.core.dim_labels where project_name ilike '%binance%') group by 1 union select date_trunc('day',block_timestamp) as day, sum(amount_usd) as volume from optimism.core.ez_token_transfers x where block_timestamp between current_date-30 and current_date-1 and amount_usd<1e9 and origin_to_address in (select distinct address from optimism.core.dim_labels where project_name ilike '%binance%') group by 1 order by 1 asc ), outs2 as ( select date_trunc('day',block_timestamp) as day, sum(amount_usd) as volume from optimism.core.ez_eth_transfers x where block_timestamp between current_date-30 and current_date-1 and amount_usd<1e9 and origin_from_address in (select distinct address from optimism.core.dim_labels where project_name ilike '%binance%') group by 1 union select date_trunc('day',block_timestamp) as day, sum(amount_usd) as volume from optimism.core.ez_token_transfers x where block_timestamp between current_date-30 and current_date-1 and amount_usd<1e9 and origin_from_address in (select distinct address from optimism.core.dim_labels where project_name ilike '%binance%') group by 1 order by 1 asc ), optimism as ( SELECT ifnull(x.day,y.day) as date, ifnull(x.volume,0) as volume_in, ifnull(-y.volume,0) as volume_out, x.volume-y.volume as netflow, sum(netflow) over (order by date) as accumulated_netflow from ins2 x join outs2 y on x.day=y.day order by 1 asc ), ins3 as ( select date_trunc('day',block_timestamp) as day, sum(amount_usd) as volume from polygon.core.ez_matic_transfers x where block_timestamp between current_date-30 and current_date-1 and amount_usd<1e9 and origin_to_address in (select distinct address from polygon.core.dim_labels where project_name ilike '%binance%') group by 1 union select date_trunc('day',block_timestamp) as day, sum(amount_usd) as volume from polygon.core.ez_token_transfers x where block_timestamp between current_date-30 and current_date-1 and amount_usd<1e9 and origin_to_address in (select distinct address from polygon.core.dim_labels where project_name ilike '%binance%') group by 1 order by 1 asc ), outs3 as ( select date_trunc('day',block_timestamp) as day, sum(amount_usd) as volume from polygon.core.ez_matic_transfers x where block_timestamp between current_date-30 and current_date-1 and amount_usd<1e9 and origin_from_address in (select distinct address from polygon.core.dim_labels where project_name ilike '%binance%') group by 1 union select date_trunc('day',block_timestamp) as day, sum(amount_usd) as volume from polygon.core.ez_token_transfers x where block_timestamp between current_date-30 and current_date-1 and amount_usd<1e9 and origin_from_address in (select distinct address from polygon.core.dim_labels where project_name ilike '%binance%') group by 1 order by 1 asc ), polygon as ( SELECT ifnull(x.day,y.day) as date, ifnull(x.volume,0) as volume_in, ifnull(-y.volume,0) as volume_out, x.volume-y.volume as netflow, sum(netflow) over (order by date) as accumulated_netflow from ins3 x join outs3 y on x.day=y.day order by 1 asc ) select *,'Arbitrum' as chain from arbitrum union select *, 'Optimism' as chain from optimism union select *, 'Polygon' as chain from polygon order by 1 asc

Query	Creator	Created
binance all l2 copy arbitrum optimism polygon l2 evm	angeal	Feb 13, 2024
arb price guess arbitrum l2 evm	angeal	Mar 17, 2023
Recipient Counts arbitrum optimism l2 evm	angeal	Mar 17, 2023
Prime Protocol 102 copy arbitrum l2 evm	angeal	Jun 16, 2023
Prime Protocol 102 copy arbitrum l2 evm	angeal	Jun 16, 2023

angeal