CODEWITHSUNDEEP
sqlpostgresql
Michi
Michi

Reputation: 5471

List users unique although they appear multiple times in different countries

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-15', 'AT', 'user_01'),
('2020-04-18', 'DE', 'user_02'),
('2020-04-22', 'DE', 'user_03'),
('2020-05-18', 'DE', 'user_04'),
('2020-05-26', 'AT', 'user_04'),
('2020-05-28', 'DE', 'user_05'),
('2020-06-03', 'AT', 'user_05'),
('2020-06-10', 'AT', 'user_06'),
('2020-06-15', 'AT', 'user_06'),
('2020-06-23', 'AT', 'user_07');

Exptected Result:

order_date country customers
2020-04-10 DE user_01
2020-04-18 DE user_02
2020-04-22 DE user_03
2020-05-18 DE user_04
2020-05-28 DE user_05
2020-06-10 AT user_06
2020-06-23 AT user_07

In the expected result I want to list the unique user based on the following conditions:

  1. If a user appears mutliple times it should only be displayed on the first date it appears in DE. If there is only an order_date in AT it should appear on the first date it appears in AT.

  2. If a user appears both in DE and AT it should only be displayed for DE in column country.
    (e.g. user_01, user_04, user_05)

So far I came up with this query:

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

However, I have no clue how I have to change this query to get the expected result?

Upvotes: 0

Views: 112

Answers (3)

Serg
Serg

Reputation: 22811

You can use row_number. Country takes a priority over Date.

select order_date, country, customer
from (
  select
    c.order_date,
    c.country,
    c.customer,
      row_number() over(partition by c.customer order by case when c.country ='DE' then 1 else 2 end, c.order_date) rn 
  from customers c
  ) t
where rn = 1;

db<>Fiddle

Note I changed a line for user_06 to ('2020-06-15', 'DE', 'user_06') to demonstrate a priority.

Upvotes: 0

forpas
forpas

Reputation: 164099

Use conditional aggregation:

SELECT COALESCE(MIN(CASE WHEN country = 'DE' THEN order_date END), MIN(order_date)) order_date, 
       COALESCE(MIN(CASE WHEN country = 'DE' THEN country END), 'AT') country, 
       customer
FROM customers
GROUP BY customer
ORDER BY customer

See the demo.

Upvotes: 1

Stu
Stu

Reputation: 32609

Working just for your specific sample data, you can group by just the Customer column and appropriately aggregate the others:

SELECT
min(order_date) Order_Date,
max(country) Country,
customer
FROM customers
GROUP BY customer
ORDER BY customer;

See Fiddle

Upvotes: 0

Related Questions