Quite often, depth cueing is combined with other effects such as transparency to properly model non-solid substances, eg. water. Theoretically, depth cueing could be used to model localised smoke, but in practice it's easier to use partially-transparent sprites. In future console systems, it's likely that new and more powerful ways of modeling inherantly volumetric effects like fire, water and smoke will be introduced. Depth cueing works fairly well for modeling fog and mist, but in some situations it doesn't quite look right, mainly when the effect is in the far distance and one's viewpoint can see a wide area. This happens because the 'edge' of the fog bank is always perpendicular (face-on) to one's viewpoint; as one turns around, the apparent edge of the mist or fog bank appears to change its position.
Turok, for example, uses depth cueing extensively. On level 1 in Turok, there is a cliff one must jump onto and then climb up (at the top of the cliff is a pack of Tek Arrows). Try staying on the side of the cliff (don't go all the way to the top), then just look down and around with the joystick: you'll see how the absolute position of the distant fog does not remain constant. One could get round this by having more than one depth-cued area, but that would require greater computation.
Elsewhere in Turok, the depth-cueing is very effective, as is shown by the images on the left and right here. The transparent water in the right-hand image shows some misting at the bottom of the lake, and if one looks into the cave then one can see the far reaches of the cave being obscured by a depth-cueing effect. Sometimes, it's not obvious that depth cueing is being used, an example being some of Turok's corridors on level 5: black depth-cueing is used to produce an increasing darkness effect as one looks down a corridor.
Note: it has been said that Turok uses depth cueing to hide 'pop-up' - this is complete nonsense. Since one cannot see anything 'popping' into view when playing Turok, then it is not pop-up. If Turok is indeed using depth cueing to mask out when an object first appears, than that is just a clever form of detail management and has nothing to do with 'pop-up'. What Turok is doing is an invisible effect, whereas pop-up is a visible effect. It's worth mentioning that the degree of depth-cueing in Turok2 is considerably less than in Turok, as the five images below (obtained from IGN64's web site) show quite well.
Shadows of the Empire uses depth cueing nicely when one looks down a cliff face (a good example is the Imperial Sewers level, where it is used to produce the dirty water effect).
Fans of Doom will probably be very appreciative of the depth cueing effect when they play Doom64 as it should enhance the tension in the game (ie. not quite being able to see what's far ahead, or having much more accurate dark-corridor effects, etc.)