One idea
Window functions calculate across a group of rows, but they do not collapse rows like GROUP BY does.
Use them when you want each row to stay visible, with an extra rank, running total, or share added beside it.
Why windows matter
Product questions often need both detail and comparison. Example: which feature got the most events this week, and what share of total activity did each feature get?
GROUP BY can give one row per feature. A window can then rank those rows and add totals without writing a separate query for every metric.
Sample data
Use a small events table like this in your project. It is enough to practise rankings, totals, and shares.
Each row is one product event from a user.
create table events (
event_id integer,
user_id integer,
event_name text,
feature text,
event_time timestamp,
amount_rs integer
);
insert into events values
(1, 101, 'view', 'payments', '2026-01-10 09:00:00', 0),
(2, 102, 'view', 'chat', '2026-01-10 09:05:00', 0),
(3, 101, 'pay', 'payments', '2026-01-10 09:10:00', 299),
(4, 103, 'view', 'payments', '2026-01-10 09:20:00', 0),
(5, 104, 'message', 'chat', '2026-01-10 09:30:00', 0),
(6, 105, 'pay', 'payments', '2026-01-10 10:00:00', 499),
(7, 102, 'message', 'chat', '2026-01-10 10:05:00', 0),
(8, 106, 'view', 'catalog', '2026-01-10 10:20:00', 0);Read the pattern
Do each one yourself, then tap it to tick it off. The ticks are only a checklist for you: they are not marked or scored.
0 of 5 done
Rank features
This query answers: which features were used most on 10 Jan? It keeps one row per feature, then ranks the features by event count.
rank() can skip numbers after ties. dense_rank() does not skip. For a leaderboard, dense_rank() is often easier to read.
select
feature,
count(*) as event_count,
dense_rank() over (order by count(*) desc) as feature_rank,
round(
100.0 * count(*) / sum(count(*)) over (),
2
) as pct_of_events
from events
where event_time >= '2026-01-10'
and event_time < '2026-01-11'
group by feature
order by feature_rank, feature;Running revenue
This query answers: how much revenue had arrived after each payment? It shows the running total in rupees after every pay event.
This is useful for order timelines, daily sales, wallet top-ups, or UPI payment flows in a student startup project.
select
event_time,
user_id,
amount_rs,
sum(amount_rs) over (
order by event_time
rows between unbounded preceding and current row
) as running_revenue_rs
from events
where event_name = 'pay'
order by event_time;Common mistake
Do not use order by only at the end and expect the window calculation to follow it. The final order by controls display order only.
For rank or running totals, put order by inside over (...). That is the order used for the calculation.
Do this now
Do each one yourself, then tap it to tick it off. The ticks are only a checklist for you: they are not marked or scored.
0 of 5 done