Active Businesses

A window AVG(occurrences) OVER (PARTITION BY event_type) hangs the per-event-type average onto every row of that event type without collapsing the rows, so a single pass over Events lets you compare each occurrence against its own event's mean and then count, per business, how many events cleared the bar.

The problem, precisely

Table Events(business_id, event_type, occurrences) with primary key (business_id, event_type) — one row per business per event type. The average activity of an event type is the mean of occurrences across all businesses that have that event. A business is active if it has more than one event type whose occurrences is strictly greater than that event type's average. Find every active business_id.

The whole task is two reductions stacked: first reduce within each event type (compute its average), then reduce within each business (count how many of its events beat their average). The window function does the first reduction; GROUP BY business_id ... HAVING COUNT(*) > 1 does the second.

The query

SELECT business_id
FROM (
    SELECT business_id,
           occurrences,
           AVG(occurrences) OVER (PARTITION BY event_type) AS avgo
    FROM   Events
) x
WHERE  occurrences > avgo
GROUP  BY business_id
HAVING COUNT(business_id) > 1;

Read it inside-out. The subquery x labels each row with avgo = the average occurrences of its event type, computed across every business sharing that event type. The outer query keeps only rows that strictly beat their avgo, then groups by business and demands at least two surviving rows. COUNT(business_id) here counts the rows that passed the WHERE filter — i.e. the number of above-average events for that business — not the total events it has.

Worked example, traced

Start from this Events table:

Step 1 — the window adds avgo. Each partition is one event type; the average is over all rows in that partition:

• reviews: (7 + 3) / 2 = 5.0
• ads: (11 + 7 + 6) / 3 = 8.0
• page views: (3 + 12) / 2 = 7.5

That value is broadcast back onto every row, and rows are not collapsed:

Step 2 — filter, group, count. Keep only the yes rows, group by business, count survivors:

Final result: business_id = 1.

The subtlety the average hides

The window average for an event type is computed over all rows in that partition — including the very row being labelled. Business 1's ads occurrence of 11 is part of the (11 + 7 + 6)/3 = 8.0 it is then compared against. This is exactly the intended semantics here ("average across all companies that have this event"), but it is worth seeing the mechanics: a large outlier inflates its own bar and makes it slightly harder to clear. With one extreme value, that bar can rise above every member of the partition, so a row can lose to an average it dominated. The diagram below makes this self-inclusion concrete.

Why a window beats the self-aggregate join

The pre-window way to write this needs a derived table of averages and a join back:

-- equivalent, but heavier
SELECT e.business_id
FROM   Events e
JOIN  (SELECT event_type, AVG(occurrences) AS avgo
       FROM   Events
       GROUP  BY event_type) a
  ON   a.event_type = e.event_type
WHERE  e.occurrences > a.avgo
GROUP  BY e.business_id
HAVING COUNT(*) > 1;

It gives the same answer, but it reads Events twice and joins them. The window version reads the table once: the engine partitions the already-loaded rows by event_type, computes one average per partition, and writes it back onto each row in place — no second scan, no join, no chance of the join silently dropping rows. When occurrences can be NULL or an event type can have a single business, the GROUP BY + join requires care that the equality join joins on exactly the right key; the window keeps the row-to-average correspondence automatic because the average travels with the row. For a self-comparison-to-group-average pattern, that is the canonical reason to reach for a window function instead of a self-join.

Pitfalls

• Filtering the window in the same SELECT. WHERE occurrences > AVG(...) OVER (...) is illegal — window functions are evaluated after WHERE, so you cannot reference avgo there. You must wrap the window in a subquery (or CTE) and filter in the outer query. This is the most common reason this query "won't run."
• >= instead of >. The spec says strictly greater. Using >= would, for example, admit a row exactly equal to its event average and change which businesses qualify. Match the wording exactly.
• COUNT(*) > 0 vs > 1. "More than one event type" means at least two above-average events, so the threshold is > 1. Off-by-one here returns businesses with a single strong event.
• Integer-average truncation. In some engines, averaging an INT column can return an integer (e.g. page views average becomes 7 instead of 7.5), flipping a boundary comparison. AVG returns a decimal in standard SQL/MySQL, but if you reimplement it as SUM/COUNT over integers, cast first.
• Forgetting that the row counts toward its own average. If the requirement were "above the average of the other businesses," this query would be subtly wrong — you would need to exclude the current row from the partition average. Always confirm whether self-inclusion is intended.

Takeaways

• A PARTITION BY window broadcasts a group aggregate onto each row without collapsing rows — the natural tool for "compare each row to its group's average."
• Window functions run after WHERE, so any filter on a window value must live in an outer query or CTE.
• The window average includes the current row; that matches this spec, but break the partition with care when the requirement says "versus the others."
• One scan + in-place labelling beats a self-aggregate join on both performance and on avoiding join-key footguns.

Based on LeetCode 1126 "Active Businesses" (the standard Events dataset and expected output). Window-function semantics per the MySQL 8.0 Reference Manual ("Window Function Concepts and Syntax") and the SQL:2003 windowing model; the rule that window functions are evaluated after the WHERE/GROUP BY/HAVING phases follows the SQL logical processing order. Re-authored and deepened for this guide: added the self-inclusion subtlety of the partition average, a window-vs-self-join comparison with the equivalent join query, the strict-vs-non-strict and integer-truncation pitfalls, and a step-by-step traced diagram.