WITH

transaction_logs AS (

SELECT

x.block_timestamp,

x.tx_id,

transaction_result as logs1,

y.event_data:metadata as pack,

count(distinct event_data:id) as nfts,

try_parse_json(EVENT_DATA:metadata) as data

from flow.core.fact_events y

join flow.core.fact_transactions x on x.tx_id=y.tx_id

where event_contract ilike '%cryptoys%' and event_type='Minted'

and x.tx_succeeded = 'true'

and x.block_timestamp>='2022-11-01'

group by 1,2,3,4

),

users as (

select

tx_id,

event_data:to as wallet

from flow.core.fact_events where tx_id in (select tx_id from transaction_logs)

and event_type = 'Deposit'

),

others as (

data[0]:value:value as skin,

data[1]:value:value as tier,

data[2]:value:value as type,

data[6]:value:value as rarity,

data[11]:value:value as category,

count(distinct x.tx_id) as mints,

count(distinct wallet) as minters,

sum(nfts*39.99) as volume_usd,

volume_usd/minters as volume_spent_per_minter