Not sure I understand the final bit. Everything useful is stateful, it just depends where your state is stored.
State storage is the key. If you look at S3 for example, that effectively scales to zero. You can store a file and use that as your data store for a function, without ever having to worry about scale (which is interesting). For DynamoDB you have autoscaling to cope with scale.
Scaling up and down is hard, and scaling down is much harder for state than scaling up. The idea of DynamoDB is that it should consistently handle that load (reads and writes) irrespective of scale.
So scale to zero is difficult and not a completely solved problem. But one thing I’m certain of is that if you use an RDBMS you are far less able to scale down or refactor than if you use something like DynamoDB or even better S3.