In google BigQuery, I want to `sum(value)` over all rows where `condition1` is smaller than in the current row AND `condition2` is smaller than in the current row. Note that the dataset is ordered by `condition1` so all previous rows regarding `condition1` are smaller than the current row's value of `condition1`.
Also, I have a very large dataset, so a self-join is not possible with respect to calculation time.

Here is an example:

```
-- | condition1 | condition2 | value | expected_output |
-- |------------|------------|-------|-----------------|
-- |     1      |      2     |    1  |        0        |
-- |     1      |      2     |    1  |        0        |
-- |     2      |      1     |    1  |        0        |
-- |     2      |      2     |    1  |        0        | 
-- |     2      |      3     |    1  |        2        | --> sum over rows 1 and 2
-- |     3      |      2     |    1  |        1        | --> sum over row 3
-- |     3      |      2     |    1  |        1        | --> sum over row 3
-- |     4      |      5     |    1  |        7        | --> sum over rows 1-7
-- |     5      |      4     |    1  |        7        | --> sum over rows 1-7
-- |     6      |      7     |    1  |        9        | --> sum over rows 1-9

```

note that in this example `value` is always 1 to make the example easy, bis `value` can be any number.

I tried with:

```
SUM(VALUE) OVER (ORDER BY condition1, condition2 RANGE UNBOUNDED PRECEDING)

```

but the problem is, that only the first condition `condition1` is "checked". It orders by `condition2`, but still, all rows are considered in the sum, even if the value in `condition2` is larger than the value in the current row.

Some query to work with where I already tested the behavior of different `windows`:

```
WITH data AS (
  SELECT * 
  FROM UNNEST([
    STRUCT
    (1 as condition1, 2 as condition2,1 as value), 
    (2,1,1),
    (3,2,1),
    (2,2,1),
    (5,4,1),
    (4,5,1),
    (2,3,1),
    (6,7,1),
    (3,2,1)
  ])
)
SELECT
  *
  ,SUM(VALUE) OVER (ORDER BY condition1, condition2 RANGE UNBOUNDED PRECEDING) as test_1
  ,SUM(VALUE) OVER (ORDER BY condition2, condition1 RANGE UNBOUNDED PRECEDING) as test_2
  ,RANK() OVER (ORDER BY condition1, condition2) as rank1
  ,RANK() OVER (ORDER BY condition2, condition1) as rank2
FROM data
ORDER BY condition1

```

Thanks!

Update:
Thanks to @Mikhail Berlyants input I noted that my question was not phrased correctly. I updated the question and the example now.