Stores Beating Company Average — Solution & ExplanationProblems / Stores Beating Company Average / EditorialApproach

Benchmark at its natural grain. AVG(revenue) GROUP BY month — one row per month.

Join back to the detail. Every store-month row picks up its month's company_avg; the comparison revenue >= 1.2 * company_avg and the excess percentage are then row-local arithmetic.

Round for display only — the filter uses the exact average, so the store landing exactly on 120% qualifies regardless of how the average prints.

Key Insight"Compare each row to its group's aggregate" is the two-grain pattern: aggregate at the coarse grain, rejoin to the fine grain, compare. It appears as a CTE+join here, as a correlated subquery in older codebases, and as AVG(...) OVER (PARTITION BY ...) in window form — all the same idea with different ergonomics. The discipline that matters across all three: keep the benchmark *exact* until the final projection. A benchmark rounded before the comparison turns your carefully specified threshold into a slightly different one.Hints

The benchmark lives at a different grain than the rows being judged: average per MONTH, compared against each STORE-month. Compute the coarse grain in a CTE and join it back to the fine grain.

Each month has its own average — a single global AVG would flatter stores in weak months and punish them in strong ones. The join key IS the month.

Filter with the raw average (revenue >= 1.2 * company_avg) and round only the displayed columns; rounding before comparing can flip a boundary case.

This is also expressible as AVG(revenue) OVER (PARTITION BY month) — the CTE-plus-join form is the portable classic, and the one to reach for when the benchmark query grows its own filters.

Solve Stores Beating Company Average yourself →