How to pull data for past x weeks dynamically in redshift?

I have a below query which I run gives me the single count for previous week which is Week 43. Current week is 44 as of now.

select
count(distinct(clientid))
from data_holder
left join name
on name.client_id = data_holder.clientid
where POC NOT IN ('SGH', 'IKU')
and status IN ('NOTTAKEN')
and (from <= '43' AND to >= '44')

As of now the output I see is this which is for Week 43-

Count
-----
124 

Now I am trying to make this query dynamic such that it can give me count for past 6 weeks something like below as an output:

Count   Week
------------
124     W43
125     W42
126     W41
127     W40
128     W39
129     W38

I am able to convert above query in dynamic way which gives me the count for previous week which is 43 and it works fine but I am not sure how can I change it so that it can give me data for all past 6 weeks in the above output format.

select
count(distinct(clientid))
from data_holder
left join name
on name.client_id = data_holder.clientid
where POC NOT IN ('SGH', 'IKU')
and status IN ('NOTTAKEN')
and (from <= DATE_PART(w, CURRENT_DATE) -1 AND to >= DATE_PART(w, CURRENT_DATE))

Update

I ran below query and I am not seeing any data coming from below query –

with data_holder as
    (
    with tree_post as
    (Select contractid as conid, max(goldennmber) as goldennmber
    from zeus.user_keys_post group by contractid)
    Select * from tree_post join zeus.user_keys_post b
    on tree_post.conid = b.contractid and tree_post.goldennmber = b.goldennmber
    ),
     
    name as 
    (
    SELECT abc, client_id, services from dim.crom c1 where c1.ver = (SELECT MAX(ver) from dim.crom c2 
    where c1.client_id = c2.client_id)
    )
     
    select from,
    count(distinct(clientid))
    from data_holder
    left join name
    on name.client_id = data_holder.clientid
    where POC NOT IN ('SGH', 'IKU')
    and status IN ('NOTTAKEN')
    and from <= date_part(w, current_date - interval '6 weeks') 
    and to   >= date_part(w, current_date)
    group by from

Answer

Is this what you want?

AND "from" <= DATE_PART(w, CURRENT_DATE - INTERVAL '6 weeks') 
AND "to"   >= DATE_PART(w, CURRENT_DATE))

Then, if you want one row per from, you can use group by. So:

select ??.from, count(distinct ??.clientid) cnt
from data_holder dh
left join name n on on n.client_id = dh.clientid
where 
    ??.poc not in ('SGH', 'IKU') 
    and ??.status = 'NOTTAKEN'
    and ??.from <= date_part(w, current_date - interval '6 weeks') 
    and ??.to   >= date_part(w, current_date)
group by ??.from

Note that I modified the query to use table aliases. I would also recommend prefixing each column with the table it belongs to, so the query is unambiguous about the underlying data structure: I had no clue, so I used ??, which you need to replace with either dh or n.

I am also quite suspicious about the left join. Is there a good reason why you don’t actually want an inner join instead?