-- Funnel Analysis -- count users reaching each step in order within window.
--
-- Required parameters (clickhouse.Named):
--   workspace_id   : String
--   from           : DateTime64(3,'UTC')
--   to             : DateTime64(3,'UTC')
--   window_seconds : UInt32
--   step{i}        : String  for i in 0..N-1
--
-- Template inputs:
--   .Steps        : []struct{ Index int; Last bool }
--   .StepCount    : int
SELECT
    step,
    countIf(level >= step)                                  AS reached,
    if(step = 1, 1.0, countIf(level >= step) / countIf(level >= 1)) AS conversion_rate
FROM (
    SELECT
        user_id,
        windowFunnel({window_seconds:UInt32})(
            timestamp,
{{- range $i, $s := .Steps }}
            event = {step{{ $s.Index }}:String}{{ if not $s.Last }},{{ end }}
{{- end }}
        ) AS level
    FROM events_track
    WHERE workspace_id = {workspace_id:String}
      AND received_at >= {from:DateTime64(3,'UTC')}
      AND received_at <  {to:DateTime64(3,'UTC')}
      AND user_id != ''
    GROUP BY user_id
) AS f
ARRAY JOIN range(1, toUInt32({{ .StepCount }}) + 1) AS step
GROUP BY step
ORDER BY step