rollupQuery.sql

with _data as (
    SELECT
        tweets,
        event_date_hour_str,
  		event_date_hour,
        id,
  		name
    FROM
        officehours."twitter-firehose-rollup" t hint(access_path=column_scan) 
    where
  		t.event_date_hour > CURRENT_TIMESTAMP() - DAYS(1)
    order by
        event_date_hour desc
),
_intermediate as (
    select
        array_agg(event_date_hour_str) _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