Discussion on: #SQL30 Day 10: Random Sampling

[zchtodd]

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;