title: Rolling Window Over Latest Values For Each ID in Pyspark
body: <p>I have a data set where transactions can be updated over time, each time a transaction is updated a new record is added with a timestamp of when it was changed and a timestamp of when it will be effective until. The old row is then updated with an effective end date which says when that row became outdated. I need to create a rolling window that sums up only the latest versions of every transaction as it would have been on that given date over the last 30 days. The data looks something like this:</p>
  <p><a href="https://i.stack.imgur.com/zq4O3.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/zq4O3.png" alt="Excel version of the desired outcome" /></a></p>
  <p>The column <code>desired outcome</code> is what I'm trying to achieve using pyspark window. I've underlined each date to hopefully make it easier to read.</p>
  <p>For example on 9/17/2021 the transaction id <code>795342800</code> had a value of <code>0.357...</code> and so the sum of transactions was <code>1.779...</code> then on 9/18/2021, the value for transaction <code>795342800</code> changed to <code>0.155...</code> and so the sum decreased to <code>1.578...</code>. I'm trying to write this in pyspark using windows, but I'm not sure how to achieve it. I thought I could do it this way</p>
  <pre><code>  SECONDS_IN_A_DAY = 60 * 60 * 24
  
    df.withColumn(&quot;sum_transactions_past_30_days_window&quot;,
            sum(
              first(&quot;value&quot;)
              .over(
                Window.partitionBy(&quot;transaction_id&quot;)
                .orderBy(col(&quot;md_eff_end_date&quot;).cast('timestamp').cast(&quot;long&quot;).desc()).rowsBetween(Window.unboundedPreceding, Window.unboundedFollowing)
              )
            ).over(Window.orderBy(col(&quot;as_of&quot;).cast('timestamp').cast(&quot;long&quot;).asc()).rangeBetween(SECONDS_IN_A_DAY * -30, 0))
           )
  </code></pre>
  <p>but the problem with this is that the outer window is summing up all transactions between those dates and not just the .first() of every transaction as it was on that day which is what I thought I was doing.</p>