Input Data
CarrierID Connection ID Flight Date Price
UA 1750 10.02.2024 250
UA 1890 07.04.2024 450
UA 2760 07.04.2024 150
UA 1970 07.04.2024 100
AA 3490 11.04.2024 340
AA 1960 13.04.2024 560
SQ 3490 15.05.2014 120

Output Data
Carrier ID Flight Date
UA 07.04.2024
AA 11.04.2024
AA 13.04.2024
SQ 15.05.2014

Need to get above result as output. Let me explain As 07.04 CarrierId has highest trip so it should be the out put in stead of 10.02. All other entries has single entry based on date so it wiil be in output .

I tried to create two CDS View to Solve the issue but I can get Busiest CarrierId without Date in Output. But requirement is to display the Date with CarrierID.

I Tried to find output with below view

define view entity View1 as select from /dmo/booking
{
key carrier_id as CarrierId,
flight_date as FlightDate,
count(*) as FlightCount

}
group by
carrier_id,flight_date

define view entity View2 as select from View1
{
key CarrierId as CarrierId,
FlightDate as FlightDate,
max(FlightCount) as Numberofflight
}group by CarrierId

This view is not working as we need to add Non aggregate Fields in Group By Clause But if i add FlightDate in GroupBy then output will be different .