I was called at my firm for having to create a analytics dashboard for a multiTenet CRM dashboard where the magnitude of rows were in thousands and the efficiency i could not blame postgress or servers I had to figure out how to index, query and cache the data.

The business questions were something like this

1. Give the sales agents aggregated sales data from the month of January, till the current month in waterfall structure so that the numbers are aggregated in that order.

2. Ranking the sales agents on the basis of their quaterly performance to reward the winner on the basis of how much sales they made and the repeating customers that were coming in from that agent.

The issue was simple I had no idea that there is something called as a window function. So I took my beginers head and started to query the data. Where I failed to make a query for the correct required data, partly because I was still a noob, if I had known sql well i would have solved the problem.

| How do I aggregate the rows of data without collapsing

A simple structure of window function

FUNCTION_NAME() OVER(
  PARTITION BY COLUMN
  ORDER BY COLUMN
  ROWS BETWEEN
)

The difference between a GROUP BY and WINDOW FUNCTION is that a group by will collapse

SELECT rep, region, sales, RANK() OVER (
  PARTITION BY region
  ORDER BY sales DESC
) AS rank_in_region
FROM sales_reps;

Partition By will make sure that the rows are split in groups. Each region is processed independently like running query separately for each group.

SELECT
  month, revenue,
  SUM(revenue) OVER (
    ORDER BY month
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM monthly_revenue;

UNBOUND PRECEDING Frame starts at row 1 always.

PARTITION BY The entire table is one window. Compare to Query 1 where PARTITION BY created separate window per region.