Redshift querying data on dates

Question

I am trying to query data from redshift table.

I have a users table with columns such as name, age, gender and created_at for example:

|----|-----|------|----------| |name|age |gender|created_at| ------------------------------ |X | 24 | F | some_date| ______________________________

I need to query above table, in such a way that I have additional columns such as created_this_week, created_last_week, created_last_4_week, current_month, last_month etc Additional flag columns should be 'Y' for conditions such as data is from last week, current week, current month, last month, last 4 weeks (excluding this week) so last 4 weeks starting last week etc, something like below.

|----|-----|------|-----------|------------|---------|-----------|------------|---------| |name|age |gender|created_at |current_week|last_week|last_4_week|current_mnth|last_mnth| _________________________________________________________________________________________ | X | 24 | F |CURRENTDATE| Y | N | N | Y | N | _________________________________________________________________________________________ | F | 21 | M | lst_wk_dt | N | Y | Y | Depends | depends | _________________________________________________________________________________________

I am new to PostgresSQL and Redshift, and still in my learning phase, I spent past few hrs trying to do this myself but was unsuccessful. I'd really appreciate if someone can help me out with this one.

Are you defining a week as inside the current Sun-Sat, or are you defining it as last 7 days, regardless of the current day of the week? — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 20, 2020 at 2:57

Gordon Linoff · Accepted Answer · 2020-02-20 02:58:34Z

1

You would use a case expressions:

select t.*, (case when created_at >= now() - interval '1 week' then 'Y' else 'N' end) as week1, (case when created_at >= now() - interval '4 week' then 'Y' else 'N' end) as week4, . . . from t;

answered Feb 20, 2020 at 2:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3035168 Over a year ago

Thanks, I get it now. Are these functions supported in redshift?

Tim Biegeleisen Over a year ago

Gordon's answer should run in Redshift without any issues AFAIK +1.

user3035168 Over a year ago

sounds good. One more question, created_at >= now() - interval '1 week' = will this return Y from today - 7 days, or last week as last Monday to last Sunday? last week in my case is from Feb 10 to Feb 16.

Gordon Linoff Over a year ago

@user3035168 . . . Your question is not clear on what you mean by those constructs. If you want a "calendar" week, then use date_trunc('week', now()).

Collectives™ on Stack Overflow

Redshift querying data on dates

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related