MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE my_table (
	name VARCHAR(10),
	tier_1 VARCHAR(10),
	tier_2 VARCHAR(10)
);
INSERT INTO my_table (name,tier_1,tier_2) VALUES ('A','x','z'),
 ('B','y','y'),
 ('C',NULL,'y'),
 ('A','y','y'),
 ('A','y','x'),
 ('C','z','x'),
 ('A',NULL,'y'),
 ('C','x',NULL),
 ('B','x','z'),
 ('A','z','x'),
 ('A','y',NULL);

Records: 11  Duplicates: 0  Warnings: 0


WITH cte AS (
  SELECT *,
         COUNT(tier_1) OVER (PARTITION BY name, tier_1) counter1,
         COUNT(tier_2) OVER (PARTITION BY name, tier_2) counter2  
  FROM my_table
)
SELECT name,
       COALESCE(tier_1, FIRST_VALUE(tier_1) OVER (PARTITION BY name ORDER BY counter1 DESC)) tier_1,
       COALESCE(tier_2, FIRST_VALUE(tier_2) OVER (PARTITION BY name ORDER BY counter2 DESC)) tier_2
FROM cte;


name
tier_1
tier_2


A
y
x

A
y
y

A
z
x

A
y
y

A
x
z

A
y
x

B
y
y

B
x
z

C
z
x

C
x
y

C
x
x


WITH cte AS (
  SELECT *,
         COUNT(tier_1) OVER (PARTITION BY name, tier_1) counter1,
         COUNT(tier_2) OVER (PARTITION BY name, tier_2) counter2  
  FROM my_table
)
SELECT name,
       COALESCE(tier_1, FIRST_VALUE(tier_1) OVER (PARTITION BY name ORDER BY counter1 DESC, tier_1)) tier_1,
       COALESCE(tier_2, FIRST_VALUE(tier_2) OVER (PARTITION BY name ORDER BY counter2 DESC, tier_2)) tier_2
FROM cte;


name
tier_1
tier_2


A
y
x

A
z
x

A
y
y

A
y
y

A
x
z

A
y
x

B
y
y

B
x
z

C
z
x

C
x
y

C
x
x