title: Oracle sql query to group consecutive records by date
body: <p>With the below sample data, I am trying to group record with same rate.</p>
  
  <pre><code>   id   start_date              end_date                rate
    -----------------------------------------------------------------
  
      1   01/01/2017 12:00:00 am  01/01/2017 12:00:00 am  300
      1   02/01/2017 12:00:00 am  02/01/2017 12:00:00 am  300
      1   03/01/2017 12:00:00 am  03/01/2017 12:00:00 am  300
      1   04/01/2017 12:00:00 am  04/01/2017 12:00:00 am  1000
      1   05/01/2017 12:00:00 am  05/01/2017 12:00:00 am  500
      1   06/01/2017 12:00:00 am  06/01/2017 12:00:00 am  500
      1   07/01/2017 12:00:00 am  07/01/2017 12:00:00 am  1000
      1   08/01/2017 12:00:00 am  08/01/2017 12:00:00 am  1000
      1   09/01/2017 12:00:00 am  09/01/2017 12:00:00 am  300
  </code></pre>
  
  <p>What I've tried :</p>
  
  <pre><code>select distinct id, mn_date, mx_date,rate
  from (
      select id, min(start_date) over (partition by grp order by start_date) mn_date,
      max(end_date) over(partition by grp order by start_date desc) mx_date, rate
      from (
      select t.*, row_number() over(partition by id order by start_date) -row_number() over(partition by rate order by start_date)grp
      from t
          )
      )
  order by mn_date;
  </code></pre>
  
  <p>Output :</p>
  
  <pre><code>id  mn_date                 mx_date                 rate
  --------------------------------------------------------
  1   01/01/2017 12:00:00 am  03/01/2017 12:00:00 am  300
  1   04/01/2017 12:00:00 am  04/01/2017 12:00:00 am  1000
  1   05/01/2017 12:00:00 am  06/01/2017 12:00:00 am  500
  1   07/01/2017 12:00:00 am  09/01/2017 12:00:00 am  300
  1   07/01/2017 12:00:00 am  09/01/2017 12:00:00 am  1000
  </code></pre>
  
  <p>Desired Output:</p>
  
  <pre><code>id  mn_date                 mx_date                 rate
  --------------------------------------------------------
  1   01/01/2017 12:00:00 am  03/01/2017 12:00:00 am 300
  1   04/01/2017 12:00:00 am  04/01/2017 12:00:00 am 1000
  1   05/01/2017 12:00:00 am  06/01/2017 12:00:00 am 500
  1   07/01/2017 12:00:00 am  08/01/2017 12:00:00 am 1000
  1   09/01/2017 12:00:00 am  09/01/2017 12:00:00 am 300
  </code></pre>
  
  <p>Final result to group by consecutive dates:  (Thanks to Gordon )</p>
  
  <pre><code>select id, min(start_date), max(end_date), rate
  from (
  select id, start_date, end_date, rate, seqnum_i-seqnum_ir grp, sum(x) over(partition by id order by start_date) grp1
  from (
  select t.*,
               row_number() over (partition by id order by start_date) as seqnum_i,
               row_number() over (partition by id, rate order by start_date) as seqnum_ir,
               case when LEAD(start_date) over (partition by id order by start_date)= end_date + 1 
               then 0 
               else 1
               end x
  from t
  )
  )
  group by id, grp+grp1, rate
  order by min(start_date);
  </code></pre>