Apple: Trade-In Credit Paid by Device Family

Easy
602.

Apple: Trade-In Credit Paid by Device Family

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

ColumnTypeValues
trade_idint
device_modelvarchar
device_conditionenumgood, fair, poor
credit_usdint
statusenumaccepted, 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.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

TradeIn

trade_iddevice_modeldevice_conditioncredit_usdstatus
1iPhone 13 Progood320accepted
2iPhone 12fair170accepted
3iPad Airgood210rejected
4MacBook Airgood450accepted
5MacBook Propoor300accepted
6Watch SEfair60pending
7iPhone 14good400rejected

Output

familyaccepted_tradestotal_credit
MacBook2750
iPhone2490
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.

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

ColumnType
trade_idint
device_modelvarchar
device_conditionenum(good, fair, poor)
credit_usdint
statusenum(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_iddevice_modeldevice_conditioncredit_usdstatus
1iPhone 13 Progood320accepted
2iPhone 12fair170accepted
3iPad Airgood210rejected
4MacBook Airgood450accepted
5MacBook Propoor300accepted
6Watch SEfair60pending
7iPhone 14good400rejected

Output

familyaccepted_tradestotal_credit
MacBook2750
iPhone2490

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 family

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