Some notable examples:
- The Godfather Part II
- The Two Towers
- The Empire Strikes Back
- Terminator 2
- The Dark Knight
- Spiderman 2
- Bourne Supremacy
It's interesting, to start off, I'd like to note that I agree with every example except one, which I don't necessarily disagree with, but where I can't say I fully agree either -- I think The Dark Knight and The Dark Knight Rises are in a tight fight for the title of best of that series, although sometimes I do feel TDK is overall the best while other times I rank TDKR as the best by a hair. But it's a fair example to consider for the purposes of the question, regardless.
There's also X2: X-Men United, Aliens, Blade II, The Road Warrior, Adams Family Values, and Evil Dead 2, just to name some others.
If I can speak just broadly about franchises in general, not just trilogies, the point gets toward the main answer to this question, so please bear with me a moment ...
Consider all of the franchises that exist. How many could you name? Probably a lot more than the ones we've listed so far, right? Probably a few dozen, even. So really, there are a lot more where the first film was the best, or where the third or later film might've been the best, right? Think about horror franchises, and the fact that rarely are the sequels ever as good as the first, for example. And there are a LOT of horror franchises.
For Star Wars, Lord of the Rings, Terminator, and Bourne, there are other films in the series, even though there are also valid reasons to perceive the first three film as a trilogy apart from the rest. Meaning with franchises, our concept of what constitutes a trilogy is not absolute. We could say, for example, that the first two films in the Halloween horror series were a set together, since the third film left behind the original established story for a brand new, unrelated story. Eventually, however, the franchise returned to its earlier popular characters. Some fans consider the first two films apart from the rest, others don't.
The point being, when we talk about trilogies and whether the second film is very often the best in a trilogy, we have to make room for the possible inclusion of franchises that might really have more than just three films, and so the number of trilogies under consideration might differ from one person to another.
There are in fact other franchises that were more than three films, where the second film was still the best of the original longer series. Consider Star Trek II: The Wrath of Khan, Bride of Frankenstein, and Dawn of the Dead for example.
I think what often happens in franchises is, the first film establishes the concepts and characters, and then everyone involved -- including the audience -- have a familiarity with the story's world that is still fresh and that is past the stage of figuring out what's what. The actors know the roles better, the world has just opened itself up to other possibilities for the first time, and the energy level off the success of a first film establishing a franchise all helps make the next film smoother, more exciting, and a familiar brand that audiences are eager to revisit for the first time to see what's new. The learning curve of the first film doesn't exist, the unfamiliarity doesn't exist, and there's less concern about trying to work in things to appeal to the audience out of uncertainty over how people will respond to the story and characters. More freedom, early excitement, familiarity without redundancy -- these things all combine to work in favor of the very first sequel produced in a series, both in terms of the production and in terms of audience perceptions.
Imagine you perform a magic trick for the first time. Everyone is wowed, they didn't expect it, and they ask you to do it again. You see their smiles and excitement, you know you impressed them, and you're eager to get that reaction a second time. So you do it, and this time they were watching and expecting it and their anticipation and the newness of it is still fresh enough and exciting enough that they "wow" and "ah" again. But then imagine they ask you to do it yet AGAIN. This time, you know they'll be paying much more attention, they've seen it once without expectation and a second time to really pay attention, so now they'll be looking for HOW you did it, and it also won't be as amazing as when they didn't expect it or when they saw it repeated with only the newness of first expectation. Each time you're asked to repeat it will remove the luster, and you'll be going through the motions without the same level of inspiration and intensity.
I think this is similar to the dynamic between franchises and audiences, in some general ways.
Also, think for a moment about the way sequels are created. A first film is made, and it's a hit, so the studio looks around and brings in top talents, ups the budget, ups the marketing, and all of this happens alongside the above-mentioned elements regarding newness and excitement etc. So, the second film has a bigger budget, often a more impressive set of talent involved, and comes out amid the very first example of the franchise's bigger marketing push. It's all new and doesn't feel as cynical or commodified or repetitious or unoriginal as it will later with each successive film.
Lastly, when a filmmaker creates a first film, they often have ideas that they leave out or that come to mind from this first encounter with the new creations. After a second film, however, it is often the case that a third film springs more from demand than from the same early creative processes. And of course, once a cinematic world's foundation has been laid, that opens the door to new opportunities to explore things and perhaps allows more room to imagine new tales and new ways to tell them, but after that second act of creation the success of the first two films now brings about pressure to replicate the success and so the desire or necessity of relying on backward glances and repeating itself in order to avoid "rocking the boat" can set in and lead to subsequent films that lack the spark of originality and excitement of earlier efforts.
Now, consider all of those examples of different reasons that later films don't measure up and/or that the first film isn't as grand or reaching. Now consider how these can mix and match with one another. And now consider that sometimes one of these things or pairs of things are true in a franchise, and sometimes another set of things are true, etc. Meaning there are many examples of ways things can go very right for second films but very wrong or "less right" for later films.
So it happens that trilogies and franchises can suffer from one or more elements that cause the second film to be the best of the trilogy or series. But not always, just sometimes and due to many factors that can play a role.
Since those things happen at different times, and since even if they don't happen so much that it leads to a second film being clearly "the best" but only "nearly as good" etc, then we can get the impression that it happens more often than it perhaps really does. Very often, franchises are just all equally "okay" or outright bad, and very often only the first film is really any good. Sometimes, all of the films are great, or the series gets better as it goes along. But when a good first film is followed by a magically great film, we take firm notice. And we're more likely to notice than when a first film is great but sequels all suck (we take that for granted a lot of the time, and so it doesn't stand out in our minds as very noticeable), nor do we notice when there is a trend of each successive film being better than the last (since it's not a case where we can single out time and again film #-whatever as constantly the best). Quite often, if a franchise reaches three films, then there will end up being a fourth and so on.
But when there is a second film that stands out among many or just three in a series, that really catches our attention and stands out to us, giving us a somewhat false sense that it happens more often than it really does and/or that it is more meaningful in a storytelling sense than it usually is.
There are also points about trilogies as an organic whole in which some storytellers have trouble with the final Act of their narratives, or points about how often franchises eventually lose the original creative teams by the time a third film rolls around, etc. But I think the previous points are the main ones to consider.