title: Filter rows in PostgreSQL based on values of consecutive rows in one column
body: <p>So I'm working with the following postgresql table:</p>
  
  <p>10 rows from PostGreSQL table
  <a href="https://i.stack.imgur.com/biKIW.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/biKIW.png" alt="enter image description here"></a></p>
  
  <p>For each business_id, I want to filter out those businesses where the review_count isn't above a specific review_count threshold for <strong>2 consecutive months (or rows)</strong>. Depending on the city the business_id is in, the threshold will be different (so for example, in the screenshot above, we can assume rows with city = Charlotte has a review_count threshold of >= 2, and those with city = Las Vegas has a review_count threshold of >= 3. <strong>If a business_id does not have at least one instance of consecutive months with review_counts above the specified threshold, I want to filter it out.</strong> </p>
  
  <p>I want this query to return only the business_ids that meet this condition (as well as all the other columns in the table that go along with that business_id). The composite primary key on this table is (business_id, year, month).</p>
  
  <p>Some months, as you may notice, are missing from the data (month 9 of the second business_id). If that is the case, I do NOT want to count 2 rows as 'consecutive months'. For example, for the business in Las Vegas, I do NOT want to consider month 8 to 10 as 'consecutive months', even though they appear in consecutive rows.</p>
  
  <p>I've tried something like this, but have kind of run into a wall and don't think its getting me far:</p>
  
  <pre><code>SELECT *
  FROM us_business_monthly_review_growth
  WHERE business_id IN (SELECT DISTINCT(business_id)
                        FROM us_business_monthly_review_growth
                        GROUP BY business_id, year, month
                        HAVING (city = 'Las Vegas' 
                               AND (CASE WHEN COUNT(review_count &gt;= 2 * 2.21) &gt;= 2))
                               OR (city = 'Charlotte' AND (CASE WHEN COUNT(review_count &gt;= 2 * 1.95) &gt;= 2))
  </code></pre>
  
  <p>I'm new to Postgre and StackOverflow, so if you have any feedback on the way I asked this question please don't hesitate to let me know! =)</p>
  
  <p><strong>UPDATE</strong>:</p>
  
  <p>Thanks to some help from @<a href="https://stackoverflow.com/users/1144035/gordon-linoff">Gordon Linoff</a>, I found the following solution:</p>
  
  <pre><code>SELECT *
  FROM us_businesses_monthly_growth_and_avg
  WHERE business_id IN (SELECT distinct(business_id)
  FROM (SELECT *,
               lag(year) OVER (PARTITION BY business_id ORDER BY year, month) AS prev_year,
               lag(month) OVER (PARTITION BY business_id ORDER BY year, month) AS prev_month,
               lag(review_count) OVER (PARTITION BY business_id ORDER BY year, month) AS prev_review_count
        FROM us_businesses_monthly_growth_and_avg 
       ) AS usga
  WHERE (city = 'Charlotte' AND review_count &gt;= 4 * 1.95 AND prev_review_count &gt;= 4 * 1.95 AND (YEAR * 12 + month) = (prev_year * 12 + prev_month) + 1)
          OR (city = 'Las Vegas' AND review_count &gt;= 4 * 3.31 AND prev_review_count &gt;= 4 * 3.31 AND (YEAR * 12 + month) = (prev_year * 12 + prev_month) + 1);
  </code></pre>