How can we calculate the conversion time for each step using the windowFunnel function?

[zhangmingchao]

You can use the windowfunnel function to calculate the number of people in each step and the conversion rate, but what about the conversion time for those people?

[nikitamikhaylov]

Please provide the table schema and the expected result of the query. It is super difficult to understand what is needed given only the information above.

[zhangmingchao]

table structure

-- auto-generated definition
create table events_zc
(
_appkey String comment '分区字段，Appkey ',
_event_name String comment '分区字段，事件标识',
_user_id Int64 comment '用户唯一ID',
_event_time DateTime64(3) comment '事件时间,n 相当于数据中的_ts字段',
_db_time Int64 comment '入库时间'
)
engine = MergeTree PARTITION BY (toYYYYMM(_event_time))
ORDER BY (_event_name, _event_time, _user_id)
SETTINGS index_granularity = 8192;

data:

need:

My need is like a funnel, I need to calculate the transition time for each step, step 1 is _event_name=start step 2 is _event_name= visitor step 3 is _event_name= end, The window is 40 minutes. You can use the clickhouse windowFunnel function to calculate the number of converts per step, but you can't calculate the average conversion time per step

windowFunnel Sql

select
countIf(1, level >= 1) as step1,
countIf(1, level >= 2) as step2,
round(step2/step1,4) as step2_rate,
countIf(1, level >= 3) as step3,
round(step3/step2,4) as step3_rate
from (
select _user_id,
windowFunnel(2400)(toDateTime(_event_time), _event_name = 'start', _event_name = 'visitor',
_event_name = 'end') as level
from (
select *
from events_zc
order by _event_time, _user_id
) events_zc
group by _user_id
) data

What is the conversion time ?

Step 7 people, step 1 to step 6 people, so the six people (step 2 time - step 1 time)/6 is the average conversion time of the six people, excuse me, this demand, how should I achieve? From the data, you can see that the time of step 1 is 2024-01-01 10:00:00 and the time of step 2 is 2024-01-01 10:20:00, so the average conversion time is 1200s. How do I do that with ck?