title: Calculating working days and holidays from (overlapping) date ranges in PySpark
body: <pre><code>from pyspark.sql import SparkSession
  from pyspark.sql import functions as F
  
  # Create PySpark dataframe
  columns = [&quot;user&quot;,&quot;hiring_date&quot;,&quot;termination_date&quot;]
  data = [(&quot;A&quot;, &quot;1995-09-08&quot;, &quot;1997-09-09&quot;), (&quot;A&quot;, &quot;2003-05-08&quot;, &quot;2006-11-09&quot;),
          (&quot;A&quot;, &quot;2000-05-06&quot;, &quot;2003-05-09&quot;), (&quot;B&quot;, &quot;2007-06-27&quot;, &quot;2008-05-27&quot;),
          (&quot;C&quot;, &quot;2003-01-20&quot;, &quot;2006-01-19&quot;), (&quot;C&quot;, &quot;2011-04-03&quot;, &quot;2011-04-04&quot;)]
  
  spark = SparkSession.builder.appName('SparkByExamples.com').getOrCreate()
  rdd = spark.sparkContext.parallelize(data)
  
  df = spark \
      .createDataFrame(rdd) \
      .toDF(*columns) \
      .withColumn('hiring_date', F.expr('CAST(hiring_date AS DATE)')) \
      .withColumn('termination_date', F.expr('CAST(termination_date AS DATE)'))
      
  df.show()
  
  +----+-----------+----------------+
  |user|hiring_date|termination_date|
  +----+-----------+----------------+
  |   A| 1995-09-08|      1997-09-09|
  |   A| 2003-05-08|      2006-11-09|
  |   A| 2000-05-06|      2003-05-09|
  |   B| 2007-06-27|      2008-05-27|
  |   C| 2003-01-20|      2006-01-19|
  |   C| 2011-04-03|      2011-04-04|
  +----+-----------+----------------+
  </code></pre>
  <p>In the above example, I have multiple users with a start date <code>hiring_date</code> and an end date <code>termination_date</code>. Per user, there can be single as well as multiple rows. In addition, users can have multiple jobs at the same time (overlapping termination and hiring dates).</p>
  <p>For each user, I need to calculate the following:</p>
  <ul>
  <li>The number of days the user was working. Overlapping dates should <strong>not</strong> be counted multiple times.</li>
  <li>The number of days the user was not working (i.e., was on vacation).</li>
  </ul>