http://twissandra.com/ is a demo Cassandra application built on django + pycassa. It's a great Cassandra showcase and very useful for people learning Cassandra. We could use more of those.
Jake Luciani suggested one that presents full-text search of Wikipedia using Lucandra (see http://blog.sematext.com/2010/02/09/lucandra-a-cassandra-based-lucene-backend/ and http://github.com/tjake/Lucandra). Feel free to propose other application ideas here.
Rackspace is willing to provide a VM to deploy on for a live demo, but remember, to be really useful this needs full DIY instructions, the final product is not the demo but the code + instructions.