Query editor
WITH zksync AS (SELECT*FROM zksync.transactions),user_summary AS (SELECT"from" AS user_address,COUNT(DISTINCT "to") AS dest_address_count,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 zksync_age_days,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,SUM(b.value * p.price) / POWER(10, 18) AS amount_usdFROM zksync bLEFT JOIN prices.usd pON DATE_TRUNC('minute', b.block_time) = p.minute AND p.contract_address = 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2GROUP BY 1),user_summary_with_rank AS (SELECT *,(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(zksync_age_days >= 100, 1, 0) -- Started using Layer Zero before 100 days+ IF(zksync_age_days >= 200, 1, 0) -- Started using Layer Zero before 200 days+ IF(zksync_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(dest_address_count >= 5, 1, 0) -- Interacted with more than 5 contracts on source chain+ IF(dest_address_count >= 10, 1, 0) -- Interacted with more than 10 contracts on source chain+ IF(dest_address_count >= 25, 1, 0) -- Interacted with more than 25 contracts on source chain+ IF(dest_address_count >= 50, 1, 0) -- Interacted with more than 100 contracts on source chain+ IF(dest_address_count >= 100, 1, 0) -- Interacted with more than 100 contracts on 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