bots as (

select

column1 as name,

column2 as pubkey

from values

('liquid staking', 'opLSF7LdfyWNBby5o6FT8UFsr2A4UGKteECgtLSYrSm'),

('native staking', 'opNS8ENpEMWdXcJUgJCsJTDp7arTXayoBEeBUg6UezP'),

('tips', 'Tip2wnUSbEw2VQ6m95Mq8QSbMoX6Cmyn5QBaXjTNFeB')

)

bots.name as "Bot",

substr(date_trunc('month', block_timestamp), 0, 10) as "Period",

count(*) as "Tx count",

sum(array_size(instructions)) as "Ix count",

sum(fee / 1e9) as "Tx costs",

solana.core.fact_transactions

inner join bots on array_contains(bots.pubkey::variant, signers)

date_trunc('day', block_timestamp) between '2024-05-01' and '2024-07-01'

group by

bots.name, date_trunc('month', block_timestamp)

order by

date_trunc('month', block_timestamp) desc,

bots.name asc

limit 12