Here you go, based on my own limited experience :
Concerning RPGs :
For tabletop the best was a Star Wars one but I forgot which edition. Played two campains and it was perfect, good progression, never went in our way but always helpful and to the point when needed. I've also mastered a GURPS campaign and the system was neat, very "plug and play", maybe a bit too complex for its own good but nothing serious. There was also In Nomine Satanis which was underwhelming stat-wise but with a cleverly crafted settings allowing lot of leeway for both the GM and the players while staying in-bounds at all times. Nice stuff such as recovering energy doing a chosen RP action a certain way.
Next to it D&D was a chore, stupidly heavy on rules and expensive rulebooks. You had to bookmark every other page because you always hit some edge case, dozen of pages were needed to cover fighting and leveling was so brutal it made us metagame to get the best of our characters instead of going with the flow. In hindsight it has become obvious how well all this was marketed to ease beginners into thinking that RPGs = rules = rulebooks = $$$
Tabletop wise the only thing more unbearable was a homebrew made a weeb from our RP group that had all the tropes you can imagine. Complete nonsense, however the fact it still was played and had a months long campaign I quickly dropped from (stealing half the player base and the only girl to start my own group) so the lesson is people will play the worst hair-pulling ass-designed horror is they have nothing else to do (or are promised pizzas).
For CRPGs I know D&D grid-based fights and finite state fighting is very well adapted to computer development, and it was very good for say Nethack. But I was quite concerned to see the same system in Planescape Torment were it does not make any kind of sense ("You have received 2000000xp for talking to that NPC because of plot advancement !"). However for video games it is less aggravating since the computer does all the shitty jobs for you and you only have to focus on watching the numbers grow. Still I think the TES games system is better for example, if only because it has always been tailored for computing and depends on lots of ugly formulas you will never see, only providing you a nice interface.
Concerning minis :
Void (BWM) was a SF game with similar scope and approach to 40k. However the rules were tight (and shorter, and free), fights were resolved quickly making the game quite hardcore, factions were wildly dissimilar and there were rules for funky shit such as AI controlled robots. Almost perfect, the minis were nice and not too expensive, the company went under then came back but I was out of the scene at that time so that's it. Next, contender for the greatest game package ever designed was Confrontation, probably the only that could fight Warhammer head-on on the quality of models and art. Plus it had a all-in-one design : you only bought the miniatures, they were each coming with a beautiful stat card and for the most important ones the rulebook that was a small paper book of twenty or so pages fitting neatly into the blister box. Everybody was sure to have at least a couple extra copies of the rules he could share around, and they were so short you had them memorized quickly anyway. The game was short and to the point, small scale, each creature acts in turn, fights were resolved 1v1 with the possibility to choose each round whether to offend or defend against possibly incoming attacks, so same two encounters between the same opponents could go differently according to their orders. You could play a whole battle with three or four dice shared between the two players.
The special award of the stupidly clever trick was Ronin Duel, a giant robot fighting game where robots had a magnet on each body part. Damages taken were small metal markers you would stick to the magnets. Three markers on an arm ? It was destroyed. You could even wrap the markers in yellow painted cotton to give the impression your robot was catching fire. Game was otherwise ok but shit was so fun.
40K and WFB relied mostly on throwing more dice than your opponents and checking tables. Playing orks ? Hope you brought at least 15d6. Wanna play ? For the price of the rulebook and the army books alone you could have bought a real starting army in any other game cited above.
And finaly, CCGs :
Was not very big on CCGs since I quickly realized how shitty the booster system was (a teen and its money are easily parted away). Magic was ok I guess in the end as a game. Not much to say. Many possibilities, a bit boring on the side. I remember playing a couple games with a Fellowship of the Ring deck from a friend a thinking "wow, this is so much more fun". Probably because once again the focus was on strategy and not on making people buy more cards.
There was a small game with a name I can't remember (was along the line of "zoondu", serves them well for choosing a stupid name) where the cards were placed on the table on a virtual grid, each had attack and defense values linked to its position on the board and relatively the the attacking/defending card. Simple, fast, elegant.
On the topic of card games, I also used to be a seasoned Belote Coinchée player, and played quite a few 52-cards deck based games, so I know how much more fun you can have for years with a 2 bucks card game compared to the deck-building money-burning CCGs.
I hope this answer your question.
