Leetcode: report-contiguous-dates

leetcode-Report-Contiguous-Dates.sql

-- Problem description found at https://code.dennyzhang.com/report-contiguous-dates
-- Schema and test data:
-- CREATE TABLE Failed(fail_date DATE);
-- CREATE TABLE Succeeded(success_date DATE);
-- INSERT INTO Failed VALUES ('2018-12-28'), ('2018-12-29'), ('2019-01-04'), ('2019-01-05');
-- INSERT INTO Succeeded VALUES ('2018-12-30'),('2018-12-31'),('2019-01-01'),('2019-01-02'),('2019-01-03'),('2019-01-06');

WITH RECURSIVE 
  tasks(dt, state) AS 
        (SELECT success_date, 'succeeded' 
         FROM   Succeeded 
         WHERE  success_date BETWEEN '2019-01-01' AND '2019-12-31' 
         UNION ALL 
         SELECT fail_date, 'failed' 
         FROM   Failed 
         WHERE  fail_date BETWEEN '2019-01-01' AND '2019-12-31'), 
  uf(d, state, start_date, n) AS 
        (SELECT dt, state, dt, 0 
         FROM   tasks 
           UNION ALL 
         SELECT dt, tasks.state, start_date, n+1 
         FROM   tasks 
         JOIN   uf 
         ON     dt=d+1 AND tasks.state=uf.state ) 
  SELECT   uf.state AS period_state, 
           uf.start_date, 
           uf.start_date+max(n) AS end_date 
  FROM     uf 
  JOIN (SELECT   d, state,  max(n) n 
        FROM     uf 
        GROUP BY d, state) tt 
  USING    (d,state, n) 
  GROUP BY uf.state, uf.start_date 
  ORDER BY uf.start_date;