Walmart: Supercenters per State From Store Codes
Walmart's real-estate analytics team only has a store code for each location and wants a count of Supercenters by state.
- Difficulty: Easy
- Topics: Strings, Aggregation
- Dialect: MySQL
- Problem: #1003
Problem statement
Walmart's real-estate analytics team only has a store code for each location and wants a count of Supercenters by state.
A store_code looks like SC-AR-0100: a two-letter format (SC Supercenter, NM Neighborhood Market, DS Discount Store), a hyphen, the two-letter state, a hyphen and a four-digit number. Count only stores whose format is SC — careful, SC is also a state. Return state and supercenters for each state with at least one Supercenter, ordered by supercenters descending, then state 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: StoreLocation
| Column | Type |
|---|---|
| store_code | varchar |
| city | varchar |
| opened_on | date |
Primary key: store_code.
One row per store. Every code follows the pattern XX-YY-NNNN in upper case.
Examples
Example 1
StoreLocation
| store_code | city | opened_on |
|---|---|---|
| SC-AR-0100 | Bentonville | 1988-03-01 |
| SC-AR-0412 | Rogers | 1992-07-15 |
| NM-SC-0042 | Columbia | 2010-05-20 |
| SC-SC-0207 | Columbia | 1995-09-09 |
| DS-TX-0310 | Austin | 1985-01-30 |
| SC-TX-1188 | Dallas | 2001-11-02 |
| SC-TX-2231 | Austin | 2006-04-18 |
| NM-AR-5260 | Rogers | 2015-08-01 |
| SC-OK-0731 | Tulsa | 1999-06-12 |
Output
| state | supercenters |
|---|---|
| AR | 2 |
| TX | 2 |
| OK | 1 |
| SC | 1 |
How to solve Walmart: Supercenters per State From Store Codes
The code packs two facts into fixed positions, so both can be read with plain string functions. LEFT(store_code, 2) is the format and SUBSTRING(store_code, 4, 2) is the state, since the format and its hyphen take the first three characters.
The trap is the letters SC, which are a format and a state. A loose test such as LIKE '%SC%' would count NM-SC-0042, a Neighborhood Market in South Carolina, as a Supercenter. Testing the first two characters — or LIKE 'SC-%', anchored at the start — counts only the format. Note that SC-SC-0207 is a Supercenter in South Carolina and rightly counts once.
After filtering, group by the extracted state and count. The order is total: count descending, then state alphabetically. The alternative reads the second hyphen-separated field with nested SUBSTRING_INDEX, which would still work if the formats ever grew to three letters. One scan of the table.
Reference solution (MySQL)
SELECT SUBSTRING(store_code, 4, 2) AS state, COUNT(*) AS supercenters
FROM StoreLocation
WHERE LEFT(store_code, 2) = 'SC'
GROUP BY state
ORDER BY supercenters DESC, stateAnother way
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(store_code, '-', 2), '-', -1) AS state, COUNT(*) AS supercenters
FROM StoreLocation
WHERE store_code LIKE 'SC-%'
GROUP BY SUBSTRING_INDEX(SUBSTRING_INDEX(store_code, '-', 2), '-', -1)
ORDER BY COUNT(*) DESC, state ASC← Walmart: Associate Shifts Clocked Over Ten Hours · Walmart: DC Trailers Dispatched Under 80% Full →