HarbourVest PartnersMediumSQL · PostgreSQL00:00Welcome to the SQL screen for the Operations Manager role at HarbourVest Partners.
The question is on your right: Analyze Decision Impact by Region. Read through the requirements and the three tables first.
Would you like to talk through your approach, or are you ready to start coding?
You are given transaction, decision, and client data from an investment operations workflow. Write a PostgreSQL query that helps you decide whether a process change improved outcomes: for each region, return the number of decisions made, the number of successful outcomes, and the success rate for decisions made in Q2 2024. Only include regions with at least 2 decisions, and sort by success rate descending, then region ascending.
| Column | Type | Description |
|---|---|---|
| decision_idPK | INT | Primary key for each decision record |
| client_id | INT | Client associated with the decision |
| decision_date | DATE | Date the decision was made |
| decision_type | VARCHAR(50) | Type of decision |
| Column | Type | Description |
|---|---|---|
| decision_id | INT | Foreign key to decisions.decision_id |
| outcome_status | VARCHAR(20) | Outcome status such as success or failure |
| resolved_at | TIMESTAMP | When the outcome was recorded |
| Column | Type | Description |
|---|---|---|
| client_idPK | INT | Primary key for each client |
| region | VARCHAR(50) | Client region |
| account_name | VARCHAR(100) | Client account name |