with pr as ( SELECT date_trunc('day', HOUR) as date, avg(price) as price, CASE when symbol = 'ETH' then '0x000000000000000000000000000000000000800a' when symbol = 'WETH' then '0x5aea5775959fbc2557cc8789bc1bf90a239d9a91' when symbol = 'USDC' then '0x3355df6d4c9c3035724fd0e3914de96a5a83aaf4' when symbol = 'USDT' then '0x493257fd37edb34451f62edf8d2a0c418852ba4c' when symbol = 'BEL' then '0xb83cfb285fc8d936e8647fa9b1cc641dbaae92d9' when symbol = 'BUSD' then '0x2039bb4116b4efc145ec4f0e2ea75012d6c0f181' when symbol = 'BNB' then '0x7400793aad94c8ca801aa036357d10f5fd0ce08f' when symbol = 'MATIC' then '0x28a487240e4d45cff4a2980d334cc933b7483842' when symbol = 'IZI' then '0x16a9494e257703797d747540f01683952547ee5b' when symbol = 'MUTE' then '0x0e97c7a0f8b2c9885c8ac9fc6136e829cbc21d42' when symbol = 'SIS' then '0xdd9f72afed3631a6c85b5369d84875e6c42f1827' when symbol = 'WBTC' then '0xbbeb516fb02a01611cbbe0453fe3c580d7281011' when symbol = 'POOL' then '0x97003ac71cc4a096e06c73e753d9b84f0039a064' when symbol = 'COMBO' then '0xc2b13bb90e33f1e191b8aa8f44ce11534d5698e3' when symbol = 'PERP' then '0x42c1c56be243c250ab24d2ecdcc77f9ccaa59601' when symbol = 'MAV' then '0x787c09494ec8bcb24dcaf8659e7d5d69979ee508' when symbol = 'ZAT' then '0x47ef4a5641992a72cfd57b9406c9d9cefee8e0c4' else null end as contract_address, CASE when symbol = 'ETH' then 18 when symbol = 'WETH' then 18 when symbol = 'USDC' then 6 when symbol = 'USDT' then 6 when symbol = 'BEL' then 18 when symbol = 'BUSD' then 18 when symbol = 'BNB' then 18 when symbol = 'MATIC' then 18 when symbol = 'IZI' then 18 when symbol = 'MUTE' then 18 when symbol = 'SIS' then 18 when symbol = 'WBTC' then 8 when symbol = 'POOL' then 18 when symbol = 'COMBO' then 18 when symbol = 'PERP' then 18 when symbol = 'MAV' then 18 when symbol = 'ZAT' then 18 end as dec FROM crosschain.price.ez_hourly_token_prices WHERE contract_address is not NULL and hour :: date >= '2023-02-14' GROUP BY 1, 3, 4 ), all_t as ( SELECT value:con as con, value:d as d, value:h as h, value:c as c, value:v :: int as value FROM ( SELECT chainbase_utils.post( '/v1/dw/query', { 'query': ' select "vl_tx" as con, date_trunc("day",block_timestamp) as d, transaction_hash as h, case when contract_address = "0x32Fd44bB869620C0EF993754c8a00Be67C464806" then "0x000000000000000000000000000000000000800A" when contract_address = "0x75Af292c1c9a37b3EA2E6041168B4E48875b9ED5" then "0x000000000000000000000000000000000000800A" when contract_address = "0x1382628e018010035999A1FF330447a0751aa84f" then "0x2039bb4116B4EFc145Ec4f0e2eA75012D6C0f181" when contract_address = "0x503234F203fC7Eb888EEC8513210612a43Cf6115" then "0x2039bb4116B4EFc145Ec4f0e2eA75012D6C0f181" when contract_address = "0x8E86e46278518EFc1C5CEd245cBA2C7e3ef11557" then "0x3355df6D4c9C3035724Fd0e3914dE96A5a83aaf4" else contract_address end as c, value as v from zksync.token_transfers WHERE from_address = "{{wallet_address}}" and to_address != "0x0000000000000000000000000000000000008001" and from_address != to_address union all select "all_t" as con, date_trunc("month",block_timestamp) as d, transaction_hash as h, to_address as c, reinterpretAsUInt32(input) as v from zksync.transactions WHERE from_address = "{{wallet_address}}" union all SELECT "nft" as con, block_timestamp as d, transaction_hash as h, contract_address c, value as v from zksync.token_transfers where ( from_address = "{{wallet_address}}" or to_address = "{{wallet_address}}" ) and token_id like "0x%" and transaction_hash in( SELECT transaction_hash from zksync.transactions WHERE to_address in ( "0xf7Ce7998B4c8aFc97a15c32E724ae2C0D0F90F73", "0x64848EEfbC2921102A153b08fa64536AE1f8e937", "0x36e3f736b15f9783E541Fa0a698d74Db13a4218F", "0x47796c60c9a734ef3cbbbcf29688b50c3c0c77da", "0x035942696B2d2D22EaAF0B226cc79e14597F5569", "0xc46C966Dd05447DeC36152254306FeFFd0116338", "0xaca0d132a31352cebd63536ed65541a0e2e4be13", "0x53ec17bd635f7a54b3551e76fd53db8881028fc3", "0x6f6b9063097492cbb782ea8269ce11f411f03ee2" )) union all SELECT "bridge" as con, date_trunc("day",block_timestamp) as d, transaction_hash h, contract_address c, value as v from zksync.token_transfers where to_address = "{{wallet_address}}" and transaction_hash in ( SELECT transaction_hash from zksync.transactions WHERE to_address in ( "0xE0B015E54d54fc84a6cB9B666099c46adE9335FF", "0x53596aB134569396B9A7070d2c4e6Cd536b3c130", "0xb0D30aD9C1A7b303977DB7ea073a4329d930D94c", "0xe4e156167cc9c7ac4abd8d39d203a5495f775547", "0x54069e96C4247b37C2fbd9559CA99f08CD1CD66c", "0x2DcC88Fa6b6950EE28245C3238B8993BE5feeA42" ) ) and from_address != "0x0000000000000000000000000000000000008001" UNION ALL SELECT "bridge" as con, date_trunc("day",block_timestamp) as d, transaction_hash as h, contract_address as c, value as v from zksync.token_transfers where from_address = "{{wallet_address}}" and from_address = to_address UNION ALL SELECT "bridge" as con, date_trunc("day",block_timestamp) as d, transaction_hash as h, contract_address as c, value as v from zksync.token_transfers where from_address in ( "0xe4edb277e41dc89ab076a1f049f4a3efa700bce8", "0x80c67432656d59144ceff962e8faf8926599bcf8", "0x41d3D33156aE7c62c094AAe2995003aE63f587B3", "0xee73323912a4e3772b74ed0ca1595a152b0ef282", "0x0a88bc5c32b684d467b43c06d9e0899efeaf59df" ) and to_address = "{{wallet_address}}" ' } ) :: variant AS resp ) AS subquery, LATERAL FLATTEN(input => resp:data:data:result) ), all_v as( SELECT sum(value / pow(10, dec) * price) as volume FROM all_t t inner join pr p on t.c = p.contract_address and p.date = t.d WHERE con = 'vl_tx' ), tx_f as ( SELECT count(DISTINCT d) as distinct_month, count(DISTINCT h) as tx_count FROM all_t WHERE con = 'all_t' ), nft_c as ( SELECT count(h) as nft_count FROM all_t WHERE con = 'nft' ), bridge_v as ( SELECT sum(value / pow(10, dec) * price) as volume, count(DISTINCT h) as tr_C FROM all_t t inner join pr p on t.c = p.contract_address and p.date = t.d WHERE con = 'bridge' ), s_count as ( SELECT count( DISTINCT( CASE when con = 'all_t' and value != '30768' then c when con = 'vl_tx' then c end ) ) as sm_count FROM all_t WHERE con = 'all_t' or con = 'vl_tx' ),vl_p as( SELECT case when volume < 10000 then 0 when volume >= 10000 and volume < 50000 then 1 when volume >= 50000 and volume < 250000 then 2 when volume >= 250000 then 3 end as v_p from all_v ), mo_p as( SELECT case when distinct_month < 3 then 0 when distinct_month >= 3 and distinct_month < 6 then 1 when distinct_month >= 6 and distinct_month < 9 then 2 when distinct_month >= 9 then 3 end as m_p from tx_f ), tx_p as ( SELECT case when tx_count < 20 then 0 when tx_count >= 20 and tx_count < 50 then 1 when tx_count >= 50 and tx_count < 100 then 2 when tx_count >= 100 then 3 end as t_p from tx_f ) , br_p as ( SELECT case when volume < 10000 then 0 when volume >= 10000 and volume < 50000 then 1 when volume >= 50000 and volume < 250000 then 2 when volume >= 250000 then 3 end as br_p from bridge_v ), fu_p as ( SELECT case when tr_c >= 1 then 1 when tr_c = 0 then 0 end as f_p from bridge_v ), nf_p as ( SELECT CASE when nft_count < 5 then 0 when nft_count >= 5 and nft_count < 10 then 1 when nft_count >= 10 and nft_count < 25 then 2 when nft_count >= 25 then 3 end as n_p from nft_c ), sm_p as ( SELECT case when sm_count < 10 then 0 when sm_count >= 10 and sm_count < 25 then 1 when sm_count >= 25 and sm_count < 50 then 2 when sm_count >= 50 then 3 end as s_p from s_count ),all_p as ( SELECT S_P+n_p+t_p+v_p+br_p+f_p+m_p as total_p FROM sm_p,nf_p,tx_p,vl_p,br_p,fu_p,mo_p) SELECT total_p as "Your total points", all_v.volume as "total Volume (USD)", tx_count as "transaction count", sm_count as "interacted smart contracts", nft_count as "Buy or sell nft", distinct_month as "distinct months activity", bridge_v.volume as "Bridge volume to zksync era" from all_v, s_count, nft_c, bridge_v, tx_f, all_p

Query	Creator	Created
YOUR WALLET STATUS	joondeka-RwL7sL	Nov 2, 2023
Getting Started	joondeka-RwL7sL	Oct 16, 2023

joondeka-RwL7sL