WITH t0 AS (

SELECT id

, recorded_hour::date AS date

, AVG(close) AS token_price

FROM solana.core.fact_token_prices_hourly p

WHERE recorded_hour >= '2023-01-09'

AND id IN ('bonk','solana')

AND provider = 'coingecko'

GROUP BY 1, 2

), t1 AS (

SELECT mint

, sales_amount

, sales_amount * token_price AS sale_price_usd

, ROW_NUMBER() OVER (PARTITION BY mint ORDER BY block_timestamp) AS rn

FROM solana.core.fact_nft_sales s

JOIN solana.core.dim_labels l

ON l.address = s.mint

JOIN t0

ON t0.date = DATE_TRUNC('day', s.block_timestamp)

AND t0.id = 'solana'

WHERE s.block_timestamp >= '2023-01-10'

AND s.succeeded

AND l.label = 'bonkz'

), t2 AS (

SELECT t.block_timestamp

, t.signers[0]::string AS address

, m.mint

, COALESCE(

inner_instructions[0]:instructions[0]:parsed:info:amount::int

, inner_instructions[0]:instructions[1]:parsed:info:amount::int

, 0

) * POWER(10, -5) AS bonk_amt

, CASE

WHEN t.block_timestamp <= '2023-01-10 18:00:00' THEN '1: Gods WL'

WHEN t.block_timestamp <= '2023-01-10 19:00:00' THEN '2: Private WL'