Also Iain Banks' Culture novels have this question as an occasional theme (and the main themes are also very interesting, of course).

There's an interesting little passage where the protagonist of Use of Weapons, experiencing the Culture's society for the first time, meets a sociologist who works part-time cleaning tables at a cafeteria -- even though the job could easily be automated.

“I could try composing wonderful musical works, or day-long entertainment epics, but what would that do? Give people pleasure? My wiping this table gives me pleasure. And people come to a clean table, which gives them pleasure. And anyway" - the man laughed - "people die; stars die; universes die. What is any achievement, however great it was, once time itself is dead? Of course, if all I did was wipe tables, then of course it would seem a mean and despicable waste of my huge intellectual potential. But because I choose to do it, it gives me pleasure. And," the man said with a smile, "it's a good way of meeting people. So where are you from, anyway?”