Is there a way to do a self join in a query

[S]

We have our hits table and would like to do a funnel report.

Our table has just 4 columns:

userID, eventType, EventTime,    Age

1           A              '01-01-2001'    35

2           A              '01-01-2001'    35

1           B              '01-01-2002'    35

1           A              '01-01-2010'    35

3           A              '01-01-2001'    25

3           B              '01-01-2011'    25

4           A              '01-01-2012'    35

4           B              '01-01-2011'    35

Question: Want to get how many people who are older then 30 years of age, who first did event A, then they did Event B.

Result: Based on the data above, the answer should be 1. (userid 1 is the only one matching this criteria.

Can you please provide help with this?

[man...@gmail.com]

sequenceMatch aggregate function doing exactly what you need: https://gist.github.com/alexey-milovidov/3a429ab096d6fdbfd42f4862cafab017
https://clickhouse.yandex/reference_en.html#sequenceMatch%28pattern%29%28time,%20cond1,%20cond2,%20...%29

[Burak Emre Kabakcı]

Unfortunately ClickHouse implements SQL quite different from ANSI SQL standard but the solution you suggested actually works better than the funnel query that uses JOINs.

Do you also have an example for retention queries? We need to perform queries such as "users who did register event come back 1..x days and did pageview event". Currently we use Postgresql and implemented this using PL/Pgsql. Since ClickHouse doesn't have UDF and JOINs has limited capabilities (also expensive) we don't know how to do that with ClickHouse.