Way back in the distant dawn of time (or at least, of competitive Counter-Strike play), I ran a major UK Counter-Strike league. Cheating was a pretty big issue back then (not least because software anit-cheat was much less developed) and we spent a lot of time on the watch for it. In the 18 months or so I was running the league, we had maybe 10 cheat detections during competitive play. The guys running the "open" public servers sponsored by the same company were getting a similar number of detections in the average week.
By and large, I think there were three reasons why people cheated. The first was simple curiosity; people who were bored of playing the game honestly and just wanted to see what the cheats were like. There probably weren't too many of these.
The largest group were the trolls; the people who cheated not because it was fun in itself, but because they got off on pissing off other people and screwing up their leisure time. Some of them would try to hide their cheating, but a lot of them were pretty damned open about it. After all, it's annoying to play a guy you think might be cheating. It's even worse to play a guy who is open and proud about the fact he's cheating, in a world where it can take time (up to an hour, on the public servers) to summon an admin.
The third kind were the properly competitive gamers who felt they were struggling to keep up with the pack and thought that by making subtle use of cheats, they could give themselves an edge. This was the only kind we tended to see in the competitive league. "Pro-gaming" was in its infancy back then, but was already becoming "a thing" and there was sponsorship and prize money floating around. There were lots of players who frankly weren't good enough who thought they could make a fist of pro-gaming. When it became clear that they weren't cut out for it (you need both a hell of a lot of practice time and god's own natural reflexes to cut it in that world), they'd often resort to cheats. They would always try to hide the fact they were cheating, so unless you got a rare software detection, discerning cheating from good or lucky play was hard (but not impossible) for an admin.