straight.sql

WITH
  Input(date, star) AS (
    SELECT '2013-04-01', 0
    UNION ALL SELECT '2013-04-03', 1
    UNION ALL SELECT '2013-04-04', 1
    UNION ALL SELECT '2013-04-05', 0
    UNION ALL SELECT '2013-04-06', 1
    UNION ALL SELECT '2013-04-07', 1
    UNION ALL SELECT '2013-04-08', 1
    UNION ALL SELECT '2013-04-10', 1
    UNION ALL SELECT '2013-04-11', 1
    UNION ALL SELECT '2013-04-12', 1
    UNION ALL SELECT '2013-04-14', 0
    UNION ALL SELECT '2013-04-15', 0
    UNION ALL SELECT '2013-04-17', 0
    UNION ALL SELECT '2013-04-18', 1
    UNION ALL SELECT '2013-04-19', 1
    UNION ALL SELECT '2013-04-20', 0
  )
  , Input_ AS (
    SELECT
      RANK() OVER(ORDER BY date) AS id
      , date
      , star
    FROM
      Input
  )
  , BeginSet AS (
    SELECT
      ROW_NUMBER() OVER (ORDER BY I1.id) as set_id
      , I1.id AS row_id
      , I1.star
    FROM
      Input_ I1
      LEFT JOIN Input_ I2 ON
        I1.Id - 1 = I2.Id
    WHERE
      I2.star IS NULL
      OR I1.star <> I2.star
  )
  , EndSet AS (
    SELECT
      ROW_NUMBER() OVER (ORDER BY I1.id) as set_id
      , I1.id AS row_id
      , I1.date
      , I1.star
    FROM
      Input_ I1
      LEFT JOIN Input_ I2 ON
        I1.Id + 1 = I2.Id
    WHERE
      I2.star IS NULL
      OR I1.star <> I2.star
  )
  , Straight AS (
    SELECT
      BeginSet.set_id
      , BeginSet.row_id AS begin_id
      , EndSet.row_id AS end_id
      , BeginSet.star AS star
    FROM
      BeginSet
      INNER JOIN EndSet ON
        BeginSet.set_id = EndSet.set_id
  )
SELECT
  star
  , MAX(end_id - begin_id) + 1
FROM
  Straight
GROUP BY
  star