title: Pandas: 30 Day retention for customers who made a new purchase
body: <p>I have a dataframe of Transaction Data containing these columns since the beginning of 2017</p>
  <pre><code>Order_nr     Order_item_number         Amount     Purchase_date      Customer_ID
     20045              12365              20        2021-08-08             5564
     20045              12789              50        2021-08-08             5564
     20045              14789              44        2021-08-08             5564
     20046              19635              63        2021-08-09             7854
     20047              77895              78        2021-08-10             3699
     20047              78456              78        2021-08-10             3699
  </code></pre>
  <p>There are more than 40 million rows in the data.</p>
  <p>I want to find the 30 Day retention rate of the customers who make new purchases each day, i.e.
  let's say 2000 new customers made their first purchase on 2017-07-30, or we can choose any other date, how many of them made a repeat purchase in the next 30 days.</p>
  <p>ResultSet :</p>
  <pre><code>Date      No_of_first_time_buyers    Repeat_buyers_in_+30_days       
  2017-01-01        1000                        500
  2017-01-02        1500                        300
  2017-01-03        1000                        600
  </code></pre>
  <p>I tried something in SQL couldn't get there</p>
  <pre><code>    with fo as (
      select 
      fk_customer,
      created_at,
      LEAD(created_at) OVER (PARTITION BY fk_customer ORDER BY created_at) as next_order_date,
      ROW_NUMBER() OVER (PARTITION BY fk_customer ORDER BY created_at) as seqnum
      from `xxx-xxx.sales_order`
      WHERE Extract(date from created_at) &gt;= '2019-01-04'
  )
  
  select * from fo 
  where Extract(date from created_at) = '2019-01-04'
  and seqnum =  1
  and DATE_DIFF(created_at, next_order_date, day) &lt;= 30
  </code></pre>
  <p>If you can please help.</p>