medium Retail Expansion Analysis

The whole query is a set-intersection done with two GROUP BY rollups: one pass groups rows by rev_2020 and keeps the groups of size > 1 (revenue shared with someone), a second pass groups rows by the coordinate pair and keeps the groups of size = 1 (location belongs to exactly one store), and a store survives only if it is in both result sets — so you join the base table to both and SUM the survivors' rev_2021.

Problem

Table StorePerformance(store_id PK, rev_2020 float, rev_2021 float, lat float, lon float). Return the total rev_2021 across stores that (a) share their rev_2020 with at least one other store, and (b) sit at a coordinate pair no other store occupies.

store_id | rev_2020 | rev_2021 | lat  | lon
   1     |   100    |   150    | 10.0 | 10.0
   2     |   200    |   250    | 20.0 | 20.0
   3     |   100    |   300    | 20.0 | 20.0
   4     |   100    |   400    | 40.0 | 40.0

Expected output: a single value 550.00.

The two conditions are independent filters

Notice the trap in the data: rev_2020 = 100 is shared by three stores (1, 3, 4), so all three pass the revenue test. But store 3 sits at (20.0, 20.0) — the same pair as store 2 — so it fails the location test. Each condition slices a different axis; only stores that survive both count. The diagram traces which stores fall out at each gate.

The query

Each gate is its own GROUP BY … HAVING derived table; the base table is inner-joined to both, which keeps only the rows present in both survivor sets, then summed.

SELECT FORMAT(SUM(sp.rev_2021), 2) AS rev_2021
FROM StorePerformance sp
JOIN (                              -- Gate A: revenue shared with another store
    SELECT rev_2020
    FROM StorePerformance
    GROUP BY rev_2020
    HAVING COUNT(*) > 1
) dup_rev   ON sp.rev_2020 = dup_rev.rev_2020
JOIN (                              -- Gate B: this store owns its coordinate alone
    SELECT lat, lon
    FROM StorePerformance
    GROUP BY lat, lon
    HAVING COUNT(*) = 1
) uniq_loc  ON sp.lat = uniq_loc.lat AND sp.lon = uniq_loc.lon;

Step-by-step trace

Survivors are stores 1 and 4. SUM(rev_2021) = 150 + 400 = 550, and FORMAT(550, 2) renders the string "550.00".

Watch store 3 specifically: it passes Gate A on revenue but is killed by Gate B because its coordinate group (20.0, 20.0) has COUNT(*) = 2. That row is exactly why the two filters cannot be collapsed into one condition.

Pitfalls

1. FORMAT returns a string, not a number

FORMAT(x, 2) in MySQL produces a locale-formatted string like "550.00" — and for large values it inserts grouping commas ("1,234,567.00"). The instant you wrap a number in FORMAT you have left numeric land. Consequences that bite in real reports:

• Sorting breaks: ORDER BY FORMAT(total,2) sorts lexicographically, so "1,000.00" sorts before "950.00" because '1' < '9' as text.
• Arithmetic / comparisons silently coerce: WHERE FORMAT(total,2) > 1000 coerces "1,234.00" back to the number 1 (parsing stops at the comma), so the row is wrongly excluded.
• Downstream code: a client expecting a numeric column now receives text. Format for display at the very end, in the application layer; keep the SQL value numeric (ROUND(SUM(rev_2021), 2)) if anything downstream will compute on it.

2. Float equality is the real trap in this problem

Both gates hinge on equality of float columns: GROUP BY rev_2020, sp.rev_2020 = dup_rev.rev_2020, and sp.lat = uniq_loc.lat. FLOAT/DOUBLE are binary IEEE-754 — they cannot represent most decimal fractions exactly. Two stores you think have the same revenue may differ in the last bit, so GROUP BY places them in separate groups and HAVING COUNT(*) > 1 never fires. The toy data uses clean integers stored as floats (100, 10.0) which are exact, so the bug stays hidden here — but on real data rev_2020 = 19.99 entered from two sources can compare unequal. Group/equate on an exact type: store money as DECIMAL(p,s), or compare with a tolerance (ABS(a - b) < 1e-9) when you are stuck with floats. Coordinates have the same hazard, made worse by rounding at different precisions on ingest.

3. Same-store self-match and NULLs

Gate A's COUNT(*) > 1 counts rows, which is what you want — but if you ever rewrite it as a self-join (a.rev_2020 = b.rev_2020 AND a.store_id <> b.store_id) remember to exclude the row matching itself. Also, GROUP BY collapses all NULL coordinates into one group, so a pile of stores with unknown lat/lon would look like a single shared location and all fail Gate B; filter or treat NULLs deliberately if they exist.

Takeaways

• Two independent membership tests → two GROUP BY … HAVING derived tables inner-joined to the base; the join performs the set intersection for you.
• HAVING COUNT(*) > 1 means "shared"; HAVING COUNT(*) = 1 means "unique" — the same idiom flipped picks duplicates vs. singletons.
• FORMAT yields a comma-grouped string: presentation only. Never sort, compare, or compute on its output — keep the value numeric until the final display layer.
• Grouping or equating on FLOAT is a correctness landmine; the test data hides it. Use DECIMAL for money or an epsilon comparison for floats.

Re-authored and deepened for this guide. Mechanism (GROUP BY/HAVING as set membership, then join-as-intersection), the float-equality and FORMAT-returns-a-string traps, NULL grouping, and the IEEE-754 reasoning draw on the MySQL Reference Manual (sections on FORMAT(), the FLOAT/DECIMAL types, and "Problems with Floating-Point Values"), the SQL standard's treatment of GROUP BY/HAVING, and the LeetCode-style "Retail Expansion Analysis" problem this lesson is built on. Worked example verified: stores 1 + 4 = 150 + 400 = 550.00.