Agreed generally that the "Riker is so awesome he gets offered a command but then turns it down" reference in S1 was a bad idea.
I think "The Icarus Factor" really answers some of these questions. On the surface, it looks like Riker bucks promotion simply to piss off his Dad... but I think actually what happens there is Riker realizes he'd been to some extent living to spite his Dad while also
living the life his Dad wanted him to live.
In other words, the choice Riker makes for staying on the Enterprise
is his choice, whereas he may've accepted promotion and been the opportunistic Shelby-type prior to this because of his father's expectations. "Dammit, I am good enough, Dad, and I'm going to prove it." All that changed, IMO, in "Icarus."
Fitting Picard into that, as the The Old Mixer
suggests, Riker may've found strength/inspiration from this from Picard as a surrogate father figure. Consider Picard's offer to become Starfleet Academy Commandant in "Coming of Age" which was apparently premature and due to Admiral Quinn's fear of invasion, but Picard still chose to turn down.
If one is cynical, one seems to get the impression that in the 24th century Starfleet is in the habit of assigning officers and then almost immediately afterward trying to transfer/promote them elsewhere!