Your question is SQL Joins for Transactions. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Supernova Technology needs a monthly customer transaction summary for its internal reporting pipeline. Write a PostgreSQL query that combines customer records with qualifying transactions and includes optional transaction notes.
INNER JOIN between customers and transactions so only transactions belonging to known customers are included.LEFT JOIN to retain qualifying transactions that do not have notes.completed status, then return one row per customer with transaction count and total spend.High spend to totals at least 500, Medium to totals at least 200, and Low otherwise. Concatenate available notes in transaction order, returning No notes when none exist.| Column | Type | Description |
|---|---|---|
| customer_idPK | INTEGER | Unique customer identifier |
| customer_name | VARCHAR(100) | Customer display name |
| region | VARCHAR(40) | Customer sales region |
| Column | Type | Description |
|---|---|---|
| transaction_idPK | INTEGER | Unique transaction identifier |
| customer_id | INTEGER | Customer associated with the transaction |
| transaction_date | DATE | Date the transaction occurred |
| status | VARCHAR(20) | Transaction processing status |
| amount | NUMERIC(10,2) | Transaction amount |
| Column | Type | Description |
|---|---|---|
| note_idPK | INTEGER | Unique note identifier |
| transaction_id | INTEGER | Transaction associated with the note |
| note_text | VARCHAR(255) | Operational note text |