It's very common in suburban homes to have both an unused guest bedroom and an unused dining room solely for the two times a year the owner entertains. Since this wasn't really logical in the first place I doubt more short-term rentals would make much difference.
My parents have 3 spare rooms, because my siblings and I have moved out and they haven't downsized to a smaller house.
But I wouldn't expect a young family to have a spare room. When I was younger and we needed a spare room, I was always relegated to the couch to make room for guests.
I don't expect it's super common to have a dedicated guest bedroom in a house unless it's, as you say, a kid's room who has moved out. On the other hand, quite a few homeowners have a room they use as a part-time office, for hobbies, etc. that has a sofabed in it. That's more or less the case with me. I have a "spare" room that I use for various things but I can make up a bed if I have guests.