Example of sequenceMatch aggregate function usage in ClickHouse.

example.txt

$ clickhouse-client 
ClickHouse client version 0.0.53720.
Connecting to localhost:9000.
Connected to ClickHouse server version 1.1.53981.

:) CREATE TABLE test.funnel (userID UInt64, eventType Enum8('A' = 1, 'B' = 2), EventTime Date, Age UInt8) ENGINE = Memory

CREATE TABLE test.funnel
(
    userID UInt64, 
    eventType Enum8('A' = 1, 'B' = 2), 
    EventTime Date, 
    Age UInt8
) ENGINE = Memory

Ok.

0 rows in set. Elapsed: 0.014 sec. 

:) INSERT INTO test.funnel VALUES (1,'A','2001-01-01',35),(2,'A','2001-01-01',35),(1,'B','2002-01-01',35),(1,'A','2010-01-01',35),(3,'A','2001-01-01',25),(3,'B','2011-01-01',25),(4,'A','2012-01-01',35),(4,'B','2011-01-01',35)

INSERT INTO test.funnel VALUES

Ok.

8 rows in set. Elapsed: 0.006 sec. 

:) SELECT * FROM test.funnel

SELECT *
FROM test.funnel

┌─userID─┬─eventType─┬──EventTime─┬─Age─┐
│      1 │         A │ 2001-01-01 │  35 │
│      2 │         A │ 2001-01-01 │  35 │
│      1 │         B │ 2002-01-01 │  35 │
│      1 │         A │ 2010-01-01 │  35 │
│      3 │         A │ 2001-01-01 │  25 │
│      3 │         B │ 2011-01-01 │  25 │
│      4 │         A │ 2012-01-01 │  35 │
│      4 │         B │ 2011-01-01 │  35 │
└────────┴───────────┴────────────┴─────┘

8 rows in set. Elapsed: 0.007 sec. 

:) SELECT userID FROM test.funnel WHERE Age > 30 GROUP BY userID HAVING sequenceMatch('(?1)(?2)')(toDateTime(EventTime), eventType = 'A', eventType = 'B')

SELECT userID
FROM test.funnel
WHERE Age > 30
GROUP BY userID
HAVING sequenceMatch('(?1)(?2)')(toDateTime(EventTime), eventType = 'A', eventType = 'B')

┌─userID─┐
│      1 │
└────────┘

1 rows in set. Elapsed: 0.011 sec. 

:) SELECT count() FROM (SELECT userID FROM test.funnel WHERE Age > 30 GROUP BY userID HAVING sequenceMatch('(?1)(?2)')(toDateTime(EventTime), eventType = 'A', eventType = 'B'))

SELECT count()
FROM 
(
    SELECT userID
    FROM test.funnel
    WHERE Age > 30
    GROUP BY userID
    HAVING sequenceMatch('(?1)(?2)')(toDateTime(EventTime), eventType = 'A', eventType = 'B')
)

┌─count()─┐
│       1 │
└─────────┘

1 rows in set. Elapsed: 0.003 sec.