SQL query for a specific scenario

@Yogita_Gaikwad Kindly share the Excel file, it will help us to provide better solution