lesson 44 Window functions · advanced

Share of total with windows

Compare each row with its group total.

the idea

An aggregate window without GROUP BY can put a grand total alongside each group. Aggregate per model first, then use SUM(request_count) OVER() as the denominator.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

ai_requests7 columns · 12 rows

idinteger · key	account_idinteger	modeltext	requested_attext	input_tokensinteger	output_tokensinteger	statustext
1	1	swift	2025-01-01 10:00:00	1000	200	success
2	1	reason	2025-01-02 11:00:00	2000	600	success
3	2	swift	2025-01-02 12:00:00	500	0	error
4	3	reason	2025-01-02 13:00:00	8000	2000	success
5	3	swift	2025-01-03 14:00:00	4000	1000	success

showing 5 of 12 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

WITH counts AS(
  SELECT model,COUNT(*) AS request_count
  FROM ai_requests
  GROUP BY model
)
SELECT
  model,
  request_count,
  ROUND(100.0*request_count/ SUM(request_count) OVER(),2) AS share_pct
FROM counts;

Watch for: Partitioning by model would give each model 100%.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1
Count requests per model. A share of total needs each group's count and the grand total. Start from the per-model counts.
step-1.sql
sql›
```
SELECT model, COUNT(*) AS request_count
FROM ai_requests
GROUP BY model
ORDER BY model;
```

Divide each count by the window total. A window SUM over the model counts supplies the denominator so each row can show its share.

step-2.sql

WITH counts AS (
  SELECT model, COUNT(*) AS request_count
  FROM ai_requests
  GROUP BY model
)
SELECT
  model,
  request_count,
  ROUND(1.0 * request_count / SUM(request_count) OVER (), 3) AS share
FROM counts;