Which character concepts are less cool to play than they seems ?

the loner. People want to channel Aragorn at the prancing pony but it's just annoying. It's a group game. Play with the group.
the outsider. The one that doesn't fit in. Like you're playing a dungeon crawl so someone makes an accountant with no combat skills. You're playing a game about vampire hunting in Louisiana and someone makes a character that doesn't believe in vampires that's from Spain.
related to the above: the absolute newbie. You're playing a game of vampire focused on intrigue and plots. Someone makes a character that was embraced yesterday. They don't know anything about anything. A constant stream of "we need to drink blood?? You can turn invisible??"". It gets boring real fast. Or, you're playing a sci-fi future game and someone wants to play a 20th century man who was just unfrozen, and doesn't know anything about the 23rd century.

The theme I'm trying to nail down there are characters that don't really engage with the game's premise. They're characters that could exist in the world, but are for this game don't really belong.

I think people see Bilbo from the Hobbit and want to channel that "party boy out of his element" energy but it usually won't work. Or fry from Futurama. You're playing a game not writing a book. Don't take extra spotlight. Don't be incompetent.

It makes sense that people would be drawn to the fish-out-of-water archetype. It is the archetypal protagonist for the "hero's journey" monomyth. The whole idea of the character who is just learning about the world works great for that sort of hero-centric story. But RPGs aren't meant to center around one character, they center on a party. And the stories tend to work better when the characters feel like they are part of the world.
I think people see Bilbo from the Hobbit and want to channel that “party boy out of his element” energy but it usually won’t work. Or fry from Futurama. You’re playing a game not writing a book. Don’t take extra spotlight. Don’t be incompetent.

This is done in movies and books to give the reader/watcher a chance to learn the setting without getting hamfisted about it, stuff is naturally explained to the character because they don't know it either. That's not necessary in TTRPGs, since the player can always ask out of character.

Also, it's a way to get some character into the story the reader/watcher can identify with. This also is not necessary in TTRPGs, since there the players naturally identify with their character (at least in most games, some do that differently).
People like to subvert tropes but don't always understand those tropes and why the purpose they serve, so their subversions don't necessarily serve a counterpurpose.
I dunno. Depends on the group. I played a babylon 5 where I was a doctor with unregistered psychic abilities and was trying my damnedest to be under the radar, low profile, and stay away from anything that might get the spotlight on me. Ended up being beaten and basically running off the station. The dms will find the ways.
If you wanna see the newbie done well, watch LA By Night season 1. Erika Ishii's character is brand newly embraced, while the other main characters are a fair bit more established.
Re: #3 - He doesn’t know about the three shells!?

Any sort of character concept that depends on witholding information from the other players is extremely difficult to do in a satisfying way.

The other players can easily not care about your mystery at all, making your secret just you and the dm making eyebrows at each other. Or they’ll care more than you want, and any sort of long-term intrigue goes out the window as the party drills into your character. Or hell, maybe they’ll be annoyed that you’re being so coy about your character, maybe they’ll find it shifty or frustrating or any of a dozen other things. And even if there’s the perfect level of investment and buy-in from everyone else, it still runs the risk of being a spotlight hog of a character.

So generally it’ll either have absolutely no impact, or it’ll derail the party.

Oh, and all of this goes for double if your secret is that you’re working against the party.

I've found this trope works best when all players know the secret, but the characters don't. If it's a cool, interesting secret, everyone can play into it and enjoy the dramatic irony.
- I had it work masterfully in a limited game (one shot turned into two sessions) where everyone had a secret agenda. I took inspiration from one of the unknown armies one shots, I think.
  
  I think because everyone has their own secret, and didn't know the other people had secrets, it worked out great. It easily could've failed though.

A few thoughts on my side.

The sniper : At a shadowrun game, created a sniper with an interfaced gun, like never miss a target, it's pretty cool, I stay at long range while providing cover to the PC. This turns out in The PC are having fun doing the mission, my character stays out as support, time to play tetris because for the next 2 hours I won't do anything
The Malkavian : If you take the VtM lore as written, it's great you're dangerous, wise, borderline frightening and could explode at any moment. Practically, it's hard to play madness properly, especially on a frightening way, it's even harder when you're the clan without clear "combat discipline" meaning that other PC won't really fear you, and without a GM giving you "secret informations to handle". I tried a few time but never got a satisfying result

I feel like the Malkavians need mechanical solutions for these problems.

On derangements: something like 'you go mad when it's a full moon' is vague. I feel like it'd be easier with a just any system, for example 'renew all Willpower during a full moon, but lose one each scene thereafter', which encourages the player to try just about anything during that night.

Twisting the mechanics also means the player doesn't lose agency by thinking 'oh well, time to act crazy I guess'.

On the combat problem: I feel like this is a symptom of a larger problem with the system. Combat has a system - it has levers everywhere which do things. Nothing else does, and you can't push buttons which aren't there.

I've solved the second problem by replacing Combat rules with general 'Contest' rules -- a single system for Extended and Resisted actions, which works for Investigations, competing companies, or snide remarks at Elysium...and sword fights, if you must.
- ve solved the second problem by replacing Combat rules with general ‘Contest’ rules – a single system for Extended and Resisted actions, which works for Investigations, competing companies, or snide remarks at Elysium…and sword fights, if you must.
  
  This is how Fate works and I like the idea a lot.
  
  I toyed with having social conflict in CofD (close relative to WoD) use the same rules as combat, but attack willpower instead of health. So screaming at someone is presence + intimidate instead of strength+brawl. Seemed like it would work on paper. Don't have any players at the moment to try it with.
