Window Function Calls in Postgres, Visualised (Example Data)

create_expenses_table.sql

create table expenses (
  id serial primary key,
  description varchar,
  category varchar(255),
  created_at date,
  cost decimal(2));

insert into expenses
(description, category, created_at, cost)
values
('bus ride to work', 'transport', '2020-01-01', 3),
('lunch', 'food & drinks', '2020-01-01', 15),
('dinner', 'food & drinks', '2020-01-01', 35),
('groceries', 'food & drinks', '2020-01-01', 60),
('taxi to home', 'transport', '2020-01-01', 20),
('supper', 'food & drinks', '2020-01-01', 15),
('bus ride to work', 'transport', '2020-01-02', 3),
('lunch', 'food & drinks', '2020-01-02', 15),
('tea break', 'food & drinks', '2020-01-02', 5),
('bus ride home', 'transport', '2020-01-02', 4);

query_expenses_table.sql

-- avg of whole table, subquery
select *, (select avg(cost) from expenses)
from expenses;

-- avg of whole table, window function
select *, avg(cost) over()
from expenses;

-- avg per category, window function
select *, avg(cost) over(partition by category)
from expenses;

-- ranked expenses per category per day, window function
select *, rank() over(partition by category, created_at order by cost desc)
from expenses;

-- top expense per category per day, after filtering above query
select *
from (
  select *, rank() over(partition by category, created_at order by cost desc)
  from expenses
) as "ranked_expenses"
where rank = 1;