Athena query find till next value

Question

I have below records

The logic is index will be same till next price found, and count is just row number

I am trying to write the query, but unable to even start like how should I proceed, I was looking if loop is there but could not found.

If anyone could guide, that would be great help

Answer 1

This is basically a variation of gaps-and-islands problem.

You can use conditional sum to determine the index (group) and then use row_number to determine the count:

-- sample data
with dataset (date, price) as (
    values (1, 1300),
        (2, 0),
        (3, 0),
        (4, 869),
        (5, 0),
        (6, 0),
        (7, 259)
)

-- query
select *,
       row_number() over (partition by index order by date) as count
from (select date,
        price,
        sum(if(price > 0, 1)) over (order by date) index
from dataset) as t
order by index, count;

Output:

date	price	index	count
1	1300	1	1
2	0	1	2
3	0	1	3
4	869	2	1
5	0	2	2
6	0	2	3
7	259	3	1