Apple-style Company ChallengeBeginnerVerified answerSQLite live

Discontinued Products

Which Apple products have been discontinued and are no longer for sale?

  • Filtering
  • Sorting

Challenge brief

Understand the request

Product Lifecycle Team is auditing the product catalog and needs a list of all discontinued models for archiving.

List all discontinued products with name, model, category, storage, colour, and original price.

Return

  • product_name
  • model
  • category
  • storage_capacity
  • color
  • price

Constraints

  • Return discontinued products only
  • Order by product ID

Data you will use

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

products

  • product_nameVARCHAR(100)
  • modelVARCHAR(50)
  • categoryVARCHAR(50)
  • storage_capacityVARCHAR(20)
  • colorVARCHAR(30)
  • priceREAL
  • discontinuedINTEGER

Hints, when you need them

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

Hint 1

All product data is in the products table. The discontinued column is a 1/0 integer flag — 1 means discontinued.

Hint 2

Single-table query: WHERE discontinued = 1. No JOIN required.

Hint 3

Build question 11 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 product_name, model, category, storage_capacity, color, price FROM products WHERE discontinued = 1 ORDER BY product_id

Why this works

discontinued is stored as 1/0. WHERE discontinued = 1 returns only the iPhone 12. All other 9 products have discontinued = 0 (active).

Success check

1 product — iPhone 12 (64GB, Black, $599)

Expected result

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

product_namemodelcategorystorage_capacitycolorprice
iPhone 1212iPhone64GBBlack599

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.