Um, I dropped a rock on the Pope's head once, which was certainly memorable. But it is fairly rare (only happened once to me that I recall). I used to like catapults for the "shock and awe" factor (even more so with firey onagers in RTW) - they certainly seem impressive. But when I look at the post-battle casualties they often kill very modest numbers - far less than a vanilla unit of archers in my SP games. I've heard they get much better with high valour generals, so that may be a factor.