with memecoin_deposit as ( -- get user who had a deposit transaction before (not necessary currently staked) select block_timestamp, symbol, contract_address, --sum(amount) as total_staked, origin_from_address from avalanche.core.ez_token_transfers where to_address = '0xb22c6d1c2897b950fc1040913c0d84d788f24df2' union all select block_timestamp, 'KINGSHIT' as symbol, contract_address, --sum(decoded_log:amount)*pow(10,-18) as total_staked, origin_from_address from avalanche.core.ez_decoded_event_logs where contract_address=lower('0x05b0def5c00ba371683d7035934bcf82b737c364') and event_name='ERC20Transfer' and decoded_log:to='0xb22c6d1c2897b950fc1040913c0d84d788f24df2' ), user_deposit as ( select origin_from_address, count(distinct contract_address) as pool_deposited, min(date(block_timestamp)) as first_deposit from memecoin_deposit group by origin_from_address ), avax_tx as ( -- get user select from_address, min(date(block_timestamp)) as first_avax_tx, -- first tx min(case when block_timestamp>='2023-01-01' and block_timestamp<'2024-01-01' then date(block_timestamp) end) first_avax_tx_2023, min(case when block_timestamp>='2024-01-01' then date(block_timestamp) end) first_avax_tx_2024, count(distinct tx_hash) as tx_count, count(distinct case when block_timestamp>='2023-01-01' and block_timestamp<'2024-01-01' then tx_hash end) tx_since_2023, count(distinct case when block_timestamp>='2024-01-01' then tx_hash end) tx_since_2024 -- 3 mth before rush --count(distinct tx_hash) as tx_1mth-- 1 mth before rush -- rush annoucement --count(distinct case when block_timestamp>= -- during rush from avalanche.core.fact_transactions where 1=1 and from_address not in (select address from avalanche.core.dim_contracts) -- filter out decoded contracts? and from_address in (select distinct origin_from_address from avalanche.core.fact_event_logs) -- filter only logs and from_address in (select origin_from_address from user_deposit) --block_timestamp<='2024-03-15' -- 1 week before launch of 22nd Mar memecoin szn --and block_timestamp>='2023-01-01' group by from_address order by tx_count desc ), sorted as ( select origin_from_address, first_avax_tx, first_avax_tx_2023, first_avax_tx_2024, first_deposit, datediff('day',first_avax_tx_2023,first_deposit) as date_diff_2023, datediff('day',first_avax_tx_2024,first_deposit) as date_diff_2024, tx_count, tx_since_2023, tx_since_2024, -- grouping sorted case when first_deposit = first_avax_tx then 'new to avax' when first_avax_tx_2023 is null and first_avax_tx!=first_avax_tx_2024 and first_avax_tx_2024>=first_deposit-14 then 'returning to avax due to memecoin' when first_avax_tx_2023 is null and first_avax_tx!=first_avax_tx_2024 and first_avax_tx_2024='2024-01-01' then 'existing user since 2024' else 'existing user' end as tx_sorted, case when tx_count>0 and tx_count<=10 then '1. 0 to 10' when tx_count>10 and tx_count<=100 then '2. 10 to 100' when tx_count>100 and tx_count<=1000 then '3. 100 to 1,000' when tx_count>1000 and tx_count<=10000 then '4. 1,000 to 10,000' when tx_count>10000 and tx_count<=100000 then '5. 10,000 to 100,000' end as tx_group, case when tx_since_2024>0 and tx_since_2024<=10 then '1. 0 to 10' when tx_since_2024>10 and tx_since_2024<=100 then '2. 10 to 100' when tx_since_2024>100 and tx_since_2024<=1000 then '3. 100 to 1,000' when tx_since_2024>1000 and tx_since_2024<=10000 then '4. 1,000 to 10,000' when tx_since_2024>10000 and tx_since_2024<=100000 then '5. 10,000 to 100,000' end as tx_group_2024 from user_deposit left join avax_tx on from_address=origin_from_address ) select count(origin_from_address) as user_count, tx_sorted, tx_group, tx_group_2024 from sorted group by tx_sorted, tx_group, tx_group_2024 /* select count(origin_from_address) as user_count, tx_sorted, tx_group from sorted group by tx_sorted, tx_group */

Trader Joe Meme Rush