Build one activity feed for customer 1: a row per event with eventtype ('transaction' or 'payment'), eventdate, and…
SQLLevel: IntermediateBonus drills (mixed difficulty)
Build one activity feed for customer 1: a row per event with event_type ('transaction' or 'payment'), event_date, and amount. Transactions come through the accounts join; payments directly. Order by date.
Table definitions (from the source exercise):
CREATE TABLE accounts(account_id INTEGER PRIMARY KEY, customer_id INTEGER, product_type TEXT, open_date TEXT, credit_limit INTEGER, status TEXT);
CREATE TABLE transactions(transaction_id INTEGER PRIMARY KEY, account_id INTEGER, transaction_date TEXT, amount REAL, merchant_category TEXT);
CREATE TABLE payments(payment_id INTEGER PRIMARY KEY, customer_id INTEGER, payment_date TEXT, amount REAL, method TEXT);
Hints
- Write the join keys and relationship cardinality.
- What happens to duplicates and unmatched rows? Check WHERE versus ON.
Answer
Approach: Two SELECTs with matching column lists glued by UNION ALL, then one ORDER BY at the end. Literal strings make the event_type column.
SELECT 'transaction' AS event_type, t.transaction_date AS event_date, t.amount
FROM accounts a
JOIN transactions t ON t.account_id = a.account_id
WHERE a.customer_id = 1
UNION ALL
SELECT 'payment', payment_date, amount
FROM payments
WHERE customer_id = 1
ORDER BY event_date;
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