sql - How to avoid using limit clause in my redshift query?

Question

I have a below query where I am using limit 8 to get value of dates as shown below:

SELECT (date_trunc('week', getdate() + INTERVAL '1 day')::date - 7 * (row_number() over (order by true) - 1) - INTERVAL '1 day')::date AS week_column
FROM dimensions.customer LIMIT 8

Below is the output I get from above query:

2021-01-10
2021-01-03
2020-12-27
2020-12-20
2020-12-13
2020-12-06
2020-11-29
2020-11-22

Is there any way to avoid using limit 8 in my above query and still get same output? One of our platform doesn't allow us to run queries if it has limit in it so trying to see if I can rewrite it differently in sql redshift?

Below is my full query where I am using my dates CTE:

WITH dates AS (
    SELECT (date_trunc('week', getdate() + INTERVAL '1 day')::date - 7 * (row_number() over (order by true) - 1) - INTERVAL '1 day')::date AS week_column
    FROM dimensions.customer LIMIT 8
)
SELECT 
dates.week_column, 
'W' || ceiling(date_part('week', dates.week_column + INTERVAL '1 day')) AS week_number,
COUNT(DISTINCT features.client_id) AS total
FROM dimensions.program features 
JOIN dates ON features.last_update <= dates.week_column
WHERE features.type = 'capacity'
AND features.status = 'CURRENT'
GROUP BY dates.week_column
ORDER by dates.week_column DESC

How can I rewrite my dates CTE query differently so that it can give me same output?

Update

If I run query like this it gives me an error but if I run innermost sql query inside from then it gives me data.

SELECT *
FROM (SELECT (date_trunc('week', getdate() + INTERVAL '1 day')::date - 7 * (row_number() over (order by true) - 1) - INTERVAL '1 day')::date AS week_column,
              ROW_NUMBER() OVER () as seqnum
      FROM dimensions.customer
     ) c
WHERE seqnum <= 8;

Error is:

Invalid operation: Output timestamp out of range after subtracting constant. Details: ----------------------------------------------- error: Output timestamp out of range after subtracting constant. 

Answer 1

You can use window functions:

SELECT . . .   -- whatever columns you want
FROM (SELECT (date_trunc('week', getdate() + INTERVAL '1 day')::date - 7 * (row_number() over (order by true) - 1) - INTERVAL '1 day')::date AS week_column,
              ROW_NUMBER() OVER () as seqnum
      FROM dimensions.customer
     ) c
WHERE seqnum <= 8;

In Redshift, the ORDER BY is optional for ROW_NUMBER(). In general, I would encourage you to have an ORDER BY. That is true of your query as well. LIMIT is normally used with an ORDER BY.

EDIT:

The idea is the same:

with dates as (
     ),
     q as (
      <your query here>
     )
select q.*
from (select q.*,
             row_number() over (order by weeks_column desc) as seqnum
      from q
     ) q
where seqnum <= 8;