I've contemplated this aspect of the games from time to time. I even wrote a short story attempting to explain how Roger, who isn't a complete idiot but still isn't exactly the sharpest knife in the drawer, could possibly survive the various life-threatening situations and brain-boggling puzzles over the course of the SQ series.
I once read a story that took a different approach, describing how Roger is tormented by the terrifying moments from his past adventures, and is filled with anguish that despite his countless victories, he is still seen as merely a bumbling idiot with incredible luck. While the story was very well-written, it's not the way I personally picture Roger's mentality.
Now that I think about it, most of SQ6 featured the narrator calling Roger an idiot a lot more than Roger actually acting like an idiot. Lots of Roger's lines were actually pretty witty, though he did occasionally have his "slow" moments (the "Oh, hi Stellar! What are you doing here?" situation springs to mind).
Your actions as the player also effect how much of an idiot Roger is as well as how much the narrator verbally abuses Roger (and you as well, to an extent). For instance, if you try touching the frozen endodroid, causing Roger to get frozen in a pose reminiscent of John Travolta in "Saturday Night Fever", the narrator mutters, "This may just be your dumbest moment ever. It's hard to tell -- you've had so many."