with recent as(select s.mint as NFT_address, s.posttokenbalances[0]:owner as owner_address, s.block_timestamp as date,

case when s.program_id = 'J7RagMKwSD5zJSbRQZU56ypHUtux8LRDkUpAPSKH4WPp' then 'SMB'

when s.program_id = 'M2mx93ekt1fmXSVkTrUL9xVFHkmME8HTUi5Cyc5aF7K' then 'ME'

else 'Other program_ID' end as Program_ID

from solana.nfts s join solana.dim_nft_metadata m on s.mint = m.mint

where s.succeeded = 'True'

and m.project_name = 'Solana Monkey Business'

group by 1, 2, 3, 4),

row_order as (select r.NFT_address, r.owner_address, r.date, row_number() over (partition by r.NFT_address order by r.date desc) as rn

from recent r

group by 1,2,3),

SMB_owner as (select o.owner_address, count(o.NFT_address) as Total_SMB_owned

from row_order o

where o.rn = 1

group by 1),

voters as (SELECT distinct instructions[0]:accounts[0] AS address_voters,

case when instructions[0]:data ='Yjf5DvKUCfa2bPkYz4AiWs' then 'YES'

when instructions[0]:data ='Yjf5DvKUCfa2Rh5YBEg7FM' then 'NO'

else null end as Vote,

SUM(ARRAY_SIZE(inner_instructions)) AS total_votes

FROM solana.fact_transactions

WHERE block_timestamp <= '2022-03-05' AND block_timestamp >= '2022-02-15'

AND succeeded = 'True'

AND instructions[0]:programId = 'Daovoteq2Y28gJyme6TNUXT9TxXrePiouFuHezkiozci'

and instructions[0]:accounts[6] = 'BD9XQhU1DXA9V4GLdgTq7xdQY5UBnShfq4caTSEL9Lr'

GROUP BY 1, 2

order by 2 desc)

select v.address_voters, v.Vote, o.Total_SMB_owned

from SMB_owner o join voters v on o.owner_address = v.address_voters

where v.Vote = 'YES'