title: How can I increment the numerical value in my WHERE clause using a loop?
body: <p>I am currently using the <strong>UNION ALL</strong> workaround below to calculate <strong>old_eps_tfq</strong> regression slopes of each <strong>ticker</strong> based off its corresponding <strong>rownum</strong> value (see <code>WHERE rownum &lt; x</code>). I am interested to know what the <strong>old_eps_tfq</strong> is when <code>rownum &lt; 4</code> then increment <strong>4</strong> by <strong>1</strong> to find out what <strong>old_eps_tfq</strong> is when <code>rownum &lt; 5</code>, and so on (there are ~20 rownum)</p>
  <p>Could I use PL/pgSQL for this?</p>
  <pre><code>SELECT * FROM(
      WITH regression_slope AS(
          SELECT
              ROW_NUMBER() OVER ( PARTITION BY ticker ORDER BY earnings_growths_ped) AS rownum,
              *
          FROM &quot;ANALYTICS&quot;.&quot;vEARNINGS_GROWTHS&quot;
          --WHERE ticker = 'ACN' 
          ORDER BY ticker )   
      SELECT
          ticker,
          current_period_end_date,
          max(earnings_growths_ped) AS max_earnings_growths_ped, 
          --max(rownum) AS max_rownum, 
          round(regr_slope(old_eps_tfq, rownum)::numeric, 2) AS slope,
          round(regr_intercept(old_eps_tfq, rownum)::numeric, 2) AS y_intercept,
          round(regr_r2(old_eps_tfq, rownum)::numeric, 3) AS r_squared
      FROM regression_slope  
      WHERE rownum &lt; 4
      GROUP BY ticker, current_period_end_date
      ORDER BY ticker asc ) q
  
  UNION ALL 
  
  SELECT * FROM(
      WITH regression_slope AS(
          SELECT
              ROW_NUMBER() OVER ( PARTITION BY ticker ORDER BY earnings_growths_ped) AS rownum,
              *
          FROM &quot;ANALYTICS&quot;.&quot;vEARNINGS_GROWTHS&quot;
          --WHERE ticker = 'ACN' 
          ORDER BY ticker )   
      SELECT
          ticker,
          current_period_end_date,
          max(earnings_growths_ped) AS max_earnings_growths_ped, 
          --max(rownum) AS max_rownum, 
          round(regr_slope(old_eps_tfq, rownum)::numeric, 2) AS slope,
          round(regr_intercept(old_eps_tfq, rownum)::numeric, 2) AS y_intercept,
          round(regr_r2(old_eps_tfq, rownum)::numeric, 3) AS r_squared
      FROM regression_slope  
      WHERE rownum &lt; 5
      GROUP BY ticker, current_period_end_date
      ORDER BY ticker asc ) q
  </code></pre>
  <p><a href="https://i.stack.imgur.com/RrI7c.png" rel="nofollow noreferrer">Here is my table</a></p>