There's an operational argument to make, but fundamentally it's about performance, and secondarily about what having an ultra-fast local database does to your performance budget and thus how you build things. The premise is that in a typical web application, each request incurs multiple round trips to the database, and those round trips add up to eat a chunk of your budget. A database like SQLite can essentially eliminate that round-trip cost.
It's not just about making snappier applications; it's also that even ordinary apps burn engineering time (for most shops, the most expensive resource) on minimizing those database round trips --- it's why so much ink has been spilt about hunting and eliminating N+1 query patterns, for instance, which is work you more or less don't have to think about with SQLite.
This premise doesn't hold for all applications, or maybe even most apps! But there is a big class of read-heavy applications where it's a natural fit.
You appropriately answered the question that was asked. I would add a little extension that this question (about SQLite specifically) was asked in the context of a discussion about "Distributed SQLite". The considerations around round-trip costs for "Distributed SQLite" are very different than for SQLite.
It's not just about making snappier applications; it's also that even ordinary apps burn engineering time (for most shops, the most expensive resource) on minimizing those database round trips --- it's why so much ink has been spilt about hunting and eliminating N+1 query patterns, for instance, which is work you more or less don't have to think about with SQLite.
This premise doesn't hold for all applications, or maybe even most apps! But there is a big class of read-heavy applications where it's a natural fit.