Wallet 360

select 

    nvl(entities.project, 'Unknown') as funder,

    nvl(entities.category, 'Unknown') as funder_category,

    count(1) as wallet_count

from ethereum.wallet_features.wallet_360

left join common.identity.entities on first_funding_address = address and chain = 'ethereum'

where array_contains('rainbow_wallet'::variant, app_list)

group by all

order by 1 asc

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