Apple: Trade-In Credit Paid by Device Family — SQL Problem
The trade-in programme reports credit by device family — the first word of the traded model, so iPhone 13 Pro and iPhone 12 are both iPhone, while MacBook…
- Difficulty: Easy
- Topics: Strings, Aggregation
- Dialect: MySQL
- Problem: #602
Problem statement
The trade-in programme reports credit by device family — the first word of the traded model, so iPhone 13 Pro and iPhone 12 are both iPhone, while MacBook Air is MacBook.
Considering only trade-ins with status accepted, return family, accepted_trades (how many) and total_credit (the sum of credit_usd). Families with no accepted trade-in do not appear. Return the rows in any order.
A practice scenario set at Apple: the tables and data are invented, and this is not a question from, or endorsed by, Apple.
Tables
Table: TradeIn
| Column | Type |
|---|---|
| trade_id | int |
| device_model | varchar |
| device_condition | enum(good, fair, poor) |
| credit_usd | int |
| status | enum(accepted, rejected, pending) |
Primary key: trade_id.
One row per device sent in for credit. device_model is always at least two words separated by single spaces.
Examples
Example 1
TradeIn
| trade_id | device_model | device_condition | credit_usd | status |
|---|---|---|---|---|
| 1 | iPhone 13 Pro | good | 320 | accepted |
| 2 | iPhone 12 | fair | 170 | accepted |
| 3 | iPad Air | good | 210 | rejected |
| 4 | MacBook Air | good | 450 | accepted |
| 5 | MacBook Pro | poor | 300 | accepted |
| 6 | Watch SE | fair | 60 | pending |
| 7 | iPhone 14 | good | 400 | rejected |
Output
| family | accepted_trades | total_credit |
|---|---|---|
| MacBook | 2 | 750 |
| iPhone | 2 | 490 |
How to solve Apple: Trade-In Credit Paid by Device Family
The family is not a column — it is derived from the model's text. SUBSTRING_INDEX(device_model, ' ', 1) returns everything before the first space, which turns iPhone 13 Pro into iPhone and MacBook Air into MacBook. Grouping by that expression puts every model of a family into one group.
The status filter runs in WHERE, before grouping, so rejected and pending trade-ins contribute nothing; a family whose trade-ins were all rejected never forms a group and is left out, as the statement asks. Inside each group COUNT(*) is the number of accepted trades and SUM(credit_usd) the credit paid.
The same prefix can be cut with LEFT(device_model, LOCATE(' ', device_model) - 1) or SUBSTRING(…, 1, INSTR(…) - 1); computing it in a derived table lets you group by a plain alias. The query is one scan of the table plus a small hash aggregate.
Reference solution (MySQL)
SELECT SUBSTRING_INDEX(device_model, ' ', 1) AS family,
COUNT(*) AS accepted_trades,
SUM(credit_usd) AS total_credit
FROM TradeIn
WHERE status = 'accepted'
GROUP BY SUBSTRING_INDEX(device_model, ' ', 1)Another way
SELECT family, COUNT(*) AS accepted_trades, SUM(credit_usd) AS total_credit FROM (SELECT LEFT(device_model, LOCATE(' ', device_model) - 1) AS family, credit_usd FROM TradeIn WHERE status = 'accepted') t GROUP BY familyAnother way
SELECT SUBSTRING(device_model, 1, INSTR(device_model, ' ') - 1) AS family, SUM(1) AS accepted_trades, SUM(credit_usd) AS total_credit FROM TradeIn WHERE status IN ('accepted') GROUP BY SUBSTRING(device_model, 1, INSTR(device_model, ' ') - 1)← Apple: AppleCare Claims Filed in a Device's First 30 Days · Apple: App Store Apps Not Updated Since Mid-2023 →