Spread the amount to all rows when a condition is met

Question

I have a dataset as below:

date          country       price       product
20210101       US              5        laptop
20210101       Italy          10        laptop
20210201       Australia      15        laptop
20210301       France         10        monitor
20210301       US             15        monitor

What I would like to do is to add another column that always takes the original price of the US when product names are the same across all the countries. The desired output should be:

date          country       price       product       original_price
20210101       US              5        laptop             5
20210101       Italy          10        laptop             5
20210201       Australia      15        laptop             5
20210301       France         10        monitor            15
20210301       US             15        monitor            15

I was thinking of using conditional logic such as below but it's not working:

COALESCE
(MAX(CASE WHEN country = 'US' THEN price END),
 MAX(CASE WHEN country != 'US' THEN price END)) 

If anyone could give me a hint on a method or an approach to tackle this, it would be highly appreciated.

Answer 1

For MySql 8.0+ you can use MAX() window function:

SELECT *, 
       MAX(CASE WHEN country = 'US' THEN price END) OVER (PARTITION BY product) original_price
FROM tablename;

For previous versions use a correlated subquery:

SELECT t1.*, 
       (SELECT t2.price FROM tablename t2 WHERE t2.product = t1.product AND t2.country = 'US') original_price
FROM tablename t1;

See the demo.