Sparse elapsed duration in a Timescale continuous aggregate

sparse_duration.sql

-- the custom aggregate state
CREATE TYPE elapsed__t AS (acc interval, cur timestamptz);
-- state transition function
CREATE FUNCTION elapsed__sf(s elapsed__t, x interval, cur timestamptz)
    RETURNS elapsed__t IMMUTABLE LANGUAGE sql AS $$
    SELECT
        coalesce(s.acc, '0'::interval) + case
            when cur-s.cur > x
            then null
            else cur-s.cur end,
        cur $$;
-- final function
CREATE FUNCTION elapsed__ff(s elapsed__t)
    RETURNS interval IMMUTABLE LANGUAGE sql AS $$ SELECT s.acc $$;
-- elapsed('15 minutes', time order by time)
CREATE AGGREGATE elapsed(interval, timestamptz) (
    STYPE     = elapsed__t,
    SFUNC     = elapsed__sf,
    FINALFUNC = elapsed__ff);

-- continuous aggregate
CREATE MATERIALIZED VIEW umami.session_elapsed
    WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 day', time) ß,
    website_id,
    session_id,
    elapsed('5 minutes', time order by time) active
    elapsed('15 minutes', time order by time) passive
    elapsed('1 hour', time order by time) engaged
FROM umami.event e
GROUP BY ß, website_id, session_id;