How to use ROW_NUMBER in sqlite

Try this query

select id, value, (select count(*) from tbl b  where a.id >= b.id) as cntfrom tbl a

FIDDLE

| id | value | cnt |--------------------|  1 |   yes |   1 ||  3 |   yes |   2 ||  4 |   yes |   3 ||  6 |   yes |   4 ||  9 |   yes |   5 |

SQLite Release 3.25.0 will add support for window functions

2018-09-15 (3.25.0)

1. Add support for window functions

Window Functions :

A window function is a special SQL function where the input values are taken from a "window" of one or more rows in the results set of a SELECT statement.

SQLite supports the following 11 built-in window functions:

row_number()

The number of the row within the current partition. Rows are numbered starting from 1 in the order defined by the ORDER BY clause in the window definition, or in arbitrary order otherwise.

So your query could be rewritten as:

select *, ROW_NUMBER() OVER(ORDER BY Id) AS NoIdfrom data where value = "yes";

db-fiddle.com demo

I mended somewhat with fiddleanswer and got exactly the result as expected

select id, value ,        (select count(*) from data b where a.id >= b.id and b.value='yes') as cnt from data a where  a.value='yes';result1|yes|13|yes|24|yes|36|yes|49|yes|5