DB2-Assign3.sql

/*
  What is the correlation between the average daily temperature
  and the number of tweets with the tag yycbike. What does this
  correlation mean?
*/
SELECT 
    W.DIM_DATE,
    W.CW_MEAN_TEMP,
    T.NUM_TWEETS,
    CORR(W.CW_MEAN_TEMP, T.NUM_TWEETS) AS TWEET_TEMP_CORR
FROM
    YYCWAREHOUSE.FACT_WEATHER W
LEFT JOIN (
    SELECT
        DIM_DATE,
        SUM(TWEETS) AS NUM_TWEETS
    FROM (
        SELECT 
            DIM_DATE, 
            TWEETS 
        FROM 
            YYCWAREHOUSE.FACT_TWEET 
        WHERE 
            TAG = 'yycbike'
    )
    GROUP BY
        DIM_DATE
) T
ON
    W.DIM_DATE = T.DIM_DATE
GROUP BY
    W.DIM_DATE
ORDER BY
    W.DIM_DATE;
    
/*
  AS IS - produces an ERROR at line 3:
  ORA-00979: not a GROUP BY expression
  
  IF - I add the W.CW_MEAN_TEMP and T.NUM_TWEETS to
  the GROUP BY clause, the CORR() function returns nothing... :(
*/