SELECT

sol_volume * price AS volume_usd,

sol_volume,

price,

trades.dt AS dt,

TX_ID,

PURCHASER,

SELLER,

MARKETPLACE,

MINT

FROM

(

SELECT

SALES_AMOUNT as sol_volume,

DATE_TRUNC('DAY', BLOCK_TIMESTAMP) AS dt,

TX_ID,

PURCHASER,

SELLER,

IFF(MARKETPLACE = 'magic eden v2', 'Magic Eden', MARKETPLACE) AS MARKETPLACE,

MINT

FROM solana.nft.fact_nft_sales

WHERE

SUCCEEDED

AND BLOCK_TIMESTAMP BETWEEN '2023-12-12 00:00:00' AND '2024-01-12 00:00:00'

--AND SELLER = 'K5iuD4h1zv46bsw5KNBMDkYKnnZCGoLq151wdJ7uv9d' OR PURCHASER = 'K5iuD4h1zv46bsw5KNBMDkYKnnZCGoLq151wdJ7uv9d'

) trades

LEFT JOIN

(

SELECT avg(CLOSE) as price, DATE_TRUNC('DAY', RECORDED_HOUR) as dt

FROM solana.price.fact_token_prices_hourly

WHERE

SYMBOL = 'SOL' AND ID = '16116' AND PROVIDER = 'coinmarketcap'

AND RECORDED_HOUR BETWEEN '2023-12-01 00:00:00' AND '2024-01-12 00:00:00'

GROUP BY dt

) prices