Walmart: Price Match Approval Rate by Competitor
Walmart's pricing analysts look at price-match claims by competitor: a competitor whose advertised prices are often approved for a match is one whose prices…
- Difficulty: Medium
- Topics: Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #1006
Problem statement
Walmart's pricing analysts look at price-match claims by competitor: a competitor whose advertised prices are often approved for a match is one whose prices the stores are not keeping up with.
A claim is decided when its status is approved or denied; pending claims are ignored entirely. For every competitor with at least 3 decided claims, return competitor, decided_claims and approval_pct — the approved share of its decided claims as a percentage, rounded to 2 decimals. Order by approval_pct descending, then competitor ascending.
A practice scenario set at Walmart: the tables and data are invented, and this is not a question from, or endorsed by, Walmart.
Tables
Table: PriceMatchClaim
| Column | Type |
|---|---|
| claim_id | int |
| store_id | int |
| competitor | varchar |
| claim_date | date |
| status | enum(approved, denied, pending) |
Primary key: claim_id.
One row per price-match request raised at a register; competitor is the rival whose advertised price the customer showed.
Examples
Example 1
PriceMatchClaim
| claim_id | store_id | competitor | claim_date | status |
|---|---|---|---|---|
| 1 | 1204 | Northgate Grocers | 2025-05-02 | approved |
| 2 | 1204 | Northgate Grocers | 2025-05-03 | denied |
| 3 | 3318 | Northgate Grocers | 2025-05-03 | approved |
| 4 | 3318 | Northgate Grocers | 2025-05-04 | pending |
| 5 | 1204 | ValueKing | 2025-05-02 | approved |
| 6 | 1204 | ValueKing | 2025-05-05 | approved |
| 7 | 3318 | ValueKing | 2025-05-06 | denied |
| 8 | 1204 | MegaSave | 2025-05-02 | approved |
| 9 | 1204 | MegaSave | 2025-05-03 | approved |
| 10 | 3318 | MegaSave | 2025-05-08 | pending |
| 11 | 3318 | FreshCo Market | 2025-05-09 | denied |
| 12 | 3318 | FreshCo Market | 2025-05-09 | denied |
| 13 | 1204 | FreshCo Market | 2025-05-10 | denied |
Output
| competitor | decided_claims | approval_pct |
|---|---|---|
| Northgate Grocers | 3 | 66.67 |
| ValueKing | 3 | 66.67 |
| FreshCo Market | 3 | 0 |
How to solve Walmart: Price Match Approval Rate by Competitor
The rate is approved ÷ decided, so the first decision is what the denominator holds. Pending claims have no outcome yet; counting them would drag every competitor's rate down by however many claims happen to be in the queue. Filtering them in WHERE leaves only decided claims, and then COUNT(*) per competitor is the denominator.
The numerator is a conditional count. In MySQL a comparison is 1 or 0, so SUM(status = 'approved') counts approvals; SUM(CASE WHEN … THEN 1 ELSE 0 END) is the portable spelling. Multiply by 100 before dividing so the percentage keeps its decimals, then round to two places.
The minimum of three decided claims is a condition on the group, so it goes in HAVING — a competitor with two decisions and five pending claims is still out. Ordering by the rounded percentage and then the name is total. The alternative keeps the pending rows and counts decided ones with a CASE instead, which shows why filtering first is simpler. A single grouped scan.
Reference solution (MySQL)
SELECT competitor,
COUNT(*) AS decided_claims,
ROUND(100 * SUM(status = 'approved') / COUNT(*), 2) AS approval_pct
FROM PriceMatchClaim
WHERE status IN ('approved', 'denied')
GROUP BY competitor
HAVING COUNT(*) >= 3
ORDER BY approval_pct DESC, competitorAnother way
SELECT competitor,
SUM(CASE WHEN status <> 'pending' THEN 1 ELSE 0 END) AS decided_claims,
ROUND(100 * SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) / SUM(CASE WHEN status <> 'pending' THEN 1 ELSE 0 END), 2) AS approval_pct
FROM PriceMatchClaim
GROUP BY competitor
HAVING SUM(CASE WHEN status <> 'pending' THEN 1 ELSE 0 END) >= 3
ORDER BY approval_pct DESC, competitor← Walmart: Sam's Club Members Who Shopped Away From Home · Walmart: Shelf Gaps Still Open at the Afternoon Walk →