title: 'LAG' function not working in Amazon Redshift
body: <p>I'm trying to find out the <code>retention rate</code> by using the following query on Amazon Redshift:</p>
  
  <pre><code>    WITH t AS (
  SELECT ga.ownerid,
         DATE_PART('month',ga.creationtime) AS month,
         COUNT(*) AS item_transactions,
         LAG(DATE_PART('month',ga.creationtime)) OVER (PARTITION BY ownerid ORDER BY DATE_PART('month',ga.creationtime)) = DATE_PART('month',ga.creationtime) -interval '1 month' OR NULL AS repeat_transaction
  FROM flx2.groupactivities ga
    JOIN auth.members m ON ga.ownerid = m.id
  WHERE ga.activitytype = 'assign'
  AND   ga.groupid NOT IN (SELECT groupid
                           FROM (SELECT groupid,
                                        COUNT(DISTINCT memberid)
                                 FROM flx2.grouphasmembers
                                 GROUP BY groupid
                                 HAVING COUNT(DISTINCT memberid) = 1))
  AND   ga.ownerid IN (SELECT memberid FROM auth.memberhasroles WHERE roleid = 5)
  AND   ga.ownerid NOT IN (SELECT memberid FROM auth.memberhasroles WHERE roleid = 25)
  GROUP BY ga.ownerid,
           DATE_TRUNC('month',ga.creationtime)
  ORDER BY ga.ownerid,
           DATE_TRUNC('month',ga.creationtime)
     )
  SELECT month
        ,sum(item_transactions) AS num_trans
        ,count(*) AS num_buyers
        ,count(repeat_transaction) AS repeat_buyers
        ,round(
            CASE WHEN sum(item_transactions) &gt; 0
               THEN count(repeat_transaction) / sum(item_transactions) * 100
               ELSE 0
            END, 2) AS buyer_retention
  FROM   t
  GROUP  BY 1
  ORDER  BY 1;
  </code></pre>
  
  <p>but it gives me the following error:</p>
  
  <pre><code>An error occurred when executing the SQL command:
  WITH t AS (
  SELECT ga.ownerid,
         DATE_TRUNC('month',ga.creationtime) AS month,
         COUNT(*) AS item_transactions,
         LAG(DATE_TRUNC('month...
  
  [Amazon](500310) Invalid operation: ORDER/GROUP BY expression not found in targetlist;
  
  Execution time: 0.29s
  1 statement failed.
  </code></pre>
  
  <p>I believe there's something wrong with the <code>LAG</code> function here, but I'm not quite sure. I got this query from the post <a href="https://stackoverflow.com/questions/16492842/how-to-calculate-retention-month-over-month-using-sql">here</a> and I modified it according to my requirements. </p>
  
  <p>Would someone please be able to help me out with what's going wrong here?</p>
  
  <p>I appreciate the help in advance.</p>