with

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'),