Materialize/test/sqllogictest/session-window-wmr.slt

# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.

statement ok
CREATE TABLE events (
    -- event ID
    id int,
    -- when?
    event_time timestamp,
    -- who?
    user_id int,
    -- how much?
    worth decimal
);

statement ok
CREATE MATERIALIZED VIEW event_session AS
    WITH MUTUALLY RECURSIVE
        -- Convert each value in events into a 5-minute long session
        make_session (user_id int4, session tsrange)
            AS (
                SELECT
                    user_id, tsrange(event_time, event_time + '5 m'::INTERVAL) AS session
                FROM
                    events
            ),
        -- Merge any overlapping/adjacent sessions
        merge_session (user_id int4, session tsrange)
            AS (
                SELECT
                    DISTINCT user_id, l_session + r_session
                FROM
                    (
                        SELECT
                            l.user_id AS user_id, l.session AS l_session, r.session AS r_session
                        -- Base case is just the `make_session` relation
                        FROM
                            make_session AS l, make_session AS r
                        WHERE
                            l.user_id = r.user_id
                            AND (l.session && r.session OR l.session -|- r.session)
                        UNION ALL
                            -- Further improve any merged sessions against any new sessions
                            SELECT
                                make_session.user_id, make_session.session, merge_session.session
                            FROM
                                make_session, merge_session
                            WHERE
                                -- ? @aalexandrov could this be
                                -- reduce_session.user_id = merge_session.user_id
                                -- AND (
                                --         reduce_session.session && merge_session.session
                                --         OR reduce_session.session -|- merge_session.session
                                --     )
                                -- if we do a cross join of reduce_session, merge_session
                                make_session.user_id = merge_session.user_id
                                AND (
                                        make_session.session && merge_session.session
                                        OR make_session.session -|- merge_session.session
                                    )
                    )
            ),
        -- Keep only widest session that touches any given point in time
        reduce_session (user_id int4, session tsrange)
            AS (
                SELECT
                    user_id, tsrange(lower, upper)
                FROM
                    (
                        SELECT
                            user_id, min(lower) AS lower, upper
                        FROM
                            (
                                SELECT
                                    user_id, lower(session), max(upper(session)) AS upper
                                FROM
                                    merge_session
                                GROUP BY
                                    user_id, lower(session)
                            )
                        GROUP BY
                            user_id, upper
                    )
            )
    SELECT
        *
    FROM
        reduce_session;

statement ok
CREATE MATERIALIZED VIEW user_session_worth AS
    SELECT
        user_id, id, count, upper(session) - lower(session) AS session_len, sum AS worth
    FROM
        (
            SELECT
                events.user_id, session, min(id) AS id, count(id), sum(worth)
            FROM
                events
                JOIN event_session ON
                        events.user_id = event_session.user_id
                        AND event_session.session @> events.event_time
            GROUP BY
                events.user_id, session
        );


statement ok
INSERT INTO events VALUES
    (1, '2021-01-01 10:00:00'::timestamp, 1, '0.01');

query T
SELECT concat(user_id, ' ', session) FROM event_session ORDER BY 1
----
1 ["2021-01-01 10:00:00","2021-01-01 10:05:00")

query T
SELECT concat(user_id, ' ', id, ' ', count, ' ', session_len, ' ', worth) FROM user_session_worth ORDER BY 1;
----
1 1 1 00:05:00 0.01

statement ok
INSERT INTO events VALUES
    (2, '2021-01-01 10:03:00'::timestamp, 1, '0.01');

query T
SELECT concat(user_id, ' ', session) FROM event_session ORDER BY 1
----
1 ["2021-01-01 10:00:00","2021-01-01 10:08:00")

query T
SELECT concat(user_id, ' ', id, ' ', count, ' ', session_len, ' ', worth) FROM user_session_worth ORDER BY 1;
----
1 1 2 00:08:00 0.02

statement ok
INSERT INTO events VALUES
    (3, '2021-01-01 10:06:00'::timestamp, 1, '1.00');

query T
SELECT concat(user_id, ' ', session) FROM event_session ORDER BY 1
----
1 ["2021-01-01 10:00:00","2021-01-01 10:11:00")

query T
SELECT concat(user_id, ' ', id, ' ', count, ' ', session_len, ' ', worth) FROM user_session_worth ORDER BY 1;
----
1 1 3 00:11:00 1.02

statement ok
INSERT INTO events VALUES
    (4, '2021-01-01 10:13:00'::timestamp, 1, '0.01');

query T
SELECT concat(user_id, ' ', session) FROM event_session ORDER BY 1
----
1 ["2021-01-01 10:00:00","2021-01-01 10:11:00")
1 ["2021-01-01 10:13:00","2021-01-01 10:18:00")

