CODEWITHSUNDEEP
sqlamazon-redshift
eric-o
eric-o

Reputation: 65

How to force zero values in Redshift?

If this is my query:

select
 (min(timestamp))::date as date,
 (count(distinct(user_id)) as user_id_count
 (row_number() over (order by signup_day desc)-1) as days_since
from
  data.table
where
  timestamp >= current_date - 3
group by
  timestamp
order by
  timestamp asc;

And these are my results

date        | user_id_count   | days_since
------------+-----------------+-------------
2018-01-22  | 3               | 1
2018-01-23  | 5               | 0

How can I get it the table to show (where the user ID count is 0?): 

date        | user_id_count   | days_since
------------+-----------------+-------------
2018-01-21  | 0               | 0
2018-01-22  | 3               | 1
2018-01-23  | 5               | 0

Upvotes: 1

Views: 1114

Answers (2)

AlexYes
AlexYes

Reputation: 4208

You have to create a "calendar" table with dates and left join your aggregated result like that:

with 
aggregated_result as (
    select ...
)
select
 t1.date
,coalesce(t2.user_id_count,0) as user_id_count
,coalesce(t2. days_since,0) as days_since
from calendar t1
left join aggregated_result t2
using (date)

more on creating calendar table: How do I create a dates table in Redshift?

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You need to generate the dates. In Postgres, generate_series() is the way to go:

select g.ts as dte,
       count(distinct t.user_id) as user_id_count
       row_number() over (order by signup_day desc) - 1) as days_since
from generate_series(current_date::timestamp - interval '3 day', current_date::timestamp, interval '1 day') g(ts) left join
     data.table t
     on t.timestamp::date = g.ts
group by t.ts
order by t.ts;

Upvotes: 2

Related Questions