most_tweeted_stocks.sql

-- unnest tweets with stock ticker symbols from the past 1 day
WITH stock_tweets AS
      (SELECT t.user.name, t.text, upper(sym.text) AS ticker
       FROM   "twitter-firehose" AS t, unnest(t.entities.symbols) AS sym
       WHERE  t.entities.symbols[1] is not null
         AND  t._event_time > current_timestamp() - INTERVAL 1 day),

-- aggregate stock ticker symbol tweet occurrences 
    top_stock_tweets AS
      (SELECT ticker, count(*) AS tweet_count
       FROM   stock_tweets
       GROUP BY ticker),

-- join stock ticker symbol in tweets with NASDAQ company list data
    stock_info_with_tweets AS 
      (SELECT top_stock_tweets.ticker, top_stock_tweets.tweet_count,
              tickers.Name, tickers.Industry, tickers.MarketCap
       FROM top_stock_tweets JOIN tickers
         ON top_stock_tweets.ticker = tickers.Symbol)

-- show top 10 most tweeted stock ticker symbols along with company info
SELECT * 
FROM   stock_info_with_tweets t
ORDER BY t.tweet_count DESC
LIMIT 10