"0ca3cc84338e841affb9b828e3b147400541c2cfbb48dfb4a82cab974c4d2be95"

"3535"

"0ca3cc84338e841affb9b828e3b147400541c2cfbb48dfb4a82cab974c4d2be94"

"vbd"

"0ca3cc84338e841affb9b828e3b147400541c2cfbb48dfb4a82cab974c4d2be93"

"df"

"0ca3cc84338e841affb9b828e3b147400541c2cfbb48dfb4a82cab974c4d2be91"

"--Error: Query is too long for wand tools. \n--Try shortening query to try again. \n--Error: Query is too long for wand tools. \n--Try shortening query to try again. \n-- select *\n-- from query_2465489\n-- order by rank_id\n-- limit 10000\n\nwith user_summary as (\n select user_address, \n count(*) as transaction_count,\n min(block_time) as initial_block_time,\n max(block_time) as last_block_time,\n date_diff('day', min(block_time), now()) as lz_age_days,\n count(distinct source_chain_id) as active_source_chain_count,\n count(distinct destination_chain_id) as active_destination_chain_count,\n count(distinct transaction_contract) as active_transaction_contract_count,\n count(distinct date_trunc('day', block_time)) as active_days_count,\n count(distinct date_trunc('week', block_time)) as active_weeks_count,\n count(distinct date_trunc('month', block_time)) as active_months_count,\n -- coalesce(sum(amount_usd / power(10, p.decimals) * p.price), 0) as amount_usd\n coalesce(sum(amount_usd), 0) as amount_usd\n from layerzero.send\n group by 1\n),\n\nuser_summary_with_rank as (\n select *,\n (\n active_source_chain_count -- Count of used source chains\n \n + if(active_destination_chain_count >= 2, 1, 0) -- Conducted transactions to 2 destination chains\n + if(active_destination_chain_count >= 5, 1, 0) -- Conducted transactions to 5 destination chains\n + if(active_destination_chain_count >= 10, 1, 0) -- Conducted transactions to 10 destination chains\n \n + if(active_months_count >= 2, 1, 0) -- Conducted transactions during 2 distinct months\n + if(active_months_count >= 6 , 1, 0) -- Conducted transactions during 6 distinct months\n + if(active_months_count >= 9, 1, 0) -- Conducted transactions during 9 distinct months\n + if(active_months_count >= 12, 1, 0) -- Conducted transactions during 12 distinct months\n \n + if(active_weeks_count >= 10, 1, 0) -- Conducted transactions during 10 distinct weeks\n + if(active_weeks_count >= 20, 1, 0) -- Conducted transactions during 20 distinct weeks\n + if(active_weeks_count >= 50, 1, 0) -- Conducted transactions during 50 distinct weeks\n + if(active_weeks_count >= 100, 1, 0) -- Conducted transactions during 100 distinct weeks\n \n + if(active_days_count >= 50, 1, 0) -- Conducted transactions during 50 distinct days\n + if(active_days_count >= 100, 1, 0) -- Conducted transactions during 100 distinct days\n + if(active_days_count >= 200, 1, 0) -- Conducted transactions during 200 distinct days\n + if(active_days_count >= 500, 1, 0) -- Conducted transactions during 500 distinct days\n \n \n + if(lz_age_days >= 100, 1, 0) -- Started using Layer Zero before 100 days\n + if(lz_age_days >= 200, 1, 0) -- Started using Layer Zero before 200 days\n + if(lz_age_days >= 500, 1, 0) -- Started using Layer Zero before 500 days\n \n + if(transaction_count >= 5, 1, 0) -- Conducted more than 5 transactions\n + if(transaction_count >= 10, 1, 0) -- Conducted more than 10 transactions\n + if(transaction_count >= 25, 1, 0) -- Conducted more than 25 transactions\n + if(transaction_count >= 50, 1, 0) -- Conducted more than 50 transactions\n + if(transaction_count >= 100, 1, 0) -- Conducted more than 100 transactions\n \n + if(active_transaction_contract_count >= 5, 1, 0) -- Interacted more than 5 contracts on source chain\n + if(active_transaction_contract_count >= 10, 1, 0) -- Interacted more than 10 contracts on source chain\n + if(active_transaction_contract_count >= 25, 1, 0) -- Interacted more than 25 contracts on source chain\n + if(active_transaction_contract_count >= 50, 1, 0) -- Interacted more than 100 contracts on source chain\n + if(active_transaction_contract_count >= 100, 1, 0) -- Interacted more than 100 contracts on source chain\n \n + if(amount_usd > 0, 1, 0) -- Bridged funds through Layer Zero\n + if(amount_usd > 1000, 1, 0) -- Bridged more than $1,000 of assets through Layer Zero\n + if(amount_usd > 10000, 1, 0) -- Bridged more than $10,000 of assets through Layer Zero\n + if(amount_usd > 50000, 1, 0) -- Bridged more than $50,000 of assets through Layer Zero\n + if(amount_usd > 250000, 1, 0) -- Bridged more than $250,000 of assets through Layer Zero\n + if(amount_usd > 500000, 1, 0) -- Bridged more than $500,000 of assets through Layer Zero\n + if(amount_usd > 1000000, 1, 0) -- Bridged more than $1,000,000 of assets through Layer Zero\n ) as rank_score\n from user_summary\n),\n\nmissing_source_chains AS (\n SELECT\n user_address,\n ARRAY_AGG(DISTINCT missing_source_chain_name) AS source_chains_missing,\n COUNT(DISTINCT missing_source_chain_name) AS No_of_chains_missing\n FROM\n (\n SELECT\n s1.user_address,\n s2.source_chain_name AS missing_source_chain_name\n FROM\n (\n SELECT DISTINCT\n user_address\n FROM\n layerzero.send\n ) AS s1\n CROSS JOIN (\n SELECT DISTINCT\n source_chain_name\n FROM\n layerzero.send\n WHERE\n source_chain_name IS NOT NULL\n ) AS s2\n LEFT JOIN\n layerzero.send AS s3 ON s1.user_address = s3.user_address\n AND s2.source_chain_name = s3.source_chain_name\n WHERE\n s3.source_chain_name IS NULL\n ) AS subquery\n GROUP BY\n user_address\n)\n\nSELECT\n row_number() OVER (ORDER BY rank_score DESC, amount_usd DESC, transaction_count DESC) AS rk,\n user_summary_with_rank.user_address AS ua,\n missing_source_chains.source_chains_missing,\n missing_source_chains.No_of_chains_missing\nFROM\n user_summary_with_rank\n JOIN missing_source_chains ON user_summary_with_rank.user_address = missing_source_chains.user_address\nORDER BY\n rank_score DESC,\n amount_usd DESC,\n transaction_count DESC\n LIMIT 890000"