A large variety of use cases really, e.g. fraud detection via pattern matching on a stream of credit card transactions, the creation of denormalized views (seeing this a lot in the context of Debezium change event streams from RDBMS), updating real-time dashboards e.g. based on incoming order data, etc. I don't think it depends at the size of the company, we see users of all kinds of sizes.
(Disclaimer: I work for decodable.co, a managed stream processing platform leveraging Flink SQL)
One big one I've seen are online ML models. You essentially have a feature store built on SQL ETLs. The ML engineers utilize this to build models, and deploy those to production.
However when using the models for prediction, the data hasn't been ETLed (think: credit modeling). One solution here is to use the same SQL for the ETL and for the online transformation to query the model. It can guarantee the biz logic in the features matches between training and online.
(Disclaimer: I work for decodable.co, a managed stream processing platform leveraging Flink SQL)