Hi All,
I am developing SQL query for funnel analysis. We have one big table 'events' with following structure (simplified):
id | user_id | type_id | created
-- | ------- | ------- | -------
1 | 7160 | 6148 | 2016-05-20 12:11:21
...
Currently I have this SQL query (for three steps):
SELECT
SUM(CASE WHEN sub.PERFORMED_STEP1 THEN 1 ELSE 0 END) AS STEP1_COUNT,
SUM(CASE WHEN sub.PERFORMED_STEP2 THEN 1 ELSE 0 END) AS STEP2_COUNT,
SUM(CASE WHEN sub.PERFORMED_STEP3 THEN 1 ELSE 0 END) AS STEP3_COUNT
FROM
(
SELECT
Q1.user_id,
Q1.created IS NOT NULL AS PERFORMED_STEP1,
Q2.created IS NOT NULL AS PERFORMED_STEP2,
Q3.created IS NOT NULL AS PERFORMED_STEP3
FROM
(SELECT user_id,MIN(created) as created FROM events WHERE type_id=6451 and created between '2016-04-16' and '2016-05-15' GROUP BY user_id) AS Q1
LEFT JOIN
(SELECT user_id,MIN(created) as created FROM events WHERE type_id=6782 and created between '2016-04-16' and '2016-05-15' GROUP BY user_id) AS Q2
ON Q1.user_id=Q2.user_id AND Q1.created<Q2.created
LEFT JOIN
(SELECT user_id,MIN(created) as created FROM events WHERE type_id=356 and created between '2016-04-16' and '2016-05-15' GROUP BY user_id) AS Q3
ON Q2.user_id=Q3.user_id AND Q2.created<Q3.created
) sub
It works almost good, but unfortunately, there is an error.
We need to build a funnel for events 1,2,3. If events happened in following order: 2,1,2,3 then this query is not working, because on step 2 we use "MIN(created)" and first event is counted (instead of third event).
Is there any suggestions how to solve this problem?
I think that we need to some kind of filtering in inner query 2 and 3 to filter all events, that happened before event in first level. But I have no idea how to do this (we can't write "created > q1.created" in WHERE clause)
--