SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE [dbo].[Passenger](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[PreFix] [varchar](50) NOT NULL,
	[PgrID]  AS ([Prefix]+right('00000000'+CONVERT([varchar](7),[ID]),(7))) PERSISTED NOT NULL,
	[PgrName] [varchar](50) NOT NULL,
	[PgrEmail] [nvarchar](255) NULL,
	[CabinNo] [varchar](50) NOT NULL,
	[PgrDOB] [date] NOT NULL,
	[PgrGender] [char](1) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[PgrID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[Passenger]  WITH CHECK ADD CHECK  (([PgrGender]='M' OR [PgrGender]='F'))

CREATE TABLE [dbo].[PassengerOrder](
	[PgrID] [varchar](57) NULL,
	[DishID] [varchar](57) NULL,
	[OrderQty] [tinyint] NOT NULL,
	[OrderDateTime] [datetime] NOT NULL,
	[DeliverTo] [tinyint] NOT NULL,
	[DelDateTime] [datetime] NOT NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[PassengerOrder]  WITH CHECK ADD FOREIGN KEY([PgrID])
REFERENCES [dbo].[Passenger] ([PgrID])

INSERT INTO [dbo].[Passenger]
           ([PreFix]
           ,[PgrName]
           ,[PgrEmail]
           ,[CabinNo]
           ,[PgrDOB]
           ,[PgrGender])
     VALUES
           ('pgr',	'Baldwin Domenici'	,'ccc'	,1	,'1999-02-06',	'M'),
		   ('pgr',	'Baldwin Domenici'	,'ccc'	,1	,'2009-02-06',	'M'),
		   ('pgr',	'Baldwin Domenici',	'ccc'	,1	,'2019-02-06',	'M'),
		   ('pgr',	'Baldwin Domenici'	,'ccc'	,1	,'1950-02-06',	'M'),
		   ('pgr',	'Baldwin Domenici'	,'ccc'	,1	,'1957-02-06',	'M'),
		   ('pgr',	'Baldwin Domenici'	,'ccc'	,1	,'1989-02-06',	'M'),
		   ('pgr',	'Baldwin Domenici'	,'ccc'	,1	,'2000-02-06',	'M'),
		   ('pgr',	'Baldwin Domenici'	,'ccc'	,1	,'2011-02-06',	'M')

INSERT INTO [dbo].[PassengerOrder]
           ([PgrID]
           ,[DishID]
           ,[OrderQty]
           ,[OrderDateTime]
           ,[DeliverTo]
           ,[DelDateTime])
     VALUES
            ('pgr0000001'	,'dsh0000001',	1,	'2022-01-28 00:29:09.000'	,1	,'2022-01-28 00:59:09.000'),
		    ('pgr0000003'	,'dsh0000001',	1,	'2022-01-28 00:29:09.000'	,1	,'2022-01-28 00:59:09.000'),
			('pgr0000002'	,'dsh0000001',	11,	'2022-01-28 00:29:09.000'	,15	,'2022-01-28 00:59:09.000'),
			('pgr0000004'	,'dsh0000001',	19,	'2022-01-28 00:29:09.000'	,1	,'2022-01-28 00:59:09.000'),
			('pgr0000005'	,'dsh0000001',	91,	'2022-01-28 00:29:09.000'	,1	,'2022-01-28 00:59:09.000'),
			('pgr0000006'	,'dsh0000001',	1,	'2022-01-28 00:29:09.000'	,15	,'2022-01-28 00:59:09.000'),
			('pgr0000006'	,'dsh0000001',	41,	'2022-01-28 00:29:09.000'	,1	,'2022-01-28 00:59:09.000'),
			('pgr0000008'	,'dsh0000002',	1,	'2022-01-28 00:29:09.000'	,1	,'2022-01-28 00:59:09.000')
			
			
			
    ;WITH cte AS
    (
      SELECT v.range, 
       COUNT(*) AS occurrences,
       COUNT(DishID) * SUM(OrderQty) AS TotalOrders,
       po.DishID,
       rn = ROW_NUMBER() OVER (PARTITION BY v.range 
            ORDER BY COUNT(DishID) * SUM(OrderQty) DESC)
      FROM 
      ( SELECT PgrId,
        CASE WHEN p.PgrDOB < DATEADD(YEAR, -40, GETDATE()) THEN '40+'
             WHEN p.PgrDOB < DATEADD(YEAR, -30, GETDATE()) THEN '30-39'
             WHEN p.PgrDOB < DATEADD(YEAR, -20, GETDATE()) THEN '20-29'
             WHEN p.PgrDOB < DATEADD(YEAR, -10, GETDATE()) THEN '10-19'
             ELSE '0-9'
        END AS range
        FROM dbo.Passenger p 	
     ) v inner join dbo.PassengerOrder po on v.PgrId  = po.PgrID	
     GROUP BY v.range,po.DishID	
    )
    SELECT range, occurrences, TotalOrders, DishID
      FROM cte 
      WHERE rn = 1
      ORDER BY TotalOrders DESC;

range	occurrences	TotalOrders	DishID
40+	2	220	dsh0000001
30-39	2	84	dsh0000001
10-19	1	11	dsh0000001
20-29	1	1	dsh0000001
0-9	1	1	dsh0000001