In DuckDB, Is it possible to change a value based on the previous value ordered by another column without using UDFs?

I have a column A which I want to increment or decrement according to columns B and C.

The logic is this:

if lag(A) = 0 and if B = 1 then A=1
if lag(A) = 1 and if C = 1 then A=0
the lag(A) is from ordering by D

I can't figure out how to implement this logic in DuckDB. I think the issue is that A depends on the previous value.

select
  unnest(array_agg(row(B, C) order by D)) as list1
from
  my_table

I figured I must be something to the un-nested version of list1 and I thought list_reduce might help I can't figure out how. Another thought is that recursive CTEs might help. But I was not able to find a solution.

Perhaps it's not possible in DuckDB and I need to use a list UDF.

If it's possible in any other variant of SQL then I would love to hear the answer too.

Example data

   B C A  D
1  0 0 0  1
2  1 0 1  2
3  0 0 1  3
4  1 0 1  4
5  0 1 0  5
6  1 0 1  6
7  1 0 1  7
8  0 1 0  8
9  0 0 0  9
10 0 1 0 10

R code to generate data

fn = function(b, c) {
  l = length(b)
  A = vector(mode="integer", l)
  A[1] = 0
  for (i in 2:l) {
    A[i]= A[i-1]
    if((b[i] == 1) & (A[i-1] == 0)) {
      A[i] = 1
    } else if ((c[i] == 1) & (A[i-1] == 1)) {
      A[i] = 0
    }
  }
  A
}


d = data.frame(
  B = c(0, 1, 0, 1, 0, 1, 1, 0, 0, 0),
  C = c(0, 0, 0, 0, 1, 0, 0, 1, 0, 1)
) %>% dplyr::mutate(
  A = fn(B, C)
) %>% 
  mutate(D = row_number())

d

Solution

In general sql is not the greatest tool to run such iterative calculations, but you can do it with recursive cte:

with recursive cte as (
    select b, c, d, 0 as a
    from df
    where d = 1

    union all

    select
        d.b, d.c, d.d,
        case
            when c.a = 0 and d.b = 1 then 1
            when c.a = 1 and d.c = 1 then 0
            else c.a
        end as a
    from df as d
        inner join cte as c on
            c.d = d.d - 1
)
select *
from cte

┌───────┬───────┬───────┬───────┐
│   b   │   c   │   d   │   a   │
│ int64 │ int64 │ int64 │ int32 │
├───────┼───────┼───────┼───────┤
│     0 │     0 │     1 │     0 │
│     1 │     0 │     2 │     1 │
│     0 │     0 │     3 │     1 │
│     1 │     0 │     4 │     1 │
│     0 │     1 │     5 │     0 │
│     1 │     0 │     6 │     1 │
│     1 │     0 │     7 │     1 │
│     0 │     1 │     8 │     0 │
│     0 │     0 │     9 │     0 │
│     0 │     1 │    10 │     0 │
├───────┴───────┴───────┴───────┤
│ 10 rows             4 columns │
└───────────────────────────────┘