SELECT

TX_ID,

BLOCK_TIMESTAMP,

DATE_TRUNC('Day', BLOCK_TIMESTAMP) AS DAY,

CASE

WHEN ARRAY_SIZE(INSTRUCTION:accounts) = 17 THEN INSTRUCTION:accounts [12]

WHEN ARRAY_SIZE(INSTRUCTION:accounts) = 18 THEN INSTRUCTION:accounts [13]

ELSE NULL

END AS MINT,

CASE

WHEN INSTRUCTION:data = 'Y2Dw8sdMvci' THEN 'HatchGenerationalBee'

WHEN INSTRUCTION:data = 'DRxwV6CjBKi' THEN 'HatchGenerationalBeeWithHxd'

ELSE NULL

END AS ACTION

FROM

solana.core.fact_events

WHERE

SUCCEEDED

AND BLOCK_TIMESTAMP >= '2022-06-01'

AND PROGRAM_ID IN(

'HLproJMrRgBz6NNMLgf1tc8RwFtKHbH5KmX6x6KSm7ZS',

'HLgmgZ4rbzAgP3p8Uj8rTa2PZgnZKaxmUVxprwqqUCyN'

) -- 'Y2Dw8sdMvci' is 'HatchGenerationalBee'

-- 'DRxwV6CjBKi' is 'HatchGenerationalBeeWithHxd'

AND INSTRUCTION:data IN('Y2Dw8sdMvci', 'DRxwV6CjBKi')

SELECT *

FROM solana.core.fact_transactions

WHERE TX_ID IN (

SELECT TX_ID

FROM HATCHED_BEES

LIMIT 5