title: Finding non-overridden date range from a list of non-ending date ranges in Postgres
body: <p>I've got a table to store price of an <code>sku</code> starting from a given date: <code>start_date</code>. There could be multiple entry made in this table for new price for the <code>sku</code>, with new <code>start_date</code>. Each new entry overrides the prices from previous set of rows from the date where this new price is added from.</p>
  <p>Table structure is like this:</p>
  <pre><code>sku_id | start_date   | price
  
  100    | &quot;2020-01-10&quot; | 100
  100    | &quot;2020-01-20&quot; | 200
  100    | &quot;2020-01-30&quot; | 300
  </code></pre>
  <p>With these entries, the prices for <code>10th Jan</code>, <code>21st Jan</code>, and <code>31st Jan</code> will come out as <code>100</code>, <code>200</code> and <code>300</code> respectively.</p>
  <p>Now, if we make another entry in this table, with start date as <code>1st Jan</code> and price of <code>500</code>. Then that till override all these 3 prices. And then price for all 3 dates fetched earlier will now come as <code>500</code>.</p>
  <pre><code>sku_id | start_date   | price
  
  100    | &quot;2020-01-10&quot; | 100
  100    | &quot;2020-01-20&quot; | 200
  100    | &quot;2020-01-30&quot; | 300
  100    | &quot;2020-01-01&quot; | 500    -&gt; This row overrides prices of all 3 rows before it. Since start date `2020-01-01` is less then all their start date.
  </code></pre>
  <p>Given this table structure, here's my requirement:</p>
  <p>I want to fetch all the records which are still active (meaning, they are not completely overridden by any of the newly created rows). I thought of using LEAD function, which can get me end_date of each row.</p>
  <pre><code>SELECT sku_id, start_date, price,
      LEAD(start_date, 1) OVER (
          PARTITION BY sku_id ORDER BY created_at
      ) - INTERVAL '1 day' AS end_date
  FROM rate;
  </code></pre>
  <p>This query gives me this result:</p>
  <pre><code>sku_id | start_date   | price  | end_date
  
  100    | &quot;2020-01-10&quot; | 100    | &quot;2020-01-19&quot;
  100    | &quot;2020-01-20&quot; | 200    | &quot;2020-01-29&quot;
  100    | &quot;2020-01-30&quot; | 300    | &quot;2019-12-31&quot;
  100    | &quot;2020-01-01&quot; | 500    | 
  </code></pre>
  <p>After this what I need is some query, which can reject row 1, 2, and 3, since their start date is less than end date of 3rd row. This is what I'm unable to understand how to achieve.</p>
  <p>Really sorry if the title of the question doesn't make sense, because I couldn't come up with what question title to give.</p>
  <p>Another approach that I've is to keep loading record in descending order. And then using that record as cursor, fetch previous rows with start_date less than this record's start date. But that will result in lot of round trip to database, which I want to avoid.</p>
  <p>Will be great if there is a solution with one single SQL query to get what I'm looking for.</p>