CODEWITHSUNDEEP
sqlamazon-redshift
Michi
Michi

Reputation: 5471

REDSHIFT: Assign value in column to all rows based on a certain value in another column

DB-Fiddle

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    order_date DATE,
    country VARCHAR(255),
    customer VARCHAR(255)
);

INSERT INTO customers
(order_date, country, customer)
VALUES 
('2020-04-10', 'DE', 'user_01'),
('2020-04-11', 'DE', 'user_01'),
('2020-04-13', 'AT', 'user_01'),

('2020-04-20', 'AT', 'user_02'),
('2020-04-22', 'AT', 'user_02'),

('2020-05-19', 'DE', 'user_03'),

('2020-06-17', 'AT', 'user_04'),
('2020-06-23', 'AT', 'user_04'),
('2020-07-04', 'DE', 'user_04'),

('2020-08-19', 'NL', 'user_05'),
('2020-10-23', 'FR', 'user_05');

Expected Result:

order_date   |   customer  |  first_country   |   second_country  | 
-------------|-------------|------------------|-------------------|-----------
2020-04-10   |   user_01   |       DE         |         DE        |
2020-04-11   |   user_01   |       DE         |         DE        |
2020-04-13   |   user_01   |       AT         |         DE        |
-------------|-------------|------------------|-------------------|-----------             
2020-04-20   |   user_02   |       AT         |        NULL       |
2020-04-22   |   user_02   |       AT         |        NULL       |
-------------|-------------|------------------|-------------------|-----------          
2020-05-19   |   user_03   |       DE         |        NULL       | 
-------------|-------------|------------------|-------------------|----------- 
2020-06-17   |   user_04   |       AT         |         DE        |
2020-06-23   |   user_04   |       AT         |         DE        |
2020-07-04   |   user_04   |       DE         |         DE        |
-------------|-------------|------------------|-------------------|----------- 
2020-08-19   |   user_05   |       NL         |        NULL       |
2020-10-23   |   user_05   |       FR         |        NULL       |

In the above example it can happen that there is one customer that ordered both in DE and AT.
For those customers I want to assign the country DE in the column second_country to each of their order_dates.

With reference to the answer from this question I tried to go with this:

SELECT
c.order_date AS order_date,
c.customer AS customer,
c.country AS first_country,

(CASE WHEN COUNT(*) FILTER (WHERE c.country = 'DE') OVER (PARTITION BY c.customer) > 0 AND
           COUNT(*) FILTER (WHERE c.country = 'AT') OVER (PARTITION BY c.customer) > 0
      THEN 'DE'
      END) AS second_country
        
FROM customers c

GROUP BY 1,2,3
ORDER BY 1,2,3;

However, in redshift the FILTER function is not available.
How do I have to modify the query to make it also work in redshift?

Upvotes: 1

Views: 826

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Just use case expressions:

SELECT c.order_date AS order_date,
       c.customer AS customer,
       c.country AS first_country,
       (CASE WHEN SUM(CASE WHEN c.country = 'DE' THEN 1 ELSE 0 END) OVER (PARTITION BY c.customer) > 0 AND
                  SUM(CASE WHEN c.country = 'AT' THEN 1 ELSE 0 END) OVER (PARTITION BY c.customer) > 0
             THEN 'DE'
         END) AS second_country        
FROM customers c
GROUP BY 1,2,3
ORDER BY 1,2,3;

Actually, you can simplify this to:

       (CASE WHEN SUM( (c.country = 'DE')::int ) OVER (PARTITION BY c.customer) > 0 AND
                  SUM( (c.country = 'DE')::int ) OVER (PARTITION BY c.customer) > 0
             THEN 'DE'
        END) AS second_country

Upvotes: 1

Related Questions