-- Generate all dates for September

SELECT

TO_DATE('2023-09-01') + SEQUENCE AS day

FROM

TABLE(GENERATOR(ROWCOUNT => 30)) -- Generates 30 rows, one for each day

SELECT

sd.day, -- Day from generated date sequence

AVG(p.PRICE) AS avg_price -- Average price from price data, if available

FROM september_dates sd

LEFT JOIN base.price.ez_prices_hourly p

ON DATE(p.HOUR) = sd.day -- Join generated dates with price data

WHERE

MONTH(sd.day) = 9 -- Ensure it's for September

AND YEAR(sd.day) = YEAR(CURRENT_DATE()) -- Ensure it's for the current year

GROUP BY sd.day -- Group by day

ORDER BY sd.day ASC; -- Order by day to observe trend