query T
SELECT concat(user_id, ' ', id, ' ', count, ' ', session_len, ' ', worth) FROM user_session_worth ORDER BY 1;
----
1 1 3 00:11:00 1.02
1 4 1 00:05:00 0.01

statement ok
INSERT INTO events VALUES
    (5, '2021-01-01 10:15:00'::timestamp, 1, '0.01');

query T
SELECT concat(user_id, ' ', session) FROM event_session ORDER BY 1
----
1 ["2021-01-01 10:00:00","2021-01-01 10:11:00")
1 ["2021-01-01 10:13:00","2021-01-01 10:20:00")

query T
SELECT concat(user_id, ' ', id, ' ', count, ' ', session_len, ' ', worth) FROM user_session_worth ORDER BY 1;
----
1 1 3 00:11:00 1.02
1 4 2 00:07:00 0.02

statement ok
INSERT INTO events VALUES
    (6, '2021-01-01 10:21:00'::timestamp, 1, '0.02');

query T
SELECT concat(user_id, ' ', session) FROM event_session ORDER BY 1
----
1 ["2021-01-01 10:00:00","2021-01-01 10:11:00")
1 ["2021-01-01 10:13:00","2021-01-01 10:20:00")
1 ["2021-01-01 10:21:00","2021-01-01 10:26:00")

query T
SELECT concat(user_id, ' ', id, ' ', count, ' ', session_len, ' ', worth) FROM user_session_worth ORDER BY 1;
----
1 1 3 00:11:00 1.02
1 4 2 00:07:00 0.02
1 6 1 00:05:00 0.02

statement ok
INSERT INTO events VALUES
    (7, '2021-01-01 10:02:00'::timestamp, 2, '1.00');

query T
SELECT concat(user_id, ' ', session) FROM event_session ORDER BY 1
----
1 ["2021-01-01 10:00:00","2021-01-01 10:11:00")
1 ["2021-01-01 10:13:00","2021-01-01 10:20:00")
1 ["2021-01-01 10:21:00","2021-01-01 10:26:00")
2 ["2021-01-01 10:02:00","2021-01-01 10:07:00")

query T
SELECT concat(user_id, ' ', id, ' ', count, ' ', session_len, ' ', worth) FROM user_session_worth ORDER BY 1;
----
1 1 3 00:11:00 1.02
1 4 2 00:07:00 0.02
1 6 1 00:05:00 0.02
2 7 1 00:05:00 1

statement ok
INSERT INTO events VALUES
    (8, '2021-01-01 10:04:00'::timestamp, 2, '1.00');

query T
SELECT concat(user_id, ' ', session) FROM event_session ORDER BY 1
----
1 ["2021-01-01 10:00:00","2021-01-01 10:11:00")
1 ["2021-01-01 10:13:00","2021-01-01 10:20:00")
1 ["2021-01-01 10:21:00","2021-01-01 10:26:00")
2 ["2021-01-01 10:02:00","2021-01-01 10:09:00")

query T
SELECT concat(user_id, ' ', id, ' ', count, ' ', session_len, ' ', worth) FROM user_session_worth ORDER BY 1;
----
1 1 3 00:11:00 1.02
1 4 2 00:07:00 0.02
1 6 1 00:05:00 0.02
2 7 2 00:07:00 2

statement ok
INSERT INTO events VALUES
    (9, '2021-01-01 10:17:00'::timestamp, 1, '0.01');

query T
SELECT concat(user_id, ' ', session) FROM event_session ORDER BY 1
----
1 ["2021-01-01 10:00:00","2021-01-01 10:11:00")
1 ["2021-01-01 10:13:00","2021-01-01 10:26:00")
2 ["2021-01-01 10:02:00","2021-01-01 10:09:00")

query T
SELECT concat(user_id, ' ', id, ' ', count, ' ', session_len, ' ', worth) FROM user_session_worth ORDER BY 1;
----
1 1 3 00:11:00 1.02
1 4 4 00:13:00 0.05
2 7 2 00:07:00 2

statement ok
DELETE FROM events WHERE id = 9;

query T
SELECT concat(user_id, ' ', session) FROM event_session ORDER BY 1
----
1 ["2021-01-01 10:00:00","2021-01-01 10:11:00")
1 ["2021-01-01 10:13:00","2021-01-01 10:20:00")
1 ["2021-01-01 10:21:00","2021-01-01 10:26:00")
2 ["2021-01-01 10:02:00","2021-01-01 10:09:00")

query T
SELECT concat(user_id, ' ', id, ' ', count, ' ', session_len, ' ', worth) FROM user_session_worth ORDER BY 1;
----
1 1 3 00:11:00 1.02
1 4 2 00:07:00 0.02
1 6 1 00:05:00 0.02
2 7 2 00:07:00 2