Apple-style Company ChallengeBeginnerVerified answerSQLite live

Flagship Store Locations

Which Apple stores are Flagship locations, and how many employees work at each?

  • Filtering
  • Sorting

Challenge brief

Understand the request

Retail Operations is benchmarking staffing levels and needs a list of all flagship stores with their headcount.

List all flagship Apple stores with store name, city, state, country, and employee count.

Return

  • store_name
  • city
  • state
  • country
  • employee_count

Constraints

  • Return Flagship stores only
  • Show the largest employee counts first and resolve ties by store ID

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

stores

  • store_idINTEGER
  • store_nameVARCHAR(100)
  • store_typeVARCHAR(20)
  • cityVARCHAR(50)
  • stateVARCHAR(50)
  • countryVARCHAR(50)
  • employee_countINTEGER

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

All data is in a single table: stores. The store_type column identifies the type — filter to 'Flagship'. No JOIN required.

Hint 2

WHERE store_type = 'Flagship'. ORDER BY employee_count DESC.

Hint 3

Build question 3 from its business grain: identify the driving rows, add only valid relationships, then apply the required filtering, aggregation, and deterministic ordering.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT store_name, city, state, country, employee_count FROM stores WHERE store_type = 'Flagship' ORDER BY employee_count DESC, store_id ASC;

Why this works

Single-table query. WHERE store_type = 'Flagship' excludes Apple Beverly Hills which is a 'Standard' store. All 5 remaining stores are Flagship.

Success check

5 flagship stores — Apple Fifth Avenue leads with 350 employees. Apple Beverly Hills (Standard) is excluded.

Expected result

Use this output to verify values, aliases, ordering, and row count.

store_namecitystatecountryemployee_count
Apple Fifth AvenueNew YorkNew YorkUSA350
Apple Regent StreetLondonEnglandUK320
Apple Champs-ÉlyséesParisÎle-de-FranceFrance290
Apple Union SquareSan FranciscoCaliforniaUSA280
Apple GinzaTokyoTokyoJapan260

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.