SQLite Forum

How to convert to CTE
Login
```
with reg1(id, date, status, pstatus)
  as (
          select id,
                 date_status_added,
                 reg_status,
                 lag(reg_status, 1) over (partition by id order by date_status_added, reg_status desc)
            from reg_history_all
           where reg_status in ('Registered', 'Deceased')
        order by id, date_status_added, reg_status desc
     ),
     reg2(id, date, cnt)
  as (
        select id,
               date,
               iif(status == 'Registered', 1, iif(pstatus == 'Registered', -1, 0))
          from reg1
         where status IS NOT pstatus
     ),
     reg3(date, cnt)
  as (
          select date,
                 sum(cnt)
            from reg2
        group by date
        order by date
     )
  select date,
         sum(cnt) over (rows between unbounded preceding and current row)
    from reg3
order by date
;
```

**Edited for correctness**