Add a counting condition into dense_rank window Function SQL

Question

I have a function that counts how many times you've visited and if you have converted or not. What I'd like is for the dense_rank to re-start the count, if there has been a conversion:

SELECT
          uid,
          channel,
          time,
          conversion,
          dense_rank() OVER (PARTITION BY uid ORDER BY time asc) as visit_order
FROM table

current table output: this customer (uid) had a conversion at visit 18 and now I would want the visit_order count from dense_rank to restart at 0 for the same customer until it hits the next conversion that is non-null.

Answer 1

See this (I do not like "try this" ):

SELECT
   id,
   ts,
   conversion,
   -- SC,
   ROW_NUMBER() OVER (PARTITION BY id,SC) R
FROM (
   SELECT
      id,
      ts,
      conversion,
      -- COUNT(conversion) OVER (PARTITION BY id, conversion=0 ORDER BY ts ) CC,
      SUM(CASE WHEN conversion=1 THEN 1000 ELSE 1 END) OVER (PARTITION BY id ORDER BY ts ) - SUM(CASE WHEN conversion=1 THEN 1000 ELSE 1 END) OVER (PARTITION BY id ORDER BY ts )%1000 SC
   FROM sample
   ORDER BY ts
   ) x
ORDER BY ts;

DBFIDDLE

output:

id	ts	conversion	R
1	2022-01-15 10:00:00	0	1
1	2022-01-16 10:00:00	0	2
1	2022-01-17 10:00:00	0	3
1	2022-01-18 10:00:00	1	1
1	2022-01-19 10:00:00	0	2
1	2022-01-20 10:00:00	0	3
1	2022-01-21 10:00:00	0	4
1	2022-01-22 10:00:00	0	5
1	2022-01-23 10:00:00	0	6
1	2022-01-24 10:00:00	0	7
1	2022-01-25 10:00:00	1	1
1	2022-01-26 10:00:00	0	2
1	2022-01-27 10:00:00	0	3