How to get data for past X weeks in redshift?

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

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
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 (fromWeek <= '44' AND toWeek >= '45')

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

Count
-----
124

Now I am trying to make this query dynamic such that it can give me count for past 6 weeks starting from 44 to 39 not including current week something like below as an output:

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

So my condition for each week will be like this if I run it manually for each of those week –

and (fromWeek <= '44' AND toWeek >= '45') // for week 44
and (fromWeek <= '43' AND toWeek >= '44') // for week 43
and (fromWeek <= '42' AND toWeek >= '43') // for week 42
and (fromWeek <= '41' AND toWeek >= '42') // for week 41
and (fromWeek <= '40' AND toWeek >= '41') // for week 40
and (fromWeek <= '39' AND toWeek >= '40') // for week 39

Now I need to make above manual query for each of the week in a dynamic way. I came up with below query but somehow I don’t get any output back when I run it. Anything wrong I am doing in 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 fromWeek,
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 fromWeek <= date_part(w, current_date - interval '6 weeks') 
and toWeek >= date_part(w, current_date)
group by fromWeek

Note:

fromWeek and toWeek column are integer data type and they contain week number just fyi. It’s a legacy query so I am trying to make it dynamic. Not sure why they have single quotes around 44 and 45 but If I run my original manual query like this and (fromWeek <= '44' AND toWeek >= '45') or this and (fromWeek <= 44 AND toWeek >= 45) for week 44 I always get same data as 124.

Answer

Don’t you want toWeek to be 1 greater than fromWeek? If so your where clauses should be:

...
and fromWeek <= date_part(w, current_date - interval '6 weeks') 
and toWeek >= date_part(w, current_date - interval '5 weeks')
...

I suspect you just don’t have data for values for toWeek that are high enough to pass your toWeek where clause.

Advertisement

Answer