Let's say you have a story of a kid who wants to become a F1 champion. You know the ending from the very start.
Why should every story end with the protagonist achieving their goal?
I don't know what a F1 champion is, but presumably only a minority of F1 contestants become champions. Therefore, in real life most F1 contestants want to become F1 champions but fail.
So a realistic story can depict a kid who wants to become an F1 champion but fails to achieve it.
And if such a story needs to be uplifting or inspirational, it can have the protagonist achieve something else worthwhile.
And sometimes the story doesn't exactly have to be inspirational.
Nancy Kerrigan (b. 1969) and Tonya Harding (b. 1970) were both girls who dreamed of being figure skating champions and wining gold medals in the winter Olympics. In their final Olympics in 1994 Harding finished 8th and Kerrigan 2nd, so they never got the gold medals they wanted.
But there was a 1994 TV movie Tonya and Nancy: The inside story about them, and documentaries in 2014 The Price of Gold and Nancy and Tonya, and the 2017 film I, Tonya.
So people have managed to tell interesting stories about those two athletes who never achieved their highest ambition, winning the gold.
George Armstrong Custer has appeared in many movies and TV shows.
In most he is an secondary character, and sometimes a villain as in Sitting Bull (1954) and Little Big Man (1970).
Custer is the protagonist in They Died with Their Boots On (1941), Custer of the West (1967), and Son of the Morning Star (1991) so I guess they count as tragedies. The creators of They Died with Their Boots On really twisted historical facts to come up with a way to make Custer's defeat and death a kind of victory for him.
And in the movie Chief Crazy Horse (1955) the protagonist fails to achieve many of his goals.
So those are just a few examples of stories where the main characters fail to achieve their goals, and yet their stories are told anyway.
Added Nov. 12 2023. A popular writer in the early 20th century, Lord Dunsany, often ended his short stories with the protagonists getting killed.