title: Spark window function with condition on current row
body: <p>I am trying to count for a given <code>order_id</code> how many orders there were in the past 365 days which had a payment. And this is not the problem: I use the <a href="https://databricks.com/blog/2015/07/15/introducing-window-functions-in-spark-sql.html" rel="noreferrer">window function</a>.</p>
  
  <p>Where it gets tricky for me is: I don't want to count orders in this time window where the <code>payment_date</code> is after <code>order_date</code> of the current <code>order_id</code>.</p>
  
  <p>Currently, I have something like this:</p>
  
  <pre><code>val window: WindowSpec = Window
    .partitionBy("customer_id")
    .orderBy("order_date")
    .rangeBetween(-365*days, -1)
  </code></pre>
  
  <p>and </p>
  
  <pre><code>df.withColumn("paid_order_count", count("*") over window)
  </code></pre>
  
  <p>which would count all orders for the customer within the last 365 days before his current order. </p>
  
  <p>How can I now incorporate a condition for the counting that takes the  <code>order_date</code> of the current order into account?</p>
  
  <p>Example:</p>
  
  <pre><code>+---------+-----------+-------------+------------+
  |order_id |order_date |payment_date |customer_id |
  +---------+-----------+-------------+------------+
  |1        |2017-01-01 |2017-01-10   |A           |
  |2        |2017-02-01 |2017-02-10   |A           |
  |3        |2017-02-02 |2017-02-20   |A           |
  </code></pre>
  
  <p>The resulting table should look like this:</p>
  
  <pre><code>+---------+-----------+-------------+------------+-----------------+
  |order_id |order_date |payment_date |customer_id |paid_order_count |
  +---------+-----------+-------------+------------+-----------------+
  |1        |2017-01-01 |2017-01-10   |A           |0                |
  |2        |2017-02-01 |2017-02-10   |A           |1                |
  |3        |2017-02-02 |2017-02-20   |A           |1                |
  </code></pre>
  
  <p>For <code>order_id = 3</code> the <code>paid_order_count</code> should not be <code>2</code> but <code>1</code> as <code>order_id = 2</code> is paid after <code>order_id = 3</code> is placed.</p>
  
  <p>I hope that I explained my problem well and look forward to your ideas!</p>