Somehow I ended up overwriting the solution for day 9 with the solution to day 7! I'm not sure how that happened.
My apologies!
I had an entire explanation written up, but for now at least here is the query that solves day 9:
WITH streak_groups AS ( SELECT s2.*, sum(price_fell) OVER ( ORDER BY day ) AS streak_group FROM ( SELECT s1.*, CASE WHEN s1.close > s1.prior_close THEN 0 ELSE 1 END AS price_fell FROM ( SELECT day9.stockprice.*, lag(close, 1) OVER ( ORDER BY day ) AS prior_close FROM day9.stockprice ) AS s1 ) AS s2 ) SELECT count(*) AS consecutive_days, min(close) AS min_close, max(close) AS max_close, min(day) AS start_date, max(day) AS end_date FROM streak_groups GROUP BY streak_group ORDER BY consecutive_days DESC;
Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment's permalink.
Hide child comments as well
Confirm
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
Somehow I ended up overwriting the solution for day 9 with the solution to day 7! I'm not sure how that happened.
My apologies!
I had an entire explanation written up, but for now at least here is the query that solves day 9: