It's good for situations where you don't have access to a "real" database and still want the power of a relational db. For example, Firefox stores a bunch of information about your settings/history/etc in an SQLite database. You can't expect everyone that runs firefox to have MySQL or postgre installed on their machine.

It's also perfectly capable of running relatively-low traffic, read-heavy websites. The performance of it is overall very good, it's more than the large majority of websites need for their traffic levels.