clickhouse做漏斗分析

漏斗分析代码记录

SELECT media_source AS media_source, funnel_sort AS funnel_sort, count(1) AS total_count
FROM (SELECT media_source,
             arraySort(x ->
                           -x, groupArray(count)) AS funnel_sort
      FROM (SELECT media_source, level_index, count(1) AS count
            FROM (SELECT media_source,
                         user_id                           AS user_id,
                         arrayJoin(arrayEnumerate(levels)) AS level_index,
                         arrayWithConstant(level, 1)       AS levels
                  FROM (SELECT media_source,
                               user_id,
                               windowFunnel(86400)(event_time, event_name = 'bf_enter_success',
                                            event_name = 'RewardVideoShowSuccess') AS level
                        FROM (SELECT date                                                            AS date,