Redshift - Case statement returns duplicates

Question

I have a dataset that has product name, order number and the time order was placed.

prod_name,order_no,order_time
a,101,2018-05-01
a,102,2018-06-04
a,103,2018-05-03
b,104,2018-01-21
b,105,2018-01-11

I am trying to build a report that shows time since first order (compared against current time) with an output as below:

prod_name,time_since_first_sale,aging
a,64,Less than 3 months back
b,177,Less than 6 months back

Given below is the SQL I am using:

select DISTINCT b.prod_name,case when((CURRENT_TIMESTAMP - min(a.order_time))) < '90'  THEN 'Less than 3 months'
                               when ((CURRENT_TIMESTAMP - min(order_time))) < '180'  THEN 'Less than 6 months' 
                               else 'Other' end as aging
                               from sales a, prod b where a.id=b.prod_id;

The above SQL when executed returns duplicates, believe it also considers each sale_id in the sales table. How could I modify the above query to get just one record per prod_name. If I however remove the case statement the duplicates are not there. Could any one assist as to what I am doing wrong that pulls in these duplicates.

I am using Amazon Redshift DB.

Thanks..

Gordon Linoff Gordon Linoff · Accepted Answer · 2018-07-04T11:29:00

Never use commas in the FROM clause. Always use proper, explicit, standard JOIN syntax.

Don't use SELECT DISTINCT when you intend GROUP BY.

So your query should look like:

select p.prod_name,
       (case when CURRENT_TIMESTAMP - min(s.order_time) < '90'  
             then 'Less than 3 months'
             when CURRENT_TIMESTAMP - min(s.order_time) < '180' then 'Less than 6 months' 
             else 'Other'
        end) as aging
from sales s join
     prod p
     on s.id = p.prod_id
group by p.prod_name;

Notice that I also added in reasonable table aliases (abbreviations for the table names) and qualified all column references.

Redshift - Case statement returns duplicates

1 Answers