Skip to content
Quantoros

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

Related questions