Query editor
WITH initial_data AS (SELECT *FROM query_2465489),user_summary AS (SELECTs.user_address,COUNT(*) AS transaction_count,MIN(s.block_time) AS initial_block_time,MAX(s.block_time) AS last_block_time,date_diff('day', MIN(s.block_time), now()) AS lz_age_days,COUNT(DISTINCT s.source_chain_id) AS active_source_chain_count,COUNT(DISTINCT s.destination_chain_id) AS active_destination_chain_count,COUNT(DISTINCT s.transaction_contract) AS active_transaction_contract_count,COUNT(DISTINCT date_trunc('day', s.block_time)) AS active_days_count,COUNT(DISTINCT date_trunc('week', s.block_time)) AS active_weeks_count,COUNT(DISTINCT date_trunc('month', s.block_time)) AS active_months_count,array_join(array_agg(DISTINCT CAST(s.source_chain_id AS VARCHAR)), '/') AS source_chains,array_join(array_agg(DISTINCT CAST(s.destination_chain_id AS VARCHAR)), '/') AS dest_chains,COALESCE(SUM(s.amount_usd), 0) AS amount_usdFROMinitial_data iJOINlayerzero.send s ON i.user_address = s.user_addressWHEREs.source_chain_id IS NOT NULLAND s.destination_chain_id IS NOT NULLGROUP BYs.user_address),user_summary_with_rank AS (SELECT *,(active_source_chain_count+ if(active_destination_chain_count >= 2, 1, 0) -- Conducted transactions to 2 destination chains+ if(active_destination_chain_count >= 5, 1, 0) -- Conducted transactions to 5 destination chains+ if(active_destination_chain_count >= 10, 1, 0) -- Conducted transactions to 10 destination chains+ if(active_months_count >= 2, 1, 0) -- Conducted transactions during 2 distinct months+ if(active_months_count >= 6 , 1, 0) -- Conducted transactions during 6 distinct months+ if(active_months_count >= 9, 1, 0) -- Conducted transactions during 9 distinct months+ if(active_months_count >= 12, 1, 0) -- Conducted transactions during 12 distinct months+ if(active_weeks_count >= 10, 1, 0) -- Conducted transactions during 10 distinct weeks+ if(active_weeks_count >= 20, 1, 0) -- Conducted transactions during 20 distinct weeks+ if(active_weeks_count >= 50, 1, 0) -- Conducted transactions during 50 distinct weeks+ if(active_weeks_count >= 100, 1, 0) -- Conducted transactions during 100 distinct weeks+ if(active_days_count >= 50, 1, 0) -- Conducted transactions during 50 distinct days+ if(active_days_count >= 100, 1, 0) -- Conducted transactions during 100 distinct days+ if(active_days_count >= 200, 1, 0) -- Conducted transactions during 200 distinct days+ if(active_days_count >= 500, 1, 0) -- Conducted transactions during 500 distinct days+ if(lz_age_days >= 100, 1, 0) -- Started using Layer Zero before 100 days+ if(lz_age_days >= 200, 1, 0) -- Started using Layer Zero before 200 days+ if(lz_age_days >= 500, 1, 0) -- Started using Layer Zero before 500 days