with degods_nfts as ( select replace(ADDRESS_NAME,'mad lad #','') as id, ADDRESS as nft_address, ADDRESS_NAME as nft_name from solana.core.dim_labels where ADDRESS_NAME='degod' and label='degod' ), degods_mints as ( select BLOCK_TIMESTAMP as mint_date, TX_ID as mint_tx, id, nft_address, nft_name from solana.core.fact_nft_mints join degods_nfts on mint=nft_address where BLOCK_TIMESTAMP>='2021-10-01' and SUCCEEDED=true), degods_sales as ( select BLOCK_TIMESTAMP as buy_date, id, initcap(marketplace) as marketplace, PURCHASER as buyer, seller, nft_name, SALES_AMOUNT as amount, nft_address, tx_id from solana.core.fact_nft_sales join degods_nfts on mint=nft_address where BLOCK_TIMESTAMP>='2021-10-01' and SUCCEEDED=true), degods_holders_raw as (select nft_name, holder, nft_address, transfers, date as buy_date, sol_balance from ( select nft_name, TX_TO as holder, nft_address, a.BLOCK_TIMESTAMP as date, PRE_BALANCES[0]/1e9 as sol_balance, sum(1) over (partition by nft_address) as transfers, rank() over (partition by nft_address order by date desc) as rank from solana.core.fact_transfers a join degods_nfts on mint=nft_address join solana.core.fact_transactions c on c.TX_ID=a.TX_ID and c.BLOCK_TIMESTAMP>='2021-10-01' where a.BLOCK_TIMESTAMP>='2021-10-01') where rank=1), degods_info as ( select a.*, count(distinct tx_id) as sales, sum(amount) as volume, volume/sales as average_price from degods_holders_raw a left join degods_sales b on a.nft_address=b.nft_address group by 1,2,3,4,5,6), degods_holders_stas as ( select holder, max(sol_balance) as balance, case when balance<10 then 'Minnows [0 - 10) $SOL' when balance>=10 and balance<100 then 'Clownfish [10 - 100) $SOL' when balance>=100 and balance<1000 then 'Dolphins [100 - 1,000) $SOL' when balance>=1000 and balance<10000 then 'Sharks [1,000 - 10,000) $SOL' else 'Whales [10,000 - ..) $SOL' end as balance_tag, count(*) as nfts from degods_holders_raw group by 1), mad_lad_nfts as ( select replace(ADDRESS_NAME,'mad lad #','') as id, ADDRESS as nft_address, ADDRESS_NAME as nft_name from solana.core.dim_labels where ADDRESS_NAME like 'mad lad%' and LABEL_TYPE='nft' union all select replace(ADDRESS_NAME,'mad rug #','') as id, ADDRESS as nft_address, replace(ADDRESS_NAME,'rug','lad') as nft_name from solana.core.dim_labels where ADDRESS_NAME like 'mad rug%' and LABEL='mad lads' ), mad_lad_mints as ( select BLOCK_TIMESTAMP as mint_date, TX_ID as mint_tx, id, nft_address, nft_name from solana.core.fact_nft_mints join mad_lad_nfts on mint=nft_address where BLOCK_TIMESTAMP>='2023-04-10' and SUCCEEDED=true), mad_lad_minters as ( select TX_FROM as minter, PRE_BALANCES[0]/1e9 as sol_balance, case when sol_balance<10 then 'Minnows [6.9 - 10) $SOL' when sol_balance>=10 and sol_balance<100 then 'Clownfish [10 - 100) $SOL' when sol_balance>=100 and sol_balance<1000 then 'Dolphins [100 - 1,000) $SOL' when sol_balance>=1000 and sol_balance<10000 then 'Sharks [1,000 - 10,000) $SOL' else 'Whales [10,000 - ..) $SOL' end as balance_tag, b.* from solana.core.fact_transfers a join mad_lad_mints b on a.TX_ID=mint_tx join solana.core.fact_transactions c on c.TX_ID=mint_tx and c.BLOCK_TIMESTAMP>='2023-04-10' where a.BLOCK_TIMESTAMP>='2023-04-10' and AMOUNT=6.9 and TX_TO='2RtGg6fsFiiF1EQzHqbd66AhW7R5bWeQGpTbv2UMkCdW'), mad_lad_sales as ( select BLOCK_TIMESTAMP as buy_date, id, initcap(marketplace) as marketplace, PURCHASER as buyer, seller, nft_name, SALES_AMOUNT as amount, nft_address, tx_id from solana.core.fact_nft_sales join mad_lad_nfts on mint=nft_address where BLOCK_TIMESTAMP>='2023-04-10' and SUCCEEDED=true), mad_lad_holders_raw as (select id, nft_name, holder, nft_address, transfers, date as buy_date, sol_balance from ( select id, nft_name, TX_TO as holder, nft_address, a.BLOCK_TIMESTAMP as date, POST_BALANCES[0]/1e9 as sol_balance, sum(1) over (partition by nft_address) as transfers, rank() over (partition by nft_address order by date desc) as rank from solana.core.fact_transfers a join mad_lad_nfts on mint=nft_address join solana.core.fact_transactions c on c.TX_ID=a.TX_ID and c.BLOCK_TIMESTAMP>='2023-04-10' where a.BLOCK_TIMESTAMP>='2023-04-10') where rank=1), mad_lad_holders as ( select id, nft_name, minter as holder, nft_address, 0 as transfers, mint_date as buy_date, sol_balance from mad_lad_minters where nft_address not in (select nft_address from mad_lad_holders_raw) union all select * from mad_lad_holders_raw), mad_lad_info as ( select a.*, count(distinct tx_id) as sales, sum(amount) as volume, volume/sales as average_price from mad_lad_holders a left join mad_lad_sales b on a.nft_address=b.nft_address group by 1,2,3,4,5,6,7), mad_lad_holders_stas as ( select holder, max(sol_balance) as balance, case when balance<10 then 'Minnows [0 - 10) $SOL' when balance>=10 and balance<100 then 'Clownfish [10 - 100) $SOL' when balance>=100 and balance<1000 then 'Dolphins [100 - 1,000) $SOL' when balance>=1000 and balance<10000 then 'Sharks [1,000 - 10,000) $SOL' else 'Whales [10,000 - ..) $SOL' end as balance_tag, count(*) as nfts from mad_lad_info group by 1) select 'Mad Lads' as "NFT Collceiton",balance_tag as "Balance", count(*) as "Holders" from mad_lad_holders_stas group by 1,2 union all select 'Degods' as "NFT Collceiton" ,balance_tag as "Balance", count(*) as "Holders" from degods_holders_stas group by 1,2

Solana Is Mad For Mad Lads