tweets-sql

gistfile1.sql

with tweets as (
    select
        t.user.name,
        t.text,
        upper(sym.text) as ticker
    from
        Demo.twitter_live as t,
        unnest(t.entities.symbols) as sym
    where
        t.entities.symbols [1] is not null
        and t.created_at > current_timestamp() - DAYS(1)
)
select
    tw.ticker,
    tkr.Name as CompanyName,
    tkr.Industry,
    count(*) as tweet_count
from
    tweets as tw
    join Demo.tickers_alias as tkr on tkr.Symbol = tw.ticker
group by
    tw.ticker,
    tkr.Name,
    tkr.Industry
order by
    count(*) desc
limit
    10;