I noticed that Assimp supports meshes with multiple vertex color sets, but what is the use of multiple vertex color sets? I mean are multiple vertex color sets are really used in any games, and if yes, for what purpose?
Vertex colours are just arbitrary extra data you can pack into a mesh - you don't have to use them as literal colours when rendering. So frequently, these get used as control weights for a variety of effects:
Storing low-frequency ambient occlusion or baked lighting data (which might be literal light colours, or each channel could store an exposure mask for a given light source or approximated bounce).
Controlling which vertices in a cloth or foliage mesh are free to flutter in the wind or get pushed away from force fields, versus which ones should stay more rigidly fixed in place.
Storing a local offset to a pivot point, or the orientation of a joint/axis, so that branches of a tree or dangling bits of a costume can be animated cheaply in a vertex shader without needing full skeletal animation for every wiggly bit.
Controlling the frequency bands of such motion, so leaf tips can tremble while thicker branches slowly sway.
Controlling the phase of animation, so effects can ripple from one part of the surface to another in an artist-controllable way, independent of the mesh's pose or texture unwrap. This can also be used to make different parts of the asset animate out of sync with each other, so the effect looks more organic rather than mechanical.
Masking which parts of a mesh with overhangs should get wet when it rains.
...or more generally, picking out any parts of the mesh that should have some special effect applied, that doesn't need the per-pixel precision of an extra mask texture.
Natalie Burke presented a great GDC talk with lots of examples of how these types of techniques are used in Destiny, especially for things like the animations of weapons and other gear items.
You can find similar techniques in this longer presentation from Gilbert Sanders about vegetation rendering in Horizon Zero Dawn (around 10 minutes in).
Something handy about encoding this information in the vertex colours instead of using the vertices' local positions is that vertex colours are preserved unchanged, even if you combine multiple small meshes using the same material into a single batch (which usually involves 'baking out' their transformations, modifying the local space vertices that the vertex shader sees). So that's why you'll still see this used, even in cases where the vertex colour is just a predictable gradient in the mesh's local coordinate space.