Wallet 360

select
  app_list[0]::VARCHAR as wallet,
  count(wallet_address) as wallet_count,
  count(total_nft_mint_txn) / wallet_count * 100 as "% NFT Minter",
  count(list_nft_trade_marketplace_used) / wallet_count * 100 as "% NFT Buyer",
  count(list_dex_trade_project_used) / wallet_count * 100 as "% DEX Trader"
from
  ethereum.wallet_features.wallet_360,
  lateral flatten(input => app_list)
where 1 = 1 and wallet like '%_wallet%'
group by all

with app as (
select 
    value::varchar as wallet,
    wallet_address 
from ethereum.wallet_features.wallet_360, 
lateral flatten(input => app_list)
    where wallet like '%_wallet%'
),
tokens_bought as (
select 
    wallet,
    trades.token_bought_address,
    trades.token_bought_name,
    count(distinct transaction_from_address) as users,
    avg(usd_amount) as avg_usd_amount,
    sum(usd_amount) as sum_usd_amount 
from ethereum.dex.trades 
    inner join app on wallet_address = transaction_from_address 
    and block_timestamp >= current_timestamp - interval '30 days'
group by all
)
select * from tokens_bought
qualify row_number() over (partition by wallet order by users desc) <= 6

select 
    trades.token_bought_address,
    trades.token_bought_name,
    count(distinct transaction_from_address) as traders
from ethereum.wallet_features.wallet_360 
inner join ethereum.dex.trades 
on wallet_address = transaction_from_address 
and block_timestamp >=current_timestamp - interval '30 days'
where array_contains('coinbase_wallet'::variant, app_list) 
group by all 
order by 3 desc 

select
    case
        when total_usd_balance = 0 or total_usd_balance is null then 'Null'
        when total_usd_balance <= 1000 then 'a. <=1k'
        when total_usd_balance > 1000 and total_usd_balance <= 10000 then 'b. 1-10k'
        when total_usd_balance > 10000 and total_usd_balance <= 100000 then 'c. 10-100k'
        when total_usd_balance > 100000 and total_usd_balance <= 1000000 then 'd. 100k-1m'
        when total_usd_balance > 1000000 then 'e. >1m'
    end as balance_tier,
    avg(stablecoin_usd_balance) as avg_stable_balance,
    count(1) as count_all
from ethereum.wallet_features.wallet_360
where array_contains('opensea_nft_marketplace'::variant, app_list)
group by all
order by 1 asc