Window aggregates without collapsing detail

An aggregate window computes over related rows while retaining each source row. The worked query attaches each active role's company-level active count to that role identity. It returns six detail rows, unlike a GROUP BY report with one row per company.

Company A's two active records each receive count two, B's three receive count three and C's one receives count one. Those repeated counts are intentional annotations, not additional independent company observations to be summed later without care.

OVER with PARTITION BY defines the peer population for the aggregate. This example omits window ORDER BY so the count covers the complete company partition. The source WHERE chooses active rows before the window measures that chosen population.

The outer ORDER BY id declares display sequence only. Adding window ordering without considering the default frame can turn a full-partition aggregate into a running or peer-bounded result. The next lesson makes frames explicit rather than relying on a convenient default.

The counterexample groups by company and selects a minimum identity. It returns three summary rows instead of six role rows. It answers a legitimate company-summary question, but it discards the detail grain requested by this lesson.

A second error sums the repeated company count over the annotated detail output and calls that the total active-role count. Group measures repeated on details require a clear downstream grain. Preserve the distinction between an annotation and an independent additive observation.

Attach the number of known active salary observations at each company to every active role identity. Predict counts two for A, two for B and one for C, repeated on their detail rows. Keep role 3 in the output even though its own salary is unknown.

Use COUNT(salary) as the window measure and keep active status as the source filter. Do not add salary IS NOT NULL to WHERE, because that would remove role 3 and change the detail population rather than merely measuring observation coverage.

Step 1: Retain the active detail filter. Step 2: Count salary values across each company partition.

The solution returns six identities with the requested observed counts. B's missing-salary role still receives count two because the measure concerns its whole active company partition. A row can lack a value while still carrying a valid group-level coverage annotation.

The acceptance check includes both the unknown detail row and its company peers. Checking only one observed B row would not reveal whether the query had incorrectly removed the missing row before window evaluation. Reconcile detail count separately from observed-value count.

Explain the grain retained by a window and compare it with GROUP BY using the same fixture. This is a common interview distinction with direct reporting consequences. A window lets a detail row refer to a group statistic without turning the entire result into group summaries.

Next, define exactly which ordered rows belong to an aggregate frame. Peer ties and default frames can change a running total even when the partition and the aggregate function are unchanged.

Optional stretch — after the core task works: Explain why summing repeated company annotations over role rows can double-count a company measure.

Computer practice: https://applydjinn.com/typorithm/sql-lab-20261105.zip. Load the shared setup.sql fixture before running a snippet. The local verifier compares exact ordered row arrays in SQLite and PostgreSQL, keeps SQL values separate from driver bindings and never connects to the production website database.