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
(and (buffer-modified-p)
(or (buffer-file-name)
buffer-offer-save))
buffers.
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.
Evgeni