In their 2008 gamasutra article, Andrew Doull writes a foundational article about progression in RPGs and how the concept is abused at large in game design. Doull splits up the concept of progression into two types, Character Progression and Player progression defined as follows:
Player progression is the increasing aptitude of the player in mastering the game: whether through learning and understanding the technical rules of the game (surface play) or the implications of those rules (deep play).
Character progression is the unlocking of additional rules of play, or altering the existing rules, by choices or actions within the game. The most common unlock is the ability: an additional in-game interaction that the player’s avatar can choose to do. But scaling upwards existing abilities is just as common in the RPG space.
I love these definitions! I think they're extremely useful to keep in mind while thinking about intrinsic fun and game design. On a personal level, I care almost nothing about my character progression, and am also completely unimpressed by other people's character progression. Being forced to spend time progressing my character so that I'm allowed to fight another player or NPC upsets me. Being forced to complete small, timegated trivial tasks periodically (like weekly torghast, for example) is extremely tilting.
Significantly better design is allowing elite players to complete challenges early while allowing lesser skilled players to compensate with character power. A perfect example of this is in dark souls 1, where it's technically possible for a new player to defeat the first enemy in the game with the broken hilt of a sword, and they may choose to do so. If that's not their jam, they can simply leave and come back later after their character has progressed. Beautiful!
Remarkably, this is how the race to world first in every wow expansion works. When guilds like Echo and Complexity-Limit try to fight the bosses for the first time, they do it when their characters have the least amount of character progression. Each week that goes by, their characters progress by acquiring more gear, and the statically difficult boss fights require less player progression. By the time top-5000 guilds are attempting the content instead top 10 guilds, they have characters that are significantly stronger compared to their top-10 counterparts, and this is wonderful.
FFXIV removes character progression for their elite content, and it's an absolute joy. FFXIV does not allow meaningful character customization, and their elite content will apply an item-level template to your character, to either bring you up-to-snuff, or lower you down to exactly the character-power level they designed the content to be completed at. This allows them to fine-tune the difficulty of the encounter to make it exactly as difficult as intended, and allows them to fine-tune class balance so that each class' playstyle is exactly what they intended. They trade freedom for elegance.
Meanwhile, ARPGs like Diablo 3 and Path of Exile feel like they're nearly entirely about character progression. The intrinsic fun for those games seems like it's about testing/researching builds and then experiencing how good your character is at carnage. Your player skill at build research translates to your character being more powerful. Players then play the game, choosing the difficulty / clear speed that affords them the most currency/expected loot per hour while minimizing risk of death. When their characters grow in strength, they adjust the difficulty upwards to compensate so that they never have to tax their own player skill. There are a few player-skill checks like difficult bosses that require nimble mouse movement and game-knowledge to not get wrecked by the boss's aoe attacks, but the vast majority of your gametime is spent in comfortably farming maps to increase your character power so you can comfortably farm more maps.
I think games that emphasize character progression over player progression are on some level predatory. Character power progression is a drip-fed dopamine rush since you feel like you leveled up when your character levels up, because you are represented by your character. Games, like RPGs that lock character progression behind RNG systems (like random loot) are thinly disguised skinner boxes and I really wish the reliance on these systems could be dimished.