SELECT

'Thursday Night Challenge - Sept 8' as challenge_name,

'https://nflallday.com/challenges/6142f89c-dc23-4b64-9cf7-b08c03293597' as challenge_link,

'9/8/2022 23:30:00' as start_time,

'9/9/2022 23:30:00' as end_time,

'2022 NFL Kickoff is here, and it’s time for the first Challenge of the 2022 NFL regular season! For this Challenge, we’ll be focusing on the pass-catchers with the most yards receiving during the 2022 NFL Kickoff game. You will also need a defensive player from either team to complete your Challenge Entry. Fans will need four total Moments:' as challenge_text,

4 as moments_required

FROM flow.core.dim_allday_metadata

), tab2 as (

SELECT

-- CASE WHEN '9/9/2022 23:30:00' > block_timestamp THEN 'Post Contest End' ELSE 'Pre Contest End' END,

date_trunc('hour', block_timestamp) as hour1,

count(*) as sales,

sum(price) as volume,

avg(price) as average_price

FROM flow.core.ez_nft_sales

LEFT outer JOIN flow.core.dim_allday_metadata

ON flow.core.dim_allday_metadata.nft_id = flow.core.ez_nft_sales.nft_id

-- LEFT outer JOIN tab1

WHERE flow.core.ez_nft_sales.nft_collection LIKE '%AllDay'

AND block_timestamp between dateadd(day, -1, '9/29/2022 23:30:00') AND dateadd(day, 1, '9/30/2022 23:30:00')

AND ( player IN ('Joe Burrow', 'Tyreek Hill', 'Joe Mixon')

OR team LIKE '%Bengals%')

group BY 1

SELECT

CASE WHEN hour1 < '9/30/2022 23:30:00' THEN 'Pre Contest End' ELSE 'Post Contest End' END as time_period,

avg(sales),

avg(volume),

median(average_price)

FROM tab2

GROUP BY 1