Apple-style Company ChallengeMediumVerified answerSQLite live

Store Performance Comparison

For each retail order location with at least one delivered order, show total revenue, average transaction value, order count, and its rank among retail locations.

  • Window functions
  • Joins
  • Aggregation
  • Numeric functions
  • Filtering

Challenge brief

Understand the request

Retail Analytics Retail Operations needs delivered-order performance by the actual order location relationship to compare revenue and transaction volume without relying on coincident store identifiers.

Compare store revenue for Delivered orders only, ranking each store within its type using RANK() OVER (PARTITION BY store_type).

Return

  • location_name
  • location_type
  • total_revenue
  • avg_transaction
  • order_count
  • rank_in_type

Constraints

  • Use the order location relationship rather than unrelated store identifiers
  • Return delivered orders at locations whose type is 'Store'
  • Locations with equal revenue share a rank and display alphabetically

Data you will use

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

locations

  • location_idINTEGER
  • location_nameVARCHAR(100)
  • cityVARCHAR(50)
  • stateVARCHAR(50)
  • countryVARCHAR(50)
  • location_typeVARCHAR(20)

orders

  • order_idINTEGER
  • location_idINTEGER
  • total_amountREAL
  • statusVARCHAR(50)

Hints, when you need them

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

Hint 1

Orders connect to stores via location_id (not a direct store_id column in orders). JOIN orders to stores on o.location_id = s.store_id. Filter to Delivered status. GROUP BY store. RANK() OVER (PARTITION BY store_type ORDER BY SUM(total_amount) DESC).

Hint 2

INNER JOIN stores to orders on s.store_id = o.location_id. WHERE o.status = 'Delivered'. GROUP BY store. RANK() OVER (PARTITION BY s.store_type ORDER BY SUM(o.total_amount) DESC).

Hint 3

Build question 10 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 l.location_name, l.location_type, ROUND(SUM(o.total_amount), 2) AS total_revenue, ROUND(AVG(o.total_amount), 2) AS avg_transaction, COUNT(o.order_id) AS order_count, RANK() OVER (PARTITION BY l.location_type ORDER BY SUM(o.total_amount) DESC) AS rank_in_type FROM locations l INNER JOIN orders o ON l.location_id = o.location_id WHERE l.location_type = 'Store' AND o.status = 'Delivered' GROUP BY l.location_id, l.location_name, l.location_type ORDER BY l.location_type, rank_in_type, l.location_name;

Why this works

Join orders to the referenced locations table on location_id. The separate stores table has no declared relationship to orders, so joining its store_id would produce plausible but incorrectly shifted store names.

Success check

3 retail locations; Apple Store Fifth Avenue ranks first with $4,945 delivered revenue, while Regent Street and Union Square share rank 2.

Expected result

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

location_namelocation_typetotal_revenueavg_transactionorder_countrank_in_type
Apple Store Fifth AvenueStore49451236.2541
Apple Store Regent StreetStore1299129912
Apple Store Union SquareStore1299129912

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.