Funnels package incompatible with ClickHouse #1433
Description
Problem
When run generate SQL for clickhouse(version 19.16.19.85)， it shows an error：
myfunel.js
Related Cube.js schema
const Funnels = require(`Funnels`);
cube(`LoginF`, {
extends: Funnels.eventFunnel({
userId: {
sql: `userId`,
},
time: {
sql: `time_dt`,
},
steps: [{
name: `fetch_step`,
eventsView: {
sql: `select * from oneid_ckdb.ppd_monitor_oneidcf_all where serviceName = 'loanhome/userNotLoginConfigService/queryLoginModel' `
},
},
{
name: `code_step`,
eventsView: {
sql: `select * from oneid_ckdb.ppd_monitor_oneidcf_all where serviceName = 'PassportSDK/passportv2-openCodeService/sendCodeApp' `
},
// timeToConvert: '1 day'
}],
},
)
});Related Cube.js generated SQL
SELECT
toDateTime(
toStartOfDay(
toTimeZone(toDateTime(`login_f`.t), 'UTC'),
'UTC'
),
'UTC'
) `login_f__time_day`,
count(`login_f`.user_id) `login_f__conversions`
FROM
(
WITH joined_events AS (
select
fetch_step_events.user_id fetch_step_user_id,
code_step_events.user_id code_step_user_id,
fetch_step_events.t
FROM
(
select
userId user_id,
time_dt t
from
(
select
*
from
oneid_ckdb.ppd_monitor_oneidcf_all
where
serviceName = 'loanhome/userNotLoginConfigService/queryLoginModel'
) e
) fetch_step_events
LEFT JOIN (
select
userId user_id,
time_dt t
from
(
select
*
from
oneid_ckdb.ppd_monitor_oneidcf_all
where
serviceName = 'PassportSDK/passportv2-openCodeService/sendCodeApp'
) e
) code_step_events ON fetch_step_events.user_id = code_step_events.user_id
AND code_step_events.t >= fetch_step_events.t
)
select
user_id,
first_step_user_id,
step,
max(t) t
from
(
SELECT
fetch_step_user_id user_id,
fetch_step_user_id first_step_user_id,
t,
'Fetch Step' step
FROM
joined_events
UNION ALL
SELECT
code_step_user_id user_id,
fetch_step_user_id first_step_user_id,
t,
'Code Step' step
FROM
joined_events
) as event_steps
GROUP BY
1,
2,
3
) AS `login_f`
GROUP BY
`login_f__time_day`
ORDER BY
`login_f__time_day` ASC
LIMIT
10000