-- forked from 2023-10-10 02:01 AM @ https://flipsidecrypto.xyz/edit/queries/6242d3b1-8fad-40d4-a549-cde4fc7fad64

WITH mints AS (

SELECT

tx_id,

block_timestamp

FROM flow.core.fact_events

WHERE (event_contract LIKE '%A.ca63ce22f0d6bdba.Cryptoys%'

and event_data:metadata like '%Mickey and Friends: Volume I%'

AND event_type = 'Minted'

AND tx_succeeded = 'true')

),

minters AS (

SELECT

event_data:to minter,

COUNT(DISTINCT event_data:id) AS NFTs,

DATE_TRUNC('HOUR', block_timestamp) AS hour

FROM flow.core.fact_events

JOIN mints USING (tx_id)

WHERE event_type = 'Deposit'

GROUP BY 1, 3

SELECT

DATE_TRUNC('HOUR', hour) AS hour,

COUNT(DISTINCT minter) AS minters,

ROUND(AVG(NFTs)) AS avg_mints_per_minter,

SUM(NFTs) AS total_NFTs,

SUM(NFTs * 20) AS total_volume

FROM minters

GROUP BY 1

ORDER BY 1;