Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE info (
  brand VARCHAR(255),
  segment VARCHAR(255),
  name VARCHAR(255)
);

INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'SUV', 'Highlander');
INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'SUV', 'Highlander');
INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'SUV', 'Highlander');
INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'SUV', '4Runner');
INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'SUV', 'RAV4');
INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'SUV', 'RAV4');
INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'Sedan', 'Camry');
INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'Sedan', 'Camry');
INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'Sedan', 'Corolla');
INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'Sedan', 'Corolla');
INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'Sedan', 'Corolla');
INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'Truck', 'Tacoma');
INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'Truck', 'Tundra');
INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'Truck', 'Tacoma');
 INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'Van', 'Sienna');
INSERT INTO info (brand, segment, name) VALUES ('x', 'y', 'z');
INSERT INTO info (brand, segment, name) VALUES ('x', 'y', 'z');
INSERT INTO info (brand, segment, name) VALUES ('x', 'y2', 'z2');

CREATE TABLE



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


WITH get_grouping_set AS
(
  SELECT brand, segment, name, count(1) AS total
  FROM info
  GROUP BY GROUPING SETS 
  (
    (brand),
    (brand, segment),
    (brand, segment, name)
  )
), 
brand_segment_order AS
(
  SELECT brand, segment,
    ROW_NUMBER() OVER (PARTITION BY brand ORDER BY total DESC) rn_seg
    FROM get_grouping_set
  WHERE segment IS NOT NULL AND name IS NULL
), 
joined_data AS
(
  SELECT T.*,
    ROW_NUMBER() OVER (PARTITION BY T.brand, T.segment ORDER BY T.total DESC) rn 
  FROM get_grouping_set T JOIN brand_segment_order T2
  ON T.brand = T2.brand AND T.segment = T2.segment OR T.segment IS NULL
  WHERE T2.rn_seg <= 2
 )
SELECT brand, segment, name, total  
FROM joined_data 
WHERE (rn = 1 AND segment IS NULL ) OR (rn <= 2 AND segment IS NOT NULL)
ORDER BY brand, MAX(Total) OVER (PARTITION BY brand, segment) DESC, 
         Total DESC, segment NULLS FIRST, name NULLS FIRST


brand
segment
name
total


Toyota
null
null
15

Toyota
SUV
null
6

Toyota
SUV
Highlander
3

Toyota
Sedan
null
5

Toyota
Sedan
Corolla
3

x
null
null
3

x
y
null
2

x
y
z
2

x
y2
null
1

x
y2
z2
1



SELECT 10


WITH get_grouping_set AS
(
  SELECT brand, segment, name, count(1) AS total, 
    MAX(count(*)) over (PARTITION BY brand, segment) max_brand_segment
  FROM info
  GROUP BY GROUPING SETS 
  (
    (brand),
    (brand, segment),
    (brand, segment, name)
  )
),
brand_segment_order AS
(
  SELECT *,
    DENSE_RANK() OVER (PARTITION BY brand ORDER BY max_brand_segment desc) segment_rank,
    DENSE_RANK() OVER (PARTITION BY brand, segment ORDER BY Total DESC) name_rank
    FROM get_grouping_set
)
SELECT brand, segment, name, total 
FROM brand_segment_order
WHERE segment_rank <= 3 AND name_rank <= 2
ORDER BY brand, max_brand_segment DESC, 
         Total DESC, segment NULLS FIRST, name NULLS FIRST


brand
segment
name
total


Toyota
null
null
15

Toyota
SUV
null
6

Toyota
SUV
Highlander
3

Toyota
Sedan
null
5

Toyota
Sedan
Corolla
3

x
null
null
3

x
y
null
2

x
y
z
2

x
y2
null
1

x
y2
z2
1



SELECT 10
brand	segment	name	total
Toyota	null	null	15
Toyota	SUV	null	6
Toyota	SUV	Highlander	3
Toyota	Sedan	null	5
Toyota	Sedan	Corolla	3
x	null	null	3
x	y	null	2
x	y	z	2
x	y2	null	1
x	y2	z2	1