title: Get count of c for each b found in another table
body: <p>I have 2 tables.<br />
  table_1 consists of a, b (primary key for identifying the email sent), and s.<br />
  table_2 table consists of a, b (primary key for identifying the email clicked), and c.</p>
  <p>For each table_1.a, I want to get the count of table_2.c for each b of table_1 found in table_2. If b from table_1 is not found in table_2, then the count of c for that b is set to 0. Then, for each table_1.a, I want to order each b by s.<br />
  Example:</p>
  <pre><code>table_1 =                           
  +----------+----+----------+
  |         a|   b|         s|
  +----------+----+----------+
  |      1433| 42a|2021-02-01|
  +----------+----+----------+
  |      1523| 41c|2021-02-23|
  +----------+----+----------+
  |      1523| 42c|2021-02-24|
  +----------+----+----------+
  |      1523| 38a|2021-01-03|
  +----------+----+----------+
  |      1421| 39a|2021-02-28|
  +----------+----+----------+
  
  mclicks =
  +----------+----+----------+
  |         a|   b|         c|
  +----------+----+----------+
  |      1523| 41c|2021-02-24|
  +----------+----+----------+
  |      1523| 41c|2021-02-25|
  +----------+----+----------+
  |      1523| 42c|2021-02-27|
  +----------+----+----------+
  |      1421| 39a|2021-02-28|
  +----------+----+----------+
  </code></pre>
  <p>I would like my final output to be:</p>
  <pre><code>+----+----+-----+
  |  a1|   b|    c|
  +----+----+-----+
  |1433| 42a|    0|
  +----+----+-----+
  |1523| 38a|    0|
  +----+----+-----+
  |1523| 41c|    2|
  +----+----+-----+
  |1523| 42c|    1|
  +----+----+-----+
  </code></pre>
  <p>This is my code:</p>
  <pre><code>df = table_1.join(table_2, table_1.a == table_2.a, &quot;left&quot;) \
              .select(table_1.a.alias('a1'), table_1.s.alias('s1'), \
                      table_1.b.alias('b1'), table_2.c('c2')) \
              .filter((table_1.s).between('2021-01-31', '2021-03-01'))
  
  df1 = df.groupBy(df['a1'], df['b1'], df['s1']) \
          .agg(count(df['c2']).alias('df_cd'))
  
  w = Window.partitionBy(df1['a1'], df1['b1']) \
            .orderBy(df1['s1'])
  
  df2 = df1.withColumn('row', row_number().over(w))
  </code></pre>