select 'Arbitrum' as chain, date_trunc (day,block_timestamp) as date, count (distinct tx_hash) as TX_Count, count (distinct origin_from_address) as Unique_Users, from arbitrum.core.fact_event_logs where origin_to_address in (select distinct address from arbitrum.core.dim_labels where label_type ilike 'dex') and tx_status = 'SUCCESS' group by 1,2,3 union ALL select 'Optimism' as chain, date_trunc (day,block_timestamp) as date, count (distinct tx_hash) as TX_Count, count (distinct origin_from_address) as Unique_Users, from optimism.core.fact_event_logs where origin_to_address in (select distinct address from optimism.core.dim_labels where label_type ilike 'dex') and tx_status = 'SUCCESS' group by 1,2,3 union ALL select 'Ethereum' as chain, date_trunc (day,block_timestamp) as date, case when date < '2022-11-08' then 'Before Collapse' when date >= '2022-11-08' and date <= '2022-11-10' then 'Main Collapse Days' when date > '2022-11-10' then 'After Collapse' end as timespan, count (distinct tx_hash) as TX_Count, count (distinct origin_from_address) as Unique_Users, count (origin_from_address) as Non_Unique_Users from ethereum.core.fact_event_logs where origin_to_address in (select distinct address from ethereum.core.dim_labels where label_type ilike 'dex') and block_timestamp >= '2022-10-25' and block_timestamp::date != CURRENT_DATE and tx_status = 'SUCCESS' group by 1,2,3 union ALL select 'Polygon' as chain, date_trunc (day,block_timestamp) as date, case when date < '2022-11-08' then 'Before Collapse' when date >= '2022-11-08' and date <= '2022-11-10' then 'Main Collapse Days' when date > '2022-11-10' then 'After Collapse' end as timespan, count (distinct tx_hash) as TX_Count, count (distinct origin_from_address) as Unique_Users, count (origin_from_address) as Non_Unique_Users from polygon.core.fact_event_logs where origin_to_address in (select distinct address from polygon.core.dim_labels where label_type ilike 'dex') and block_timestamp >= '2022-10-25' and block_timestamp::date != CURRENT_DATE and tx_status = 'SUCCESS' group by 1,2,3 union ALL select 'Binance Smart Chain' as chain, date_trunc (day,block_timestamp) as date, case when date < '2022-11-08' then 'Before Collapse' when date >= '2022-11-08' and date <= '2022-11-10' then 'Main Collapse Days' when date > '2022-11-10' then 'After Collapse' end as timespan, count (distinct tx_hash) as TX_Count, count (distinct origin_from_address) as Unique_Users, count (origin_from_address) as Non_Unique_Users from bsc.core.fact_event_logs where origin_to_address in (select distinct address from bsc.core.dim_labels where label_type ilike 'dex') and block_timestamp >= '2022-10-25' and block_timestamp::date != CURRENT_DATE and tx_status = 'SUCCESS' group by 1,2,3 union ALL select 'Avalanche' as chain, date_trunc (day,block_timestamp) as date, case when date < '2022-11-08' then 'Before Collapse' when date >= '2022-11-08' and date <= '2022-11-10' then 'Main Collapse Days' when date > '2022-11-10' then 'After Collapse' end as timespan, count (distinct tx_hash) as TX_Count, count (distinct origin_from_address) as Unique_Users, count (origin_from_address) as Non_Unique_Users from avalanche.core.fact_event_logs where origin_to_address in (select distinct address from avalanche.core.dim_labels where label_type ilike 'dex') and block_timestamp >= '2022-10-25' and block_timestamp::date != CURRENT_DATE and tx_status = 'SUCCESS' group by 1,2,3 union all select 'Solana' as chain, date_trunc (day,block_timestamp) as date, case when date < '2022-11-08' then 'Before Collapse' when date >= '2022-11-08' and date <= '2022-11-10' then 'Main Collapse Days' when date > '2022-11-10' then 'After Collapse' end as timespan, count (distinct tx_id) as TX_Count, count (distinct signers[0]) as Unique_Users, count (signers[0]) as Non_Unique_Users from solana.core.fact_transactions where instructions[0]:programId in (select distinct address from solana.core.dim_labels where label_type ilike 'dex') and block_timestamp >= '2022-10-25' and block_timestamp::date != CURRENT_DATE and succeeded = 'TRUE' group by 1,2,3 union ALL select 'Flow' as chain, date_trunc (day,block_timestamp) as date, case when date < '2022-11-08' then 'Before Collapse' when date >= '2022-11-08' and date <= '2022-11-10' then 'Main Collapse Days' when date > '2022-11-10' then 'After Collapse' end as timespan, count (distinct tx_id) as TX_Count, count (distinct trader) as Unique_Users, count (trader) as Non_Unique_Users from flow.core.ez_swaps where block_timestamp >= '2022-10-25' and block_timestamp::date != CURRENT_DATE group by 1,2,3 union ALL select 'NEAR' as chain, date_trunc (day,block_timestamp) as date, case when date < '2022-11-08' then 'Before Collapse' when date >= '2022-11-08' and date <= '2022-11-10' then 'Main Collapse Days' when date > '2022-11-10' then 'After Collapse' end as timespan, count (distinct tx_hash) as TX_Count, count (distinct trader) as Unique_Users, count (trader) as Non_Unique_Users from near.core.ez_dex_swaps where block_timestamp >= '2022-10-25' and block_timestamp::date != CURRENT_DATE group by 1,2,3 union ALL select 'Osmosis' as chain, date_trunc (day,block_timestamp) as date, case when date < '2022-11-08' then 'Before Collapse' when date >= '2022-11-08' and date <= '2022-11-10' then 'Main Collapse Days' when date > '2022-11-10' then 'After Collapse' end as timespan, count (distinct tx_id) as TX_Count, count (distinct trader) as Unique_Users, count (trader) as Non_Unique_Users from osmosis.core.fact_swaps where block_timestamp >= '2022-10-25' and block_timestamp::date != CURRENT_DATE and tx_status = 'SUCCEEDED' group by 1,2,3

Query	Creator	Created
Daily Blockchains DEXs Activity Before and After FTX Collapse Over Time flow near bsc arbitrum swaps	lgcv	Dec 20, 2022
2023-10-19 08:26 AM bsc arbitrum ethereum optimism nft	lgcv	Oct 19, 2023

lgcv