Identify all customers (customerid) who have more payment dollars in cash than electronically. Table definitions (from…
SQLLevel: IntermediateSQL scenarios
Identify all customers (customer_id) who have more payment dollars in cash than electronically.
Table definitions (from the source exercise):
CREATE TABLE customers(customer_id INTEGER PRIMARY KEY, name TEXT, city TEXT, signup_date TEXT, credit_score INTEGER);
CREATE TABLE payments(payment_id INTEGER PRIMARY KEY, customer_id INTEGER, payment_date TEXT, amount REAL, method TEXT);
Hints
- Define one output row and the source-table keys.
- Check duplicates, NULLs and the order of filtering and aggregation.
Answer
Approach: Conditional aggregation: build both totals with SUM(CASE WHEN ...) and compare them in HAVING.
SELECT customer_id
FROM payments
GROUP BY customer_id
HAVING SUM(CASE WHEN method = 'cash' THEN amount ELSE 0 END)
> SUM(CASE WHEN method = 'electronic' THEN amount ELSE 0 END);
The source uses SQLite with a MySQL compatibility layer. Date and string functions are dialect dependent; the query is preserved as published.
Source and licence: SQL Practice Lab — Ebrahim Sharifi · MIT