Skip to content

Instantly share code, notes, and snippets.

@nfarah86
Last active May 2, 2022 19:08
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save nfarah86/24ccc1b0ca74cc2dee85fb2c98658056 to your computer and use it in GitHub Desktop.
with _data as (
SELECT
count(*) tweets,
event_date_hour,
t.user.id,
arbitrary(t.user.name) name
FROM
officehours."twitter-firehose_sqlTransformation" t hint(access_path=column_scan)
where
_event_time > CURRENT_TIMESTAMP() - DAYS(1)
group by
t.user.id,
event_date_hour
order by
event_date_hour desc
),
_intermediate as (
select
array_agg(event_date_hour) _keys,
array_agg(tweets) _values,
id,
arbitrary(name) name
from
_data
group by
_data.id
)
select
object(_keys, _values) as timeseries,
id,
name
from
_intermediate
order by length(_keys) desc
limit 100
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment