with all_txs as (

select

SIGNERS[0] as miner,INSTRUCTION:parsed:info:amount as mints,

TX_ID as tx,BLOCK_ID,BLOCK_TIMESTAMP as TIMESTAMP,case

when INSTRUCTION:parsed:info:mintAuthority = 'Hv5stMw2f5G4xmogQMX7BJ1Fyn5TWUEHEv5ZaackXS5C' then 'Nebula'

when INSTRUCTION:parsed:info:mintAuthority = '23buSpWBskpcVtkQsWtcugmbfqsYqHpsDLe4tj5mmAqN' then 'The Odyssey'

when INSTRUCTION:parsed:info:mintAuthority = 'CrSPztLxSBkPdqdgoU54TVd9cyfZXCBRkYiCXZET2gwL' then 'Solar Companions'

when INSTRUCTION:parsed:info:mintAuthority = 'GC6VtWCJR8MNtDRZXaPnD5CzbUx6NsUNQ89VBpdJ8uPK' then 'Stars among us'

when INSTRUCTION:parsed:info:mintAuthority = '8Nga3QCpWiXjZrF8ap7e8ceWVQocdQbW6GdZMubfQ17n' then 'Blue'

when INSTRUCTION:parsed:info:mintAuthority = '8gtjn4LumJ3Yo9tCbL7GdR8mexvmFuGfTLzi1S4EZqSe' then 'Starborn'

when INSTRUCTION:parsed:info:mintAuthority = 'Fgs3auKevWQeECXasoZ1aCyuNr2cXK7rP9eKQFC3kZJv' then 'Andromeda'

when INSTRUCTION:parsed:info:mintAuthority = '6wBjjwmWAZzuzAYt9N7EbhPsthE6m2LStNAxZUbViqQb' then 'Over Open Land'

when INSTRUCTION:parsed:info:mintAuthority = '3x6Gqc2qFTgwq87LEe1wmybPJLFZPiE8QnSXxDkrAC9E' then 'ASC'

when INSTRUCTION:parsed:info:mintAuthority = '3bHD7zQGmxVJnxJaSCkzrZyPRUH9Tx5RjkMjUN8fyRTU' then 'AOFE'

end as collection

from eclipse.core.fact_events_inner

where --TX_id = '5RzCvnhohzmrzeLqpD9muo99RudPgwtAFfUvBaHfXrPx3BarqtPpmhLteEFHaxRisbeu6Wh8HaNdAdsaTaFtAipB'

INSTRUCTION_PROGRAM_ID in ('RariUNM3vz1rwxPg8UJyRAN7rSKXxgd2ncS2ddCa4ZE','5hEa5j38yNJRM9vQA44Q6gXVj4Db8y3mWxkDtQeofKKs')

and EVENT_TYPE = 'mintTo'

and INSTRUCTION:parsed:info:mintAuthority in

('Hv5stMw2f5G4xmogQMX7BJ1Fyn5TWUEHEv5ZaackXS5C','23buSpWBskpcVtkQsWtcugmbfqsYqHpsDLe4tj5mmAqN','CrSPztLxSBkPdqdgoU54TVd9cyfZXCBRkYiCXZET2gwL',

'GC6VtWCJR8MNtDRZXaPnD5CzbUx6NsUNQ89VBpdJ8uPK','8Nga3QCpWiXjZrF8ap7e8ceWVQocdQbW6GdZMubfQ17n','8gtjn4LumJ3Yo9tCbL7GdR8mexvmFuGfTLzi1S4EZqSe',

'Fgs3auKevWQeECXasoZ1aCyuNr2cXK7rP9eKQFC3kZJv','6wBjjwmWAZzuzAYt9N7EbhPsthE6m2LStNAxZUbViqQb','3x6Gqc2qFTgwq87LEe1wmybPJLFZPiE8QnSXxDkrAC9E',

'3bHD7zQGmxVJnxJaSCkzrZyPRUH9Tx5RjkMjUN8fyRTU')

and SUCCEEDED = 'TRUE'),

prc as (

select HOUR::date as day, avg(close) as price

from crosschain.price.fact_prices_ohlc_hourly

where HOUR::date >= '2024-09-01'

and asset_id = 'ethereum'

group by all