First region by Earliest Date per Customer (Snowflake)-CodePudding

I have this sample dataset and would like to display customer_name, region, and order date.

The issue with my dataset is that the customer has multiple regions and multiple order dates. I would like to see only the customer name, first region per order (If the first order was in US East then US East ?), and all order date

Customer 1, US East, 2021-12-10

Customer 1, US West, 2022-07-26

result>

Customer 1, US East, 2021-12-10

Customer 1, US East, 2022-07-26

select 
'Customer 1','US East', '2021-12-10'
union 
select 
'Customer 1','US West', '2022-07-26'
union 
select 
'Customer 2','Europe West', '2021-01-26'
union 
select 
'Customer 2','Europe', '2020-01-26'

CodePudding user response：

Using FIRST_VALUE:

SELECT Customer, FIRST_VALUE(Region) OVER(PARTITION BY Customer 
                                           ORDER BY date) AS region
       ,Date
FROM tab