Redshift SQL statement that will return 1 or 0 if the select statement returns any rows

user_13278605 0
Viewed 595

I have the following select statement in Redshift that will return rows with certain values if the condition inside is met. I want to transform this into a DQ check which will return 1 (True) if no rows ae returned or 0 if any row is returned, but I do not know where I should apply the case statement. Here is the select statement:

 select * from (select brand,calendar_dt, product,
 count(account) count from revenue_base 
 where player_days = 0 and volume_loc >0 group by brand,calendar_dt, product) 
 where count > 1000 and calendar_dt >='2020-07-12'
 and calendar_dt < '2020-07-13'

Can you please offer me some ideas for this?

amazon-redshift case select sql
2 Answers

You may try using exists logic here:

select
    case when not exists (
        select 1 from
        (
            select brand, calendar_dt, product, count(account) as count
            from revenue_base 
            where player_days = 0 and volume_loc > 0
            group by brand, calendar_dt, product
        ) t
        where calendar_dt >= '2020-07-12' and calendar_dt < '2020-07-13' and
              count > 1000
    )
         then 1 else 0 end as result;
user_18632290

First, Redshift supports booleans, so case is not needed. Second, do the filtering on the date before the aggregation. This is usually faster.

Then, you can filter by the count using a having clause, so no subquery is needed:

select not exists (select 1
                   from revenue_base 
                   where player_days = 0 and volume_loc > 0 and
                         calendar_dt >= '2020-07-12' and calendar_dt < '2020-07-13'
                   group by brand, calendar_dt, product
                   having count(*) > 1000
                  ) as result
user_11440350
Related