title: Joining data from 4 tables to calculate several weighted scores
body: <p>Please consider the following tables.</p>
  
  <p><code>users</code> holds a couple of thousands of Twitter-users; their <code>tweets</code> are indexed with <code>sp100_id</code>, which is the id of the company (see <code>sp100</code>) the tweet was talking about. <code>tweets.class</code> holds the assigned sentiment class (<code>1</code> = neutral, <code>2</code> = positive, <code>3</code> = negative) for each tweet. <code>tweets.rt</code> holds the amount of times the tweet has been retweeted. Finally, each user has been given a <code>quality</code> score and a <code>follow</code> score, as follows:</p>
  
  <pre><code>users                       tweets
  -------------------------   -----------------------------------------------
  user_id quality follow      tweet_id sp100_id nyse_date   user_id class  rt
  -------------------------   -----------------------------------------------
  1       2.50    5.00        1        1        2011-03-12  1       1      0
  2       0.75    1.00        2        1        2011-03-13  1       2      2
                              3        1        2011-03-13  1       2      1
  daterange                   4        1        2011-03-13  2       2      0
  ----------------            5        1        2011-03-13  2       3      3
  _date                       6        2        2011-03-12  2       2      3
  ----------------            7        2        2011-03-12  2       2      0
  2011-03-11                  8        2        2011-03-12  1       3      5
  2011-03-12                  9        2        2011-03-13  2       2      0
  2011-03-13
  
  sp100
  ----------------
  sp100_id  _name
  ----------------
  1         Alcoa
  2         Apple
  </code></pre>
  
  <p>The desired output is a list per <code>sp100_id</code> per <code>_date</code> the amount of positive (<code>class=2</code>) and negative (<code>class=3</code>) tweets weighted per <code>rt</code>, 'quality' and <code>follow</code>:</p>
  
  <pre><code>sp100_id  nyse_date  pos-rt pos-quality pos-follow neg-rt neg-quality neg-follow
  --------------------------------------------------------------------------------
  1         2011-03-11 0      0           0          0      0           0
  1         2011-03-12 0      0           0          0      0           0
  1         2011-03-13 5 (1)  5.75 (2)    11.00 (3)  3 (4)  0.75 (5)    1.00 (6)
  2         2011-03-11 0      0           0          0      0           0
  2         2011-03-12 3 (7)  5.00 (8)    10.00 (9)  5.00   2.50        2.50
  2         2011-03-13 0      0.75        1.00       0      0           0
  --------------------------------------------------------------------------------
  
  (1) On 2011-03-13, 3 positive tweets for sp100_id 1. 1 tweet retweeted 2 times,
      1 tweets retweeted 1 time and 1 tweet retweeted 0 times = 2x2+1x1+1x0 = 5
  (2) On 2011-03-13, 2 positive tweets made by user 1, who has quality 2.50 and
      1 positive tweet made by user 2, who has quality 0.75 = 2x2.50+1x0.75 = 5.75
  (3) On 2011-03-13, 2 positive tweets made by user 1, who has follow 5.00 and
      1 positive tweet made by user 2, who has follow 1 = 2x5.00+1x1.00 = 11.00
  (4) On 2011-03-13, 1 negative tweet made by user 2, retweeted 3 times = 1x3 = 3
  (5) On 2011-03-13, 1 negative tweet made by user 2, who has quality 0.75, thus
      1x0.75 = 0.75
  (6) On 2011-03-13, 1 negative tweets made by user 2, who has follow 1.00 so
      1x1.00 = 1.00
  (7) 1 positive tweet which has been retweeted 3 times, 1 positive tweet without
      any retweets = 1x3+1x0 = 3
  (8) 2 positive tweets from user 2 x quality 2.50 = 5.00
  (9) 2 positive tweets x follow 5 = 10.00
  </code></pre>
  
  <p>I've tried to explain myself as good as possible. Who can help me build the correct query? As you can see, also dates for which there are no tweets (all values zero), need to be included in the resultset. I now have this, but am having trouble finishing the rest:</p>
  
  <pre><code>SELECT
      s.sp100_id,
      d._date,
      COALESCE(c.pos-rt,0)      AS pos-rt,
      COALESCE(c.pos-quality,0) AS pos-quality,
      COALESCE(c.pos-follow,0)  AS pos-follow,
      COALESCE(c.neg-rt,0)      AS neg-rt,
      COALESCE(c.neg-quality,0) AS neg-quality,
      COALESCE(c.neg-follow,0)  AS neg-follow
  FROM sp100 s
  CROSS JOIN daterange d
  LEFT JOIN (
      SELECT 
          sp100_id,
          nyse_date, 
          COUNT(CASE class WHEN 2 THEN 1 END) * [rt]      AS pos-rt,
          COUNT(CASE class WHEN 2 THEN 1 END) * [quality] AS pos-quality,
          COUNT(CASE class WHEN 2 THEN 1 END) * [follow]  AS pos-follow,
          COUNT(CASE class WHEN 3 THEN 1 END) * [rt]      AS neg-rt,
          COUNT(CASE class WHEN 3 THEN 1 END) * [quality] AS neg-quality,
          COUNT(CASE class WHEN 3 THEN 1 END) * [follow]  AS neg-follow
      FROM tweets 
      GROUP BY sp100_id, nyse_date
  ) c ON s.sp100_id = c.sp100_id AND d._date = c.nyse_date
  ORDER BY s.sp100_id, d._date ASC
  </code></pre>
  
  <p>Obviously, <code>[rt]</code>, <code>[quality]</code> and <code>[follow]</code> need to be replaced by correct syntax and I'm not sure about the <code>COUNT(...)</code> either, because it now first counts the number of tweets, but it should take every tweet apart and multiply it by its own number of retweets ('rt').</p>
  
  <p>Can anybody help me out?</p>