Don't know everything about Roy but I'll try my best.
Roy isn't lightweight, his weight is 95, he's right in the middle, as the 30th heaviest, a pure middleweight. BUT with a bad recovery it kinda worsens this because that doesn't matter that much when your recovery is that bad. Robin is equal in weight to Roy, but he has a better recovery, by a fair margin, overall increasing his
survivability. It doesn't matter only if your weight plays a factor, but falling speed, gravity, recovery all play a part into survivability. Roy's a fast faller with a bad recovery as well as slow air acceleration, so yeah, that's not good.
Just compare Ganon and Dedede. Similar weights, but who has a superior recovery?
Also, Roy doesn't have projectiles, so we can't really compare that to Fox and Pikachu, but most of Roy's moves are disjointed, which helps. Its just a shame that unlike normal swords, Roy's tipper is as weak as a pool noodle (I believe someone else has made this joke so credit to them).
And yes, even though the sourspots can be used for combos, you'll eventually have to get in close, which kinda negates the sourspots at higher percents. So even though he technically has good range, it's the reason why Marth is successful. Marth has good range, with good damage at the TIP, therefore encouraging players to space, Roy can't do that. His design forces him to get in close, making that range simply average in reality, not something you want from a swordsman.
I'm not the most knowledgeable of Roy so please correct me if I'm wrong.
Hopefully this can give a better insight into not just Roy but Smash in general.