Собственно, все задачи, которые требуют наравне с самими данными иметь и некоторую дополнительную информацию по поднаборам (подитоги, промежуточные вычисления), доступ к строкам до/после текущей строки (LEAD/LAG), получение первых/последних значений в подгруппах (FIRST_VALUE/LAST_VALUE). В общем основная цель - отчетно-аналитическая, как и следует из названия функций. Изредка применяется при т.н. паджинации (постраничном выводе информации), особенно когда итоговая сортировка набора не совпадает с условиями формирования страниц.

В ряде случаев позволяет избежать нескольких обращений к одной и той же таблице, ускоряя выполнение запросов и делая код более читаемым.

Да, не все функции одинаково широко применимы, как приведенные вами, но есть ситуации, когда и они нужны, например, при решении о премировании . Попробуйте решить эти задачи без аналитических функций и сравните получившийся код.

Вот именно в такой постановке - вряд ли найдете за пределами учебника. Ибо "заказчику" чаще всего безразлично, как именно разработчик решил задачу. Может быть интересно администраторам, т.к. они контролируют использование ресурсов и их - ресурсов, - перерасход мало кому нравится. Аналитические функции - это инструмент, и пользоваться (или не пользоваться) инструментом - дело разработчика.

Жила-была система.
И торговали в этой системе полотово. И попросил Заказчик выводить по 50 лотов на экран за раз. Сделал это Разработчик, ибо не сложно было.

Посмотрел Заказчик, и говорит: "А хорошо бы еще Клиентам показывать общее число лотов."
"Чего уж проще" - подумал Разработчик. Первым запросом получаем общее число лотов, вторым - собственно лоты. Вроде все красиво: вот поле с количеством лотов, вот - табличка с лотами. Поблагодарил Заказчик за быстрое исполнение его желания.

Но торговали в системе интенсивно, и начали Клиенты жаловаться: "А почему общее количество лотов - 42, а у меня на экране только 37? Вы мне не все показываете?! Это не честно!" Прибежал разгневанный Заказчик к Разработчику: "Почему?!"
"Почему-почему..." - подумал Разработчик. "По SCN'у!" Но Заказчик этого не узнал, потому что Разработчик был очень воспитанный.

И взял Разработчик аналитический count, и стало вместо двух запросов - один, а число в поле с общим количеством лотов стало совпадать с числом строк в таблице (если их меньше размера страницы, конечно же). И успокоились Клиенты, извинившись за свои подозрения, и улыбка вернулась на лицо Заказчика, и спокойствие воцарилось в его душе.

Тут и сказочке конец. Счастливый. На некоторое время.

Прошу прощения за форму.

Вряд ли. Ни системные, не бизнес аналитики не указывают разработчику, каким инструментом пользоваться (чаще всего - не знают: не их область), максимум - транслируют перечень технологий, в которых надо придется работать.

Например, увидеть, что человек по зарплате перешел в другой налоговый класс (при прогрессивной шкале налогообложения), или проверить лимиты снятия наличных в день.