Task
You are given purchase data from the Apidel Technologies platform. Write a PostgreSQL query to return the first product each customer purchased. If a customer has multiple purchases at the exact same earliest timestamp, return the one with the smaller order_id as the tie-breaker.
Your result should include the customer ID, customer name, first order ID, purchase timestamp, and product name. Only customers with at least one valid product-linked order should appear in the output.
Schema
| Table | Column | Type | Description |
|---|
| customers | customer_id | INT | Unique customer identifier |
| customers | customer_name | VARCHAR(100) | Customer name |
| orders | order_id | INT | Unique order identifier |
| orders | customer_id | INT | Customer who placed the order |
| orders | product_id | INT | Purchased product |
| orders | purchased_at | TIMESTAMP | Purchase timestamp |
| products | product_id | INT | Unique product identifier |
| products | product_name | VARCHAR(100) | Product name |
Sample data
| customer_id | customer_name | order_id | product_id | purchased_at | product_name |
|---|
| 1 | Ava Patel | 102 | 203 | 2024-01-05 09:15:00 | Smart Thermostat |
| 1 | Ava Patel | 101 | 201 | 2024-01-03 10:00:00 | Cloud Hub |
| 2 | Noah Kim | 104 | 202 | 2024-01-02 08:30:00 | Sensor Tag |
| 2 | Noah Kim | 103 | 204 | 2024-01-02 08:30:00 | Access Panel |
| | | | | |
Expected output
| customer_id | customer_name | first_order_id | first_purchase_at | first_product_name |
|---|
| 1 | Ava Patel | 101 | 2024-01-03 10:00:00 | Cloud Hub |
| 2 | Noah Kim | 103 | 2024-01-02 08:30:00 | Access Panel |
