\set SHOW_CONTEXT never
SET client_min_messages TO WARNING;
CREATE EXTENSION IF NOT EXISTS pg_ivm;
CREATE EXTENSION IF NOT EXISTS timeseries CASCADE;
CREATE TABLE ivm_events (
  user_id bigint,
  event_id bigint,
  event_time timestamptz NOT NULL,
  value float
) PARTITION BY RANGE (event_time);
SELECT enable_ts_table('ivm_events');
 enable_ts_table 
-----------------
 
(1 row)

COPY ivm_events FROM STDIN WITH (FORMAT 'csv');
CREATE VIEW ivm_events_5m AS
  SELECT
    user_id,
    date_bin('5 minutes',
             event_time,
             TIMESTAMPTZ '1970-01-01') AS event_time,
    max(value),
    min(value)
    FROM ivm_events
    GROUP BY 1, 2;
CREATE VIEW ivm_events_totals AS
  SELECT
    user_id,
    sum(value),
    count(user_id)
  FROM ivm_events
  GROUP BY 1;
SELECT make_view_incremental('ivm_events_5m');
 make_view_incremental 
-----------------------
 
(1 row)

SELECT make_view_incremental('ivm_events_totals');
 make_view_incremental 
-----------------------
 
(1 row)

SELECT * FROM ivm_events_5m ORDER BY 1, 2;
 user_id |          event_time          | max | min 
---------+------------------------------+-----+-----
       1 | Wed Nov 04 15:50:00 2020 PST | 1.2 | 1.1
       1 | Wed Nov 04 15:55:00 2020 PST | 1.6 | 1.3
       2 | Wed Nov 04 15:50:00 2020 PST | 1.8 | 1.7
       2 | Wed Nov 04 15:55:00 2020 PST | 2.2 | 1.9
(4 rows)

SELECT * FROM ivm_events_totals ORDER BY 1;
 user_id | sum  | count 
---------+------+-------
       1 |  8.1 |     6
       2 | 11.7 |     6
(2 rows)

INSERT INTO ivm_events VALUES (3, 1, '2020-11-04 15:51:02.226999-08', 1.1);
DELETE FROM ivm_events WHERE event_id = 12;
SELECT * FROM ivm_events_5m ORDER BY 1, 2;
 user_id |          event_time          | max | min 
---------+------------------------------+-----+-----
       1 | Wed Nov 04 15:50:00 2020 PST | 1.2 | 1.1
       1 | Wed Nov 04 15:55:00 2020 PST | 1.6 | 1.3
       2 | Wed Nov 04 15:50:00 2020 PST | 1.8 | 1.7
       2 | Wed Nov 04 15:55:00 2020 PST | 2.1 | 1.9
       3 | Wed Nov 04 15:50:00 2020 PST | 1.1 | 1.1
(5 rows)

SELECT * FROM ivm_events_totals ORDER BY 1;
 user_id | sum | count 
---------+-----+-------
       1 | 8.1 |     6
       2 | 9.5 |     5
       3 | 1.1 |     1
(3 rows)