As much as GoT has shit the bed in the later seasons, the intro sequence including the title theme was always (and still is) amazing!
It never failed to hype me up!
House is a great one, but only the Teardrop version. It hit the perfect mood for the serious tone of the medical subjects. It also makes me weirdly nostalgic for watching that in the mid-00’s with my family late at night
The intro for Scavengers Reign is the absolutely perfect complement to the show. Ethereal, melancholic, and tense, just like the atmosphere of the story. Also it's super short. Watched it every episode
Itay not be the opening song or sequence, but the opening scene in the umbrella academy were always amazing when they did a song/dance number. Their soundtrack spanning the whole series were full of bangers... But the best has to be season three episode one, the dance off with the sparrow academy.
I’m a huge fan of shows that just have a title splash screen and play the intro credits unobtrusively over the first 1/3 of the show.
I always skip intros, if I can, mostly because I binge stuff and those intros can be minutes long, serving absolutely no purpose. I especially hate when they are 10+ minutes into the episode, and I have to wake my keyboard up to skip them.
Veronica Mars (just the perfect match for the show's vibe and a damn catchy song)
Fringe (loved how they used it later in the series to match the universe/time period the episode was set in)
True Blood (a classic, wish the show matched its atmosphere beyond season 1)
Jessica Jones (so underrated)
Severance (extremely creative and memorable)
Stranger things (hate the show, but the intro is undeniably brilliant in its simplicity)
Honorable mentions:
Yellowjackets (fun!)
Dexter (love the concept, hate the song)
The 100 (after a basic title card in season 1 they actually added an intro for the rest of the show, with the content changing to match the theme and setting of each season)
I can't really think if any "worst" ones although in general I don't like when shows only do static or just very low-effort title cards. Some examples that come to mind are Breaking Bad and The Boys.
I’ve always thought that a bad intro song tanks a show (back when you couldn’t skip them). Conversely, a good one makes people stick around and give the show a chance, so it’s more likely to succeed.
Re: the OP question:
Magnum PI (OG)
SNL intro music
30ROCK
Friends (ugh, I know, it was catchy…)
Venture Bros.
Archer
Futurama
…and more!
Your Lie In April - it has the entire plot of the anime in the intro, if you know what you're looking for. But if you don't know what to look for, it is totally innocuous. Also the song SLAPS