Skip to content

Instantly share code, notes, and snippets.

@wasabigeek
Created December 31, 2020 14:39
Show Gist options
  • Save wasabigeek/2b9fb05eba5c26928bab85bcf408511f to your computer and use it in GitHub Desktop.
Save wasabigeek/2b9fb05eba5c26928bab85bcf408511f to your computer and use it in GitHub Desktop.
Window Function Calls in Postgres, Visualised (Example Data)
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);
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment