Welcome to the SQL screen.
The question is on your right: Second-Highest Segment Transaction Value. Read through the requirements and the three tables first.
Run and submit your code as often as you need. You also have five interviewer messages this session - want to talk through your approach, or are you ready to start coding?
You are given transaction data and customer segment metadata from a McKinsey client analytics environment. Write a SQL query to return the second-highest distinct transaction value for each customer segment. If a segment has fewer than two distinct non-null transaction values, it should not appear in the result.
Use the customer-to-segment relationship rather than relying on any segment value stored elsewhere, and return the segment name with the second-highest value.
| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Unique customer identifier |
| customer_name | VARCHAR(100) | Customer full name |
| segment_id | INT | Segment assigned to the customer |
| Column | Type | Description |
|---|---|---|
| segment_idPK | INT | Unique segment identifier |
| segment_name | VARCHAR(50) | Segment label |
| Column | Type | Description |
|---|---|---|
| transaction_idPK | INT | Unique transaction identifier |
| customer_id | INT | Customer who made the transaction |
| transaction_value | DECIMAL(10,2) | Transaction amount |
| transaction_date | DATE | Date of transaction |