-------------------- Avalanche -------------------- with avaxu1 as ( select block_timestamp, origin_from_address, amount_usd from avalanche.core.ez_token_transfers where contract_address in ('0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e','0xa7d7079b0fead91f3e65f86e8915cb59c1a4c664') ), avaxu2 as ( select block_timestamp, origin_to_address, amount_usd from avalanche.core.ez_token_transfers where contract_address in ('0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e','0xa7d7079b0fead91f3e65f86e8915cb59c1a4c664') ), avaxMerge as ( select u1.block_timestamp as date, u1.origin_from_address as user, coalesce(u1.amount_usd,0) - coalesce(u2.amount_usd,0) as balance from avaxu1 u1 full join avaxu2 u2 on u1.origin_from_address=u2.origin_to_address ), avaxFinal as ( select -- date_trunc({{time_frame}}, date) as time, case when balance < 10 then 'Less than 10' when balance >= 10 and balance < 1000 then '10 - 1K' when balance >= 1000 and balance < 10000 then '1K - 10K' when balance >= 10000 and balance < 100000 then '10K - 100K' when balance >= 100000 and balance < 1000000 then '100K - 1M' when balance >= 1000000 and balance < 10000000 then '1M - 10M' else 'More than 10M' end as Status, count(DISTINCT(user)) as users from avaxMerge where date >= '{{start_date}}' and date <= '{{end_date}}' group by 1 ), -------------------- Optimism -------------------- opu1 as ( select block_timestamp, origin_from_address, amount_usd from optimism.core.ez_token_transfers where contract_address in ('0x7f5c764cbc14f9669b88837ca1490cca17c31607') ), opu2 as ( select block_timestamp, origin_to_address, amount_usd from optimism.core.ez_token_transfers where contract_address in ('0x7f5c764cbc14f9669b88837ca1490cca17c31607') ), opMerge as ( select u1.block_timestamp as date, u1.origin_from_address as user, coalesce(u1.amount_usd,0) - coalesce(u2.amount_usd,0) as balance from opu1 u1 full join opu2 u2 on u1.origin_from_address=u2.origin_to_address ), opFinal as ( select -- date_trunc({{time_frame}}, date) as time, case when balance < 10 then 'Less than 10' when balance >= 10 and balance < 1000 then '10 - 1K' when balance >= 1000 and balance < 10000 then '1K - 10K' when balance >= 10000 and balance < 100000 then '10K - 100K' when balance >= 100000 and balance < 1000000 then '100K - 1M' when balance >= 1000000 and balance < 10000000 then '1M - 10M' else 'More than 10M' end as Status, count(DISTINCT(user)) as users from opMerge where date >= '{{start_date}}' and date <= '{{end_date}}' group by 1 ), -------------------- Polygon -------------------- maticu1 as ( select block_timestamp, origin_from_address, amount_usd from polygon.core.ez_token_transfers where contract_address in ('0x2791bca1f2de4661ed88a30c99a7a9449aa84174') ), maticu2 as ( select block_timestamp, origin_to_address, amount_usd from polygon.core.ez_token_transfers where contract_address in ('0x2791bca1f2de4661ed88a30c99a7a9449aa84174') ), maticMerge as ( select u1.block_timestamp as date, u1.origin_from_address as user, coalesce(u1.amount_usd,0) - coalesce(u2.amount_usd,0) as balance from maticu1 u1 full join maticu2 u2 on u1.origin_from_address=u2.origin_to_address ), maticFinal as ( select -- date_trunc({{time_frame}}, date) as time, case when balance < 10 then 'Less than 10' when balance >= 10 and balance < 1000 then '10 - 1K' when balance >= 1000 and balance < 10000 then '1K - 10K' when balance >= 10000 and balance < 100000 then '10K - 100K' when balance >= 100000 and balance < 1000000 then '100K - 1M' when balance >= 1000000 and balance < 10000000 then '1M - 10M' else 'More than 10M' end as Status, count(DISTINCT(user)) as users from maticMerge where date >= '{{start_date}}' and date <= '{{end_date}}' group by 1 ), -------------------- Arbitrum -------------------- arbu1 as ( select block_timestamp, origin_from_address, amount_usd from arbitrum.core.ez_token_transfers where contract_address in ('0xaf88d065e77c8cc2239327c5edb3a432268e5831','0xff970a61a04b1ca14834a43f5de4533ebddb5cc8') ), arbu2 as ( select block_timestamp, origin_to_address, amount_usd from arbitrum.core.ez_token_transfers where contract_address in ('0xaf88d065e77c8cc2239327c5edb3a432268e5831','0xff970a61a04b1ca14834a43f5de4533ebddb5cc8') ), arbMerge as ( select u1.block_timestamp as date, u1.origin_from_address as user, coalesce(u1.amount_usd,0) - coalesce(u2.amount_usd,0) as balance from arbu1 u1 full join arbu2 u2 on u1.origin_from_address=u2.origin_to_address ), arbFinal as ( select -- date_trunc({{time_frame}}, date) as time, case when balance < 10 then 'Less than 10' when balance >= 10 and balance < 1000 then '10 - 1K' when balance >= 1000 and balance < 10000 then '1K - 10K' when balance >= 10000 and balance < 100000 then '10K - 100K' when balance >= 100000 and balance < 1000000 then '100K - 1M' when balance >= 1000000 and balance < 10000000 then '1M - 10M' else 'More than 10M' end as Status, count(DISTINCT(user)) as users from arbMerge where date >= '{{start_date}}' and date <= '{{end_date}}' group by 1 ) -------------------- Result -------------------- SELECT 'Avalanche' as "Chain", * from avaxFinal UNION SELECT 'Optimism' as "Chian", * from opFinal UNION SELECT 'Polygon' as "Chain", * from maticFinal UNION SELECT 'Arbitrum' as "Chain", * from arbFinal

USDC: AVAX vs L2