As you'd likely expect, each mode is markedly different from the others, so everyone will have their own favourite. In Screamrider, the idea is to get your roller coaster car around the track as quickly as possible. You can accelerate, brake, boost, and even tilt your car onto two wheels to get round the track as quickly as possible, whilst being sure to not come off the tracks...
In Engineer mode, meanwhile, the game sets you a roller coaster construction themed goal, where you have to design a coaster that's scary enough to get the required scream rating. There are a few more factors to take into account here (you'll need to put boosts in to make sure the car gets up steep inclines), but it's far from realistic, and mostly about having a bit of fun, with a fairly simple editor that handily comes with an "auto complete" option for getting your coaster to the finish.
Demolition mode, on the other hand, is all about destruction, and it's this that kids will likely have the most fun with. Mostly putting you in charge of a giant fairground ride style catapult, it's up to you to lob a capsule at a load of buildings, with the intent of knocking as many down as possible. Holding A will slow down time and bring up a dotted line showing your pod's trajectory, and all you have to do is let go to send it flying. This is a lot of fun (destroying things always is), and also works great in a makeshift, unofficial "pass the controller" multiplayer mode, where you try and beat each other's high score
In terms of accessibility, there's not really too much to stump children here. Driving your roller coaster is fairly easy, and blowing things up is fun - so the only real issue here is going to be engineer mode, which can sometimes be a bit fiddly when it comes to actually making your coaster work. When you have a problem (like the car coming off the track), switching back to edit mode doesn't always make it totally obvious where the car derailed, so fixing the issues can sometimes take a bit of guessing, with the somewhat fiddly controls not helping. Still, older children should have no issues with this - and the demolition mode is fun enough on its own. Handily, you can also download a near infinite supply of user generated levels through the game's level centre, drastically expanding the play time.