Walmart: Supercenters per State From Store Codes

Easy
1003.

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.

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

ColumnType
store_codevarchar
cityvarchar
opened_ondate

Primary key: store_code.

One row per store. Every code follows the pattern XX-YY-NNNN in upper case.

Example

The rows must come back in the order shown.

StoreLocation

store_codecityopened_on
SC-AR-0100Bentonville1988-03-01
SC-AR-0412Rogers1992-07-15
NM-SC-0042Columbia2010-05-20
SC-SC-0207Columbia1995-09-09
DS-TX-0310Austin1985-01-30
SC-TX-1188Dallas2001-11-02
SC-TX-2231Austin2006-04-18
NM-AR-5260Rogers2015-08-01
SC-OK-0731Tulsa1999-06-12

Output

statesupercenters
AR2
TX2
OK1
SC1
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: 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

ColumnType
store_codevarchar
cityvarchar
opened_ondate

Primary key: store_code.

One row per store. Every code follows the pattern XX-YY-NNNN in upper case.

Examples

Example 1

StoreLocation

store_codecityopened_on
SC-AR-0100Bentonville1988-03-01
SC-AR-0412Rogers1992-07-15
NM-SC-0042Columbia2010-05-20
SC-SC-0207Columbia1995-09-09
DS-TX-0310Austin1985-01-30
SC-TX-1188Dallas2001-11-02
SC-TX-2231Austin2006-04-18
NM-AR-5260Rogers2015-08-01
SC-OK-0731Tulsa1999-06-12

Output

statesupercenters
AR2
TX2
OK1
SC1

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

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