Query editor
WITH user_summary AS (SELECTuser_address,COUNT(*) AS transaction_count,MIN(block_time) AS initial_block_time,MAX(block_time) AS last_block_time,DATE_DIFF('day', MIN(block_time), NOW()) AS lz_age_days,COUNT(DISTINCT source_chain_id) AS active_source_chain_count,COUNT(DISTINCT destination_chain_id) AS active_destination_chain_count,COUNT(DISTINCT transaction_contract) AS active_transaction_contract_count,COUNT(DISTINCT DATE_TRUNC('day', block_time)) AS active_days_count,COUNT(DISTINCT DATE_TRUNC('week', block_time)) AS active_weeks_count,COUNT(DISTINCT DATE_TRUNC('month', block_time)) AS active_months_count,COALESCE(SUM(amount_usd), 0) AS amount_usdFROMlayerzero.sendGROUP BY1),user_summary_with_rank AS (SELECT*,(active_source_chain_count -- Count of used source chains+ 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+ IF(transaction_count >= 5, 1, 0) -- Conducted more than 5 transactions+ IF(transaction_count >= 10, 1, 0) -- Conducted more than 10 transactions+ IF(transaction_count >= 25, 1, 0) -- Conducted more than 25 transactions+ IF(transaction_count >= 50, 1, 0) -- Conducted more than 50 transactions+ IF(transaction_count >= 100, 1, 0) -- Conducted more than 100 transactions+ IF(active_transaction_contract_count >= 5, 1, 0) -- Interacted more than 5 contracts on the source chain+ IF(active_transaction_contract_count >= 10, 1, 0) -- Interacted more than 10 contracts on the source chain+ IF(active_transaction_contract_count >= 25, 1, 0) -- Interacted more than 25 contracts on the source chain+ IF(active_transaction_contract_count >= 50, 1, 0) -- Interacted more than 100 contracts on the source chain+ IF(active_transaction_contract_count >= 100, 1, 0) -- Interacted more than 100 contracts on the source chain+ IF(amount_usd > 0, 1, 0) -- Bridged funds through Layer Zero+ IF(amount_usd > 1000, 1, 0) -- Bridged more than $1,000 of assets through Layer Zero+ IF(amount_usd > 10000, 1, 0) -- Bridged more than $10,000 of assets through Layer Zero+ IF(amount_usd > 50000, 1, 0) -- Bridged more than $50,000 of assets through Layer Zero+ IF(amount_usd > 250000, 1, 0) -- Bridged more than $250,000 of assets through Layer Zero+ IF(amount_usd > 500000, 1, 0) -- Bridged more than $500,000 of assets through Layer Zero+ IF(amount_usd > 1000000, 1, 0) -- Bridged more than $1,000,000 of assets through Layer Zero