I have yet to see people give me a good, wide range of where these things help in an every day situation.
Depends on your "every day". If it's OLTP, well, that's been a "known quantity" for a long time and Windowing Functions would be merely a curiosity item.
But Windowing Functions, I hear, were originally promoted by Teradata, which gives you a clue where they're valuable.
If your "every day" is OLAP, then Windowing Functions, partitioning, bitmap indexing, compression, and such keep you from being swamped by ever-growing data sizes and query execution times. OLAP is where most new DBMS features are aimed, in my opinion.
I use Windowing Functions every day in my ETL. I just used them in this query to find some duplicates and identify the preferred ID to replace them with:
select * from (
select ROW_NUMBER() OVER (partition by policy_id, unit_Number order by unit_id desc ) r#
, MAX(unit_ID) over (partition by policy_id, unit_Number ) preferred_unit_id
, m.* from unit_table m
) where r# > 1
The FIRST_VALUE Windowing Function is a real blessing, and I know I'd miss it if I had to switch to SS (I even have a certification from Oracle that calls me an SQL Expert, so I know I could "code" around the gap, but it'd be slower to both write and to run).
I've thoroughly converted all of my ETL team into "true believers" in the value of Windowing (Analytic) Functions. As Tom Kyte has said:
11_QUESTION_ID:12864646978683" target="_blank" class="SmlLinks">Analytics rock!