CODEWITHSUNDEEP
sqlamazon-redshift
user3035168
user3035168

Reputation: 57

Redshift querying data on dates

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.

Upvotes: 0

Views: 194

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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;

Upvotes: 1

Related Questions