title: Spark DataFrame add column from subquery
body: <p>using SQL syntax I can add new column using subquery like that:</p>
  <pre><code>import spark.sqlContext.implicits._
  
       List(
        (&quot;a&quot;, &quot;1&quot;, &quot;2&quot;),
        (&quot;b&quot;, &quot;1&quot;, &quot;3&quot;),
        (&quot;c&quot;, &quot;1&quot;, &quot;4&quot;),
        (&quot;d&quot;, &quot;1&quot;, &quot;5&quot;)
      ).toDF(&quot;name&quot;, &quot;start&quot;, &quot;end&quot;)
          .createOrReplaceTempView(&quot;base&quot;)
  
      List(
        (&quot;a&quot;, &quot;1&quot;, &quot;2&quot;),
        (&quot;b&quot;, &quot;2&quot;, &quot;3&quot;),
        (&quot;c&quot;, &quot;3&quot;, &quot;4&quot;),
        (&quot;d&quot;, &quot;4&quot;, &quot;5&quot;),
        (&quot;f&quot;, &quot;5&quot;, &quot;6&quot;)
      ).toDF(&quot;name&quot;, &quot;number&quot;, &quot;_count&quot;)
        .createOrReplaceTempView(&quot;col&quot;)
  
  
     spark.sql(
       &quot;&quot;&quot;
         |select a.name,
         |       (select Max(_count) from col b where b.number == a.end) - (select Max(_count) from col b where b.number == a.start) as result
         |from base a
         |&quot;&quot;&quot;.stripMargin)
        .show(false)
  </code></pre>
  <p>How I can do that with DataFrame API?</p>