WITH tab0 as (

SELECT

DISTINCT tx_id as tix

FROM solana.core.fact_nft_sales a

LEFT outer JOIN solana.core.dim_nft_metadata b

ON a.mint = b.mint

WHERE CONTRACT_NAME LIKE '{{ nft_collections }}'

), prices AS (

SELECT date_trunc('month', recorded_hour)::date AS month

, AVG(close) AS price

FROM crosschain.core.fact_hourly_prices

WHERE id = 'solana'

AND close IS NOT NULL

GROUP BY 1

), b0 AS (

SELECT s.tx_id

, s.sales_amount

, s.mint

, CASE WHEN s.marketplace LIKE 'magic eden%' THEN 'Magic Eden' ELSE INITCAP(s.marketplace) END AS marketplace

, s.block_timestamp

, COALESCE(INITCAP(l.label), 'Other') AS collection

, SUM(

CASE WHEN tx_to IN (

'2NZukH2TXpcuZP4htiuT8CFxcaQSWzkkR6kepSWnZ24Q' -- magic eden v1

, 'rFqFJ9g7TGBD8Ed7TPDnvGKZ5pWLPDyxLcvcH2eRCtt' -- magic eden v2

, 'Fz7HjwoXiDZNRxXMfLAAJLbArqjCTVWrG4wekit2VpSd' -- yawww

, '39fEpihLATXPJCQuSiXLUSiCbGchGYjeL39eyXh3KbyT' -- solanart

, '6QEJwoTfHg4vkwE6nbprtwiwEw7msvNuZJ1tp22SPACE' -- hyperspace

, '6LQWHVXVyauAUS4KQ1wW1EvwHoauEunPN923LWhaYQx7' -- coral cube

, '6482e33zrerYfhKAjPR2ncMSrH2tbTy5LDjdhB5PXzxd' -- exchange art

, '8mcjXbJ8j4VryYFNpcBCFS37Au8zVYU53WTVaruJWcKt' -- opensea

, 'bDmnDkeV7xqWsEwKQEgZny6vXbHBoCYrjxA4aCr9fHU' -- solana monkey business marketplace

, 'DKeBWDK1jGkDvo6TGjZ2bGFBCTyZZstFhAJjWR7y2a1E' -- solport

) THEN COALESCE(t.amount, 0) ELSE 0 END

) AS m_amt