-- Get the average price for each day in September

SELECT

DATE(HOUR) AS day,

AVG(PRICE) AS avg_price

FROM base.price.ez_prices_hourly

WHERE

MONTH(HOUR) = 9

AND YEAR(HOUR) = YEAR(CURRENT_DATE())

GROUP BY DATE(HOUR)

),

september_transactions AS (

-- Calculate the number of transactions per day in September

SELECT

DATE(BLOCK_TIMESTAMP) AS day,

COUNT(*) AS transaction_count

FROM base.core.fact_transactions

WHERE

MONTH(BLOCK_TIMESTAMP) = 9

AND YEAR(BLOCK_TIMESTAMP) = YEAR(CURRENT_DATE())

GROUP BY DATE(BLOCK_TIMESTAMP)

SELECT

sp.day,

sp.avg_price,

st.transaction_count

FROM september_prices sp

JOIN september_transactions st

ON sp.day = st.day

ORDER BY sp.avg_price ASC;