select

tx_id,

event_data:id as nfts,

try_parse_json(EVENT_DATA:metadata) as data,

when data[7]:value:value like 'Star Wars™: Volume III%' then 'Star Wars™ Volume III,0'

when data[7]:value:value like '%Star Wars™: Volume II%' then 'Star Wars™ Volume II,39.99'

when data[7]:value:value like '%Star Wars™: Volume I%' then 'Star Wars™ Volume I,39.99'

when data[7]:value:value like '%Star Wars™ Special Editions: Yoda%' then 'Star Wars™ Special Editions Yoda,29.99'

when data[7]:value:value like '%Star Wars™ Special Editions: Anakin Skywalker%' then 'Star Wars™ Special Editions Anakin Skywalker,29.99'

when data[7]:value:value like '%Star Wars™ Special Editions: Boba Fett%' then 'Star Wars™ Special Editions Boba Fett,29.99'

else 'not registered' end as collect,

split(collect, ',')[1] as price_usd,

from flow.core.fact_events

where event_contract = 'A.ca63ce22f0d6bdba.Cryptoys'

and event_type = 'Minted'

and block_timestamp>='2023-06-01'

and data[7]:value:value ilike '%Star%'

),

grouping as (

select

event_data:to as users,

tx_id,

collect,

nfts,

price_usd

from flow.core.fact_events

join mints using (tx_id)

where event_type = 'Deposit'