CODEWITHSUNDEEP
sqlpostgresql
Michi
Michi

Reputation: 5471

Assign a certain country to all orders of a user if the user placed at least one order in a specific other country

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  |  country  |     customer    |
------------|-----------|-----------------|--------------------------------------
2020-04-10  |     DE    |      user_01    |
2020-04-11  |     DE    |      user_01    |
2020-04-13  |     DE    |      user_01    |  --> country changed to DE
------------|-----------|-----------------|---------------------------------------
2020-04-20  |     AT    |      user_02    |
2020-04-22  |     AT    |      user_02    |
2020-05-19  |     DE    |      user_03    |
------------|-----------|-----------------|---------------------------------------
2020-06-17  |     DE    |      user_04    |  --> country changed to DE
2020-06-23  |     DE    |      user_04    |  --> country changed to DE
2020-07-04  |     DE    |      user_04    |
------------|-----------|-----------------|---------------------------------------
2020-08-19  |     NL    |      user_05    |
2020-10-23  |     FR    |      user_05    |

If a user placed an order in DE and AT then DE should be assigned to all orders of the user (e.g. user_01, user_04).

I have not clue how I have to change this query to achieve this:

SELECT
c.order_date,
c.country,
c.customer
FROM customers c
GROUP BY 1,2,3
ORDER BY 1,2,3;

Do you have any idea?

Upvotes: 0

Views: 51

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

Use window functions!

select . . . ,
       (case when count(*) filter (where country = 'DE') over (partition by customer) > 0
             then 'DE' else country
        end) as country
from customer c;

Upvotes: 1

Stu
Stu

Reputation: 32609

You can do this a few ways, one is to check if the customer has any other row for country 'DE' and use that if so:

SELECT c.order_date, 
  coalesce( case when exists (
    select * from customers c2 
    where c2.customer=c.customer and c2.country='DE'
  ) then 'DE' end, c.country) Country, 
c.customer
FROM customers c
GROUP BY 1,2,3
ORDER BY 1,2,3;

DB Fiddle

Upvotes: 0

Related Questions