There is always that problem with the ranged character who is optimum at the edge of their range. But most maps don't go that far and if they do you end up nowhere near the fun. The character also never gets hit or targeted so they are wasted hitpoints sitting in the back and bored. The tiny dungeon rooms from dungeon crawls mitigate this problem somewhat but its more fun to be right up in the action
Crazy characters in general. It turns out mental illness makes it really hard to work collaboratively on stuff, who knew :/

As a GM, basically any artificer / inventor. They only fit into very specific settings, so they’re very out of place in most games. If the system has light rules for inventions, the player thinks they can create anything, and I have to constantly fight them to stop trying to one-up the other characters. If the system has robust invention rules, these characters don’t generally get to invent anything since so much downtime and resources are required.

From the player's perspective this is a rough one as well. There's nothing more disappointing than to roll up a crafty character only to discover that the campaign has break-neck pacing to prevent rest spam, but also incidentally preventing any downtime for crafts.
- This was a problem for Mad Scientists in Deadlands. Some builds took months or years to create, and when time is of the essence, no new toys for you, scientist!
- @HipsterTenZero @DrakeRichards
  
  Very much this. It's basically the "hacker movie" problem in tabletop form. Actual making involves a ton of time and most of it is boring (even if the results are amazing). It's very difficult to translate this into the pace of a story while still making it interesting. To do so you often have to engage in flights of complete fancy, like the competitive code writing scenes in hacker movies.
It's especially bad in D&D5e, where the artificer can create any common magical item, but it has to be selected at level up and can't be changed, and since the game is so focused on High Fantasy, all of the common magic items are completely worthless, since the interesting stuff happens at higher rarety. In the end the system makes the Artificer a reskinned magic user where everything is worse than a plain sourcerer.
Or if they have robust invention rules the player playing the inventor knows exactly everything about them and how to exploit them.

Kenku played RAW gets old quick

Yeah, I don't know what they were thinking. This is completely unplayable.

It works for NPCs that don't stick around for too long, but should never be a player character.
- It works GREAT for a villain in my experience. You can taunt the players with their own words, or give ominous hints that they know wont be expounded upon.

Oracle / Seer / Diviner

Great flavour, lots of precedence in stories. But in practice if abilities didn't give us info/tantalising hints it was a waste of resources, and if it did then we'd skipped over fun adventure story beats.

On the one hand they are a great way for the GM to give you info and hints. On the other hand, they can feel like they short cut'd fun story-rich adventures to libraries and sages and NPCs with secrets we needed to know etc.

I think the DnD 5e School of Divination wizard did some good things to make a diviner fun without ruining the vibe. I love the Portent's ability to "see the future" with its messing of the d20 roll as well as the Expert Divination (you gain 1 lower spell slot when casting divination spells) allows you to cast divination spells without "wasting" slots if you didn't get anything good. It is really a hard concept to use often because at some point as you get more powerful you transform into a NPC just giving advice instead of going on adventures.
Another thing that makes Oracle / Seer / Diviner characters difficult to GM for is that you need to know things in advance, where the adventure leads to etc. As one whose GMing style leans heavily into Play To Find Out that sort of characters is kind of counter to it.

That said it is highly dependent of what the player want out of such an archetype. If it is a flavour for how the character solves problems I'm all for that. Touching an item to get a vision/impression for something (adventure) related to it go ahead. That is not too different to other ways of investigating. But the player who wants those powers to get "quest markers" or to completely negate obstacles ("hurr durr I have foresight so I've seen the ambush") gets hard noes from me.

Also agreeing with @dumples@kbin.social, D&D 5e Divination wizards are very well made and the divination spells work well in those kind of worlds.
- Of course, if you know you have a seer in your party, you can plan ahead and come up with some prepared scenes. They don't neccesarily have to be predicated on what's going on near the players either; they could, for example, foresee the bbeg tormenting his captives - get some flavor about how evil he is, maybe some plot-relevant information to use later, but it doesn't actually depend on which level of the dungeon they're on or whatever. Obviously, this depends on the details of exactly what spells they're using and in what system.
I’d be interested to see The Sprawl or Blades In The Dark’s “planning” mechanics applied to an oracle.

The Soldier playbook in the Sprawl gets to roll at the at start of each job to determine how many bullshit points they can spend during the job to get where they need to or have something they need.

Alternately if you like vague prophecies I’m picturing a system where at the start of the adventuring day you roll on a table of prophecy fragments, each with a mechanical effect, and the more powerful you get the better effects you can have and the more fragments you can roll for. That way you can have vague prophetic words that have a mechanical effect for you to play the hand of destiny.

Most dumb characters. Often I see them played they as intentionally disruptive, actively try to choose bad decisions, play stubborn and expect other players to force them into following any form of expected plot.

I've seen them played well, but when someone tells me they are playing a dumb character i usually find it more frustrating than fun and creative

barbarians in PF2e (at least early game) promise big numbers but that's all they've got going for them. They even disable half of the useful combo fillers skills give you access to. They're the only class I've hard bounced off of.