I no longer use it much, since I create/kill temporary buffers quite often. But this is the default behavior in most programs, and I suspect many people would want to change it. My 1 year ago self included.
On second thought, there might be a case for prompting for save only on emacs exit, but I think it would be more natural if there was a separate switch to do that, and it was responsible for all
On a related note, I get the impression that the prompt being hard coded is a remnant from times when kill-buffer-query-functions didn't exist.