Does anyone really use anything other than SCART / component to play retro games on CRTs that support RGB? I've ordered composite cable to see whether some games will benefit from a worse IQ (Genesis, SNES but even PlayStation). However, I remember that back in the 90s and 00s the first thing I always did when buying a console was purchasing the best quality RGB cable. Original ones could be find easily back then. I'd never think to use composite instead.

I know some games were made before RGB was even a common thing and I've seen those comparisons on yt so I know why someone might prefer to use composite but is it really that common?

In terms of picture quality they'll go from:

RBGs (in SCART) — commonly called "RGB" — which is RGB + Sync, three pure colours plus timing for crisp pixels in SD formats.

Component — which divides the signal into luminance (i.e. brightness, via the green Y cable) and two colour-difference signals (blue and red, via the Pb/Cb and Pr/Cr cables) sync is also on luminance; display losslessly converts YPbPr to RBG internally and supports HD formats as well as SD ones.

S-Video — separates video into two channels of luminance (brightness) and chrominance (colour), sync is on luminance, it gets rid of the dot crawl of Composite, but lower colour resolution than Component or RGBS and only available for SD formats (NTSC, PAL, SECAM)

Composite — also called "CVBS" — combines luminance, chrominance, and sync on one channel it produces well known composite artefacts like dot crawl.

Radio Frequency "RF" signal — radio frequency signal compatible with regional analogue television standards (NTSC, PAL, SECAM).

I used to get my consoles modified to RGB, even imported ones (e.g. US SNES). N64 was much better graphics as RGBs than as S-Video.

RGBs support over SCART wasn't always guaranteed, nor was 60Hz support in UK, but these were both feature I always required in 1980s, e.g. plugging an Amiga into a portable tele you wanted that in RGBs 60Hz and not in Composite 50Hz.

Back when I worked on games for a living, e.g. 8-bit computers and consoles — we usually used Composite video for graphics, although BBC Micro was RGB and we had monitors for that along with hardware hacked Spectrums that output RGB video too that we put on the same NEC monitors.

So RGB design work goes right back to the start of the games industry.