Loyal Customers

Story

Marketing wants to thank customers who keep coming back. You get the shop's three tables and must produce the loyalty report: every customer with at least two delivered orders, and how much they spent on them.

Task

Write one SQL SELECT statement over these tables:

| table | columns | |---|---| | customers | id, name, country | | orders | id, customer_id → customers, status (delivered, cancelled or pending) | | order_items | id, order_id → orders, quantity, unit_price_cents |

Rules:

• Only orders with status delivered count, both for the number of orders and for the money. Cancelled and pending orders are ignored completely.
• An order is worth the sum of quantity * unit_price_cents over its items. A delivered order with no item rows still counts as a delivered order, worth 0.
• Report only customers with at least 2 delivered orders.

Return one row per customer with the columns name, country, delivered_orders, total_cents (in that order), sorted by total_cents descending, then name ascending.

How your answer is graded

Submit only the SQL (one statement, a trailing ; is fine; no three double quotes in a row). A hidden Python program builds the three tables with different data for each test, runs your statement read-only and prints every row with | between the values ((no rows) when the result is empty). The language is Python because of that wrapper; experiment with the playground below.

Example

Playground (paste into Code Lab, change query, run):

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, country TEXT NOT NULL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), status TEXT NOT NULL);
CREATE TABLE order_items (id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders(id),
                          quantity INTEGER NOT NULL, unit_price_cents INTEGER NOT NULL);
INSERT INTO customers VALUES
  (1, 'Amira Hassan', 'Egypt'), (2, 'Ben Carter', 'UK'), (3, 'Chiyo Tanaka', 'Japan'),
  (4, 'Diego Alves', 'Brazil'), (5, 'Esther Kamau', 'Kenya'), (6, 'Farid Nasser', 'Egypt');
INSERT INTO orders VALUES
  (101, 1, 'delivered'), (102, 1, 'delivered'), (103, 1, 'cancelled'), (104, 2, 'delivered'),
  (105, 2, 'pending'), (106, 3, 'delivered'), (107, 3, 'delivered'), (108, 3, 'delivered'),
  (109, 4, 'cancelled'), (110, 4, 'cancelled'), (111, 5, 'delivered'), (112, 5, 'delivered'),
  (113, 6, 'delivered'), (114, 6, 'delivered');
INSERT INTO order_items VALUES
  (1, 101, 2, 1500), (2, 101, 1, 2500), (3, 102, 1, 4000), (4, 103, 5, 1000), (5, 104, 3, 1000),
  (6, 106, 1, 9900), (7, 107, 2, 1200), (8, 107, 1, 300), (9, 107, 4, 250), (10, 108, 1, 5000),
  (11, 111, 10, 200), (12, 112, 1, 1000), (13, 113, 2, 2750);
""")

query = """
SELECT name, country FROM customers;
"""
for row in db.execute(query):
    print("|".join(map(str, row)))

The correct statement prints exactly:

Chiyo Tanaka|Japan|3|18600
Amira Hassan|Egypt|2|9500
Farid Nasser|Egypt|2|5500
Esther Kamau|Kenya|2|3000

Ben has only one delivered order and Diego none, so they are left out. Farid's second order (114) has no items but still counts.