SELECT

date_trunc('hour', block_timestamp) as h1,

--marketplace,

count(DISTINCT tx_id) as sales,

sum(SALES_AMOUNT) as sales_volume,

count(DISTINCT PURCHASER) as buyers,

min(SALES_AMOUNT) as min_price

from solana.core.fact_nft_sales

LEFT outer JOIN solana.core.dim_nft_metadata

on solana.core.fact_nft_sales.mint = solana.core.dim_nft_metadata.mint

WHERE contract_name LIKE 'DeGods'

GROUP BY 1

), tab2 as (

SELECT

date_trunc('day', h1) as day,

median(min_price) as floor_sol

FROM tab1

GROUP BY 1

), tab3 as (

SELECT

tab2.day as day,

floor_sol,

floor_sol / sol_to_eth_ratio as floor_eth

FROM tab2

LEFT OUTER JOIN (

SELECT

date_trunc('day', RECORDED_HOUR) as day,

avg(CASE WHEN symbol LIKE 'WETH' THEN close END) as eth_price,

avg(CASE WHEN symbol LIKE 'SOL' THEN close END) as sol_price,

avg(CASE WHEN symbol LIKE 'WETH' THEN close END) / avg(CASE WHEN symbol LIKE 'SOL' THEN close END) as sol_to_eth_ratio

FROM solana.core.fact_token_prices_hourly

GROUP BY 1

) as a

ON tab2.day = a.day