title: Count the retweets using SQL statement only
body: <p>I've tweets in a set of JSON files and I need to count the retweets for each user.
  I know that there is an attribute called &quot;retweet_count&quot; but its removed form the files for educational purposes:). So, I can use only SQL statement.</p>
  <p>First, Its requested to register a table from all JSON files &quot;tweets_sample&quot; as below:</p>
  <pre><code>tweets_sample = sqlContext.read.json(&quot;/tweets28a_samples&quot;)
  sqlContext.registerDataFrameAsTable(tweets_sample,&quot;tweets_sample&quot;)
  </code></pre>
  <p>then create a table for only 4 columns as follow:</p>
  <pre><code>users_agg = sqlContext.sql(&quot;SELECT user.screen_name, MAX(user.friends_count) AS friends_count, count(_id) AS tweets, MAX(user.followers_count) AS followers_count FROM tweets_sample WHERE user.lang='en' GROUP BY user.screen_name ORDER BY tweets DESC&quot;)
  Result:
  ---------------+-------------+------+---------------+
  |    screen_name|friends_count|tweets|followers_count|
  +---------------+-------------+------+---------------+
  |       anaoromi|         6258|    16|           6774|
  |    RosaMar6254|         6208|    14|           6245|
  </code></pre>
  <p>then created another table which will be the INNER JOIN for both two tables as follow:</p>
  <pre><code>retweeted = sqlContext.sql(&quot;SELECT user.screen_name, tweets, friends_count, followers_count, count(retweeted_status) AS retweeted FROM tweets_sample AS A INNER JOIN user_agg AS B ON A.user.screen_name = B.screen_name AND A.user.friends_count = B.friends_count and A.user.followers_count = B.followers_count WHERE user.screen_name='PSOE' GROUP BY user.screen_name, tweets, friends_count, followers_count &quot;)
  </code></pre>
  <p>but here the retweet comes with null value.
  I tried many ideas and SQL statements but with no luck.
  So, any one can help me with that?</p>
  <p>Below the schema structure:</p>
  <pre><code>root
   |-- _id: string (nullable = true)
   |-- created_at: long (nullable = true)
   |-- lang: string (nullable = true)
   |-- place: struct (nullable = true)
   |    |-- bounding_box: struct (nullable = true)
   |    |    |-- coordinates: array (nullable = true)
   |    |    |    |-- element: array (containsNull = true)
   |    |    |    |    |-- element: array (containsNull = true)
   |    |    |    |    |    |-- element: double (containsNull = true)
   |    |    |-- type: string (nullable = true)
   |    |-- country_code: string (nullable = true)
   |    |-- id: string (nullable = true)
   |    |-- name: string (nullable = true)
   |    |-- place_type: string (nullable = true)
   |-- retweeted_status: struct (nullable = true)
   |    |-- _id: string (nullable = true)
   |    |-- user: struct (nullable = true)
   |    |    |-- followers_count: long (nullable = true)
   |    |    |-- friends_count: long (nullable = true)
   |    |    |-- id_str: string (nullable = true)
   |    |    |-- lang: string (nullable = true)
   |    |    |-- screen_name: string (nullable = true)
   |    |    |-- statuses_count: long (nullable = true)
   |-- text: string (nullable = true)
   |-- user: struct (nullable = true)
   |    |-- followers_count: long (nullable = true)
   |    |-- friends_count: long (nullable = true)
   |    |-- id_str: string (nullable = true)
   |    |-- lang: string (nullable = true)
   |    |-- screen_name: string (nullable = true)
   |    |-- statuses_count: long (nullable = true)
  </code></pre>
  <p>Thanks</p>