Get max and min value's unique id while using group by

I have a table with columns: unique_id, product, root_location, price with more than 50 million records

I want the result to be product, min_price, min_price_unique_id, max_price, max_price_unique_id

My query:

select product
    , min(price) as min_price
    , max(price) as max_price
from mytable
group by product

How to get the unique id's of min and max price?

You could try using RANK and STRING_AGG with conditional aggregation as the following:

SELECT  product,
        MIN(price) AS min_price,
        STRING_AGG(CASE WHEN rn1 = 1 THEN unique_id END, ',') min_price_unique_id,
        MAX(price) AS max_price,
        STRING_AGG(CASE WHEN rn2 = 1 THEN unique_id END, ',') max_price_unique_id
FROM
(
  SELECT *,
    RANK() OVER (PARTITION BY product ORDER BY price) rn1,
    RANK() OVER (PARTITION BY product ORDER BY price DESC) rn2
  FROM tbl_name
) T
WHERE rn1 =1 OR rn2 =1
GROUP BY product

demo

Update:
To get the unique donator_ids values in case duplicates have existed, you could use another subquery/ CTE, and use the row_number function partitioned by product, unique_id then get only rows where row_number =1.

WITH CTE1 AS
(
  SELECT *,
    RANK() OVER (PARTITION BY product ORDER BY price) rn1,
    RANK() OVER (PARTITION BY product ORDER BY price DESC) rn2
  FROM tbl_name
),
CTE2 AS
(
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY product, unique_id ORDER BY unique_id) row_num
  FROM CTE1
)
SELECT  product,
        MIN(price) AS min_price,
        STRING_AGG(CASE WHEN rn1 = 1 THEN unique_id END, ',') min_price_unique_id,
        MAX(price) AS max_price,
        STRING_AGG(CASE WHEN rn2 = 1 THEN unique_id END, ',') max_price_unique_id
FROM CTE2
WHERE (rn1 =1 OR rn2 =1) AND  row_num =1
GROUP BY product

demo

select a.*, b.unique_id as min_price_unique_id, c.unique_id as max_price_unique_id
from (
  select product
    , min(price) as min_price
    , max(price) as max_price
  from mytable
  group by product
) a
join mytable b
  on a.min_price = b.price
join mytable c
  on a.max_price = c.price

This query uses two joins to the original table mytable to get the unique ids of the minimum and maximum prices. 1. The subquery calculates the max and min price for each product and the subquery a is joined with the original table mytable twice. Once to get the unique id of the minimum price and then to get the unique id of the maximum price. The join condition for both joins is the price column.

db<>fiddle

It really depends on what you want with the unique_id. In the situation where there are multiple unique_id for the min or max price, do you want to return all unique_id or just only one.

Depending on your requirement, use row_number() or dense_rank(). Do check out the documentation for the details on these 2 window functions.

Below query will return One unique_id per product.

select product,
       min_price           = max(case when min_rn = 1 then price end),
       min_price_unique_id = max(case when min_rn = 1 then unique_id end),
       max_price           = max(case when max_rn = 1 then price end),
       max_price_unique_id = max(case when max_rn = 1 then unique_id end)
from
(
    select *, 
           min_rn = row_number() over (partition by product order by price),
           max_rn = row_number() over (partition by product order by price desc)
    from   mytable
) t
group by product

Edit : if you do want to show all the corresponding unique_id for the min and max price, here is one way. Use 2 separate query to identify the min and max price after that join using full outer join (as there might be different number of unique_id rows in the min or max price)

with 
min_price as
(
    select  product, min_price = price, unique_id, 
            rn = row_number() over (partition by product order by unique_id)
    from
    (
        select *, 
               min_rn = dense_rank() over (partition by product order by price)
        from   mytable
    ) m
    where min_rn = 1
),
max_price as
(
    select  product, max_price = price, unique_id, 
            rn = row_number() over (partition by product order by unique_id)
    from
    (
        select *, 
               max_rn = dense_rank() over (partition by product order by price desc)
        from   mytable
    ) m
    where max_rn = 1
) 
select mn.product, mn.min_price, mn.unique_id, mx.max_price, mx.unique_id
from   min_price mn
       full outer join max_price mx on  mn.product = mx.product
                                    and mn.rn      = mx.rn
order by mn.product, mn.unique_id, mx.unique_id