SELECT DISTINCT tx_id AS hash, BLOCK_TIMESTAMP::date AS date

FROM flow.core.fact_events

WHERE EVENT_TYPE = 'ListingCompleted' AND EVENT_DATA:customID = 'flowverse-nft-marketplace'

),

tab2 AS (

SELECT DISTINCT tx_id AS hash2

FROM flow.core.fact_events

JOIN tab1 ON tx_id = hash

WHERE EVENT_TYPE = 'ListingCompleted' AND EVENT_DATA:purchased = 'true'

),

monthly_stats AS (

SELECT

trunc(a.block_timestamp, 'month') AS month,

COUNT(DISTINCT EVENT_DATA:to) AS buyers,

COUNT(EVENT_DATA:id) AS NFTs,

COUNT(DISTINCT hash2) AS trades,

sum(case when currency='A.1654653399040a61.FlowToken' then s.price*p.price else s.price end) as volume_usd

FROM flow.core.fact_events AS a

JOIN tab2 ON a.tx_id = hash2

JOIN flow.core.dim_contract_labels AS b ON b.EVENT_CONTRACT = a.EVENT_CONTRACT

JOIN flow.nft.ez_nft_sales s on s.tx_id=a.tx_id

JOIN flow.price.ez_prices_hourly p on trunc(s.block_timestamp,'hour')=hour --and s.currency=token

WHERE EVENT_TYPE = 'Deposit' and p.symbol ilike '%Flow%'

GROUP BY 1

month,

buyers,

NFTs,

trades,

volume_usd,

100 * (buyers - lag(buyers) OVER ( ORDER BY month)) / lag(buyers) OVER ( ORDER BY month) AS buyers_growth_percentage,

100 * (NFTs - lag(NFTs) OVER ( ORDER BY month)) / lag(NFTs) OVER ( ORDER BY month) AS NFTs_growth_percentage,

100 * (trades - lag(trades) OVER ( ORDER BY month)) / lag(trades) OVER ( ORDER BY month) AS trades_growth_percentage,

100 * (volume_usd - lag(volume_usd) OVER ( ORDER BY month)) / lag(volume_usd) OVER ( ORDER BY month) AS volume_growth_percentage,