Understanding window functions

Question

Postgres-sql:

I'm not able to find a resource which can help me understand how to correctly use aggregates with/inside window functions and how to use RANGE/ROWS frame_clause correctly. I have reviewed some resources online, like: https://www.compose.com/articles/metrics-maven-window-frames-in-postgresql/
(I really liked the article, it solved some doubts)

1. Using aggregates correctly with/inside window functions SUM() OVER(... ) sometimes produces multiple rows when I'm least expecting them, or asks me to add more columns to groupby

For example: For the question here: https://pgexercises.com/questions/aggregates/fachours4.html

select facid, total from
(select facid, sum(slots) total, RANK() OVER (order by sum(slots) desc) pos
from cd.bookings
group by facid
 ) A
where pos = 1

I wrote this query and it works correctly. But for this: https://pgexercises.com/questions/aggregates/rollingavg.html, I wrote this query:

select starttime::date, sum(slots * case when memid=0 then guestcost
                        else membercost end) over (order by starttime::date asc
                                                   range BETWEEN INTERVAL '14 day' 
                                                   PRECEDING AND 
                                                   CURRENT ROW)::decimal/15 revenue
from cd.bookings B inner join cd.facilities F
on B.facid = F.facid
where starttime >= '2012-08-01' and starttime <= '2012-08-31' 
group by starttime::date, slots, memid, guestcost, membercost
order by starttime::date

and it asks me to add slots, memid, guestcost, membercost to the groupby when they are a part of the average calculation. I haven't correctly solved this question yet.

2. How do different window functions operate with RANGE/ROWS? For example, rank() seems to produce correct results without frame clause UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING being specified for it, but for the last_value() calculation here, there is a need to specify this clause:

SELECT
    product_name,
    group_name,
    price,
    LAST_VALUE (price) OVER (
        PARTITION BY group_name
        ORDER BY
            price RANGE BETWEEN UNBOUNDED PRECEDING
        AND UNBOUNDED FOLLOWING
    ) AS highest_price_per_group
FROM
    products
INNER JOIN product_groups USING (group_id);

(this example is taken from here: https://www.postgresqltutorial.com/postgresql-window-function/ - sorry, not able to select the exact area in the page to link here)

vs

SELECT
    RANK () OVER ( 
        ORDER BY price
    ) rank_number 
FROM
    sales;

-> this ranks correctly without needing to specify the BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING clause.

Answer 1

https://momjian.us/main/writings/pgsql/window.pdf (slide 35 and slide 36)
https://modern-sql.com/caniuse/over_range_between_(numeric)
I hope the following picture can help you understand the difference between over rows versus over range.