query2.sql

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