Walmart: Price Match Approval Rate by Competitor

Medium
1006.

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 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

ColumnTypeValues
claim_idint
store_idint
competitorvarchar
claim_datedate
statusenumapproved, 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.

Example

The rows must come back in the order shown.

PriceMatchClaim

claim_idstore_idcompetitorclaim_datestatus
11204Northgate Grocers2025-05-02approved
21204Northgate Grocers2025-05-03denied
33318Northgate Grocers2025-05-03approved
43318Northgate Grocers2025-05-04pending
51204ValueKing2025-05-02approved
61204ValueKing2025-05-05approved
73318ValueKing2025-05-06denied
81204MegaSave2025-05-02approved
91204MegaSave2025-05-03approved
103318MegaSave2025-05-08pending
3 more rows

Output

competitordecided_claimsapproval_pct
Northgate Grocers366.67
ValueKing366.67
FreshCo Market30
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
claim_idint
store_idint
competitorvarchar
claim_datedate
statusenum(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_idstore_idcompetitorclaim_datestatus
11204Northgate Grocers2025-05-02approved
21204Northgate Grocers2025-05-03denied
33318Northgate Grocers2025-05-03approved
43318Northgate Grocers2025-05-04pending
51204ValueKing2025-05-02approved
61204ValueKing2025-05-05approved
73318ValueKing2025-05-06denied
81204MegaSave2025-05-02approved
91204MegaSave2025-05-03approved
103318MegaSave2025-05-08pending
113318FreshCo Market2025-05-09denied
123318FreshCo Market2025-05-09denied
131204FreshCo Market2025-05-10denied

Output

competitordecided_claimsapproval_pct
Northgate Grocers366.67
ValueKing366.67
FreshCo Market30

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, competitor

Another 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 →