How to force SQL row_number() to start from 1 by ignoring WHERE conditions

Question

I am trying to implement a server side pagination in retool, having Athena as the data source, and it seems that in order to make it work I have to somehow force the row_number() to start with 1.
The query has additional conditions in the WHERE statement and it queries the results based on dynamic start date and end date collected from a datetime range picker. This means the row number can start with any value, depending on the selected dates.

This is my query (query1), inspired from AWS Athena (Presto) OFFSET support:

SELECT * 
FROM (SELECT row_number() over() AS rn, * FROM database.my_table)
WHERE rn
    BETWEEN {{table.paginationOffset}} AND {{table.paginationOffset + 10}}
    AND date > CAST('{{dateRangeModule.start_date}}' AS DATE)
    AND date < CAST('{{dateRangeModule.end_date}}' AS DATE)

The table has n number of pages and it shows 10 rows per page, meaning that the BETWEEN {{table.paginationOffset}} AND {{table.paginationOffset + 10}} condition can result to these cases:
page 1: BETWEEN 0 AND 10
page 2: BETWEEN 10 AND 20
page 3: BETWEEN 20 AND 30
and so on.

Running the query without the condition described above:

SELECT * 
FROM (SELECT row_number() over() AS rn, * FROM database.my_table)
WHERE rn
    date > CAST('{{dateRangeModule.start_date}}' AS DATE)
    AND date < CAST('{{dateRangeModule.end_date}}' AS DATE)

returns values which don't start from 1 in the rn column.

This means that the query1 will return results only starting with page 3 where it will show only those two records having rn=29 and rn=30, because:
rows with rn < 29 are not on page 1, page 2, and page 3.
rows with 29 <= rn <= 30 are on page 3.
rows with 31 <= rn <= 40 are on page 4.
and so on.

Following the description above, how can I force the row_number to start from 1? Any solution which would fit my case, but not using the row_number(), would work too.

---

Edit per solution provided by Gordon Linoff:
Tried with the below query provided in the solution, but rn is still different than 1:

SELECT t.*,
       ROW_NUMBER() OVER () as page_rn
FROM (SELECT row_number() over() AS rn, t.*
      FROM database.my_table t
     ) t
WHERE rn BETWEEN {{table.paginationOffset}} AND {{table.paginationOffset + 10}} AND
      date > CAST('{{dateRangeModule.start_date}}' AS DATE) AND
      date < CAST('{{dateRangeModule.end_date}}' AS DATE);

Answer 1

Add a new row number for the page:

SELECT t.*,
       ROW_NUMBER() OVER () as page_rn
FROM (SELECT row_number() over() AS rn, t.*
      FROM database.my_table t
     ) t
WHERE rn BETWEEN {{table.paginationOffset}} AND {{table.paginationOffset + 10}} AND
      date > CAST('{{dateRangeModule.start_date}}' AS DATE) AND
      date < CAST('{{dateRangeModule.end_date}}' AS DATE);

Note that without ORDER BY -- for both the row number and the query -- the results are indeterminate. Let me assume that you really have the ORDER BYs and just oversimplified the query for the question.