Apple-style Company ChallengeBeginnerVerified answerSQLite live

Orders Using Apple Pay

Which orders were paid using Apple Pay, and who made them?

  • Joins
  • Date analysis
  • Filtering
  • Sorting

Challenge brief

Understand the request

Apple Pay Analytics wants to measure Apple Pay adoption by reviewing all orders completed via the platform.

List all Apple Pay orders with order ID, customer full name, order date, and total amount.

Return

  • order_id
  • customer_name (first + last)
  • order_date
  • total_amount

Constraints

  • Return orders paid with Apple Pay
  • Show the newest orders first and resolve timestamp ties by order ID

Data you will use

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

orders

  • order_idINTEGER
  • customer_idINTEGER
  • order_dateDATETIME
  • total_amountREAL
  • payment_methodVARCHAR(50)

customers

  • customer_idINTEGER
  • first_nameVARCHAR(50)
  • last_nameVARCHAR(50)

Hints, when you need them

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

Hint 1

Orders are in orders, customer names are in customers. Join on customer_id. Filter payment_method to 'Apple Pay'.

Hint 2

INNER JOIN orders to customers on customer_id. WHERE o.payment_method = 'Apple Pay'. Concatenate name. ORDER BY o.order_date DESC.

Hint 3

Build question 4 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 o.order_id, c.first_name || ' ' || c.last_name AS customer_name, o.order_date, o.total_amount FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id WHERE o.payment_method = 'Apple Pay' ORDER BY o.order_date DESC, o.order_id DESC;

Why this works

5 of 10 orders used Apple Pay. The other 5 used Credit Card or Debit Card. Alice Johnson is the most frequent Apple Pay user with 3 transactions.

Success check

5 Apple Pay orders — Alice Johnson used Apple Pay for 3 of them

Expected result

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

order_idcustomer_nameorder_datetotal_amount
2009Alice Johnson2024-02-08 12:00:002499
2007Emma Davis2024-02-03 10:20:00799
2005David Brown2024-01-25 11:00:00399
2003Alice Johnson2024-01-20 09:15:00249
2001Alice Johnson2024-01-15 10:30:00999

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.