SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table1 (`item` TEXT, `week` INTEGER, `sales` INTEGER);
insert into table1 values
('ice cream', 1, 17),
('cupcake', 1, 25),
('cookie', 1, 22),
('ice cream', 2, 20),
('cupcake', 2, 20),
('cookie', 2, 2),
('ice cream', 3, 17),
('cupcake', 3, 9),
('cookie', 3, 10),
('ice cream', 4, 10),
('cupcake', 4, 7),
('cookie', 4, 24),
('ice cream', 5, 12),
('cupcake', 5, 4),
('cookie', 5, 120),
('ice cream', 6, 13),
('cupcake', 6, 16),
('cookie', 6, 10),     
('ice cream', 7, 2),
('cupcake', 7, 10),
('cookie', 7, 9), 
('ice cream', 8, 25),
('cupcake', 8, 16),
('cookie', 8, 21);
with t as (
SELECT item,
       CASE 
         WHEN week <= 2 THEN '1-2'
         WHEN week <= 4 THEN '3-4'
         WHEN week <= 5 THEN '5'
         ELSE '>5'
       END weeks,
       SUM(sales) as total_sales
FROM table1
GROUP BY item, 
  CASE 
         WHEN week <= 2 THEN '1-2'
         WHEN week <= 4 THEN '3-4'
         WHEN week <= 5 THEN '5'
         ELSE '>5'
       END ),
  t2 as (
  select item, weeks, total_sales,
  row_number() over (partition by weeks order by total_sales desc) rn  from t)
  select * from t2 where rn = 1 order by weeks


item
weeks
total_sales
rn


cupcake
1-2
45
1

cookie
3-4
34
1

cookie
5
120
1

cupcake
>5
42
1