What kind of content really challenges video de-interlace circuits? Does anyone know where I can find some solid examples? Are there any demo reels/break tapes on the market or perhaps at certain post-production houses?

I would say anything that has fast moving objects in it or a fast
moving camera. Motion blur (ie, low shutter speeds) might make
it even worse.

If you have a perfectly still scene you should not be able to see
that it is interlaced footage (since there is no difference in the
two points in time).