Problem
Write a PostgreSQL query to return the second-highest distinct transaction amount for each client. Include client_id, client_name, and second_highest_amount. Exclude clients that do not have at least two distinct non-NULL transaction amounts.
Schema
| Column | Type | Description |
|---|---|---|
| client_id | ||
| client_name | ||
| segment |
| Column | Type | Description |
|---|---|---|
| transaction_id | ||
| client_id | ||
| amount | ||
| transaction_date |
Representative Rows
clients
| 1 | Ava Patel | Institutional |
| 4 | Daniel Kim | Institutional |
| 6 | Farah Ali | Retail |
transactions
| 101 | 1 | 900.00 | 2024-03-12 |
| 102 | 1 | 500.00 | 2024-02-10 |
| 109 | 4 | 1100.00 | 2024-03-15 |
| 114 | 6 | 450.00 | 2024-02-21 |
Practicing as: Data Scientist interview at The AmeripriseHi, I'll play your The Ameriprise interviewer for the Data Scientist role. Candidates describe these interviews as mostly positive and moderately difficult, so expect me to be friendly and conversational. Take your time with the question above and answer like we're in the room.
You are practicing as a guest. Sign up free to run your code against the sample data. Your draft stays right here.
