Join targets with employee data, filter active employees, and calculate attainment
I need an attainment report before the QBR tomorrow. For every active sales rep, show me their name, year, quarter, target, actual, and attainment percentage. Sort by year and quarter. A couple things to watch: one of our reps is no longer with us but is still in the system — make sure they're excluded. Also Q4 2025 targets haven't been finalized yet so NULLs there are expected.
| Column | Type | Key |
|---|---|---|
| target_id | INT | PK |
| employee_id | INT | FK → dim_employees |
| quarter | INT | |
| year | INT | |
| target_amount | REAL | |
| actual_amount | REAL |
| Column | Type | Key |
|---|---|---|
| employee_id | INT | PK |
| first_name | TEXT | |
| last_name | TEXT | |
| TEXT | ||
| department | TEXT | |
| title | TEXT | |
| hire_date | TEXT | |
| salary | INT | |
| manager_id | INT | |
| is_active | INT |
Each hint you reveal reduces the XP you can earn. Try the query first.
You need employee names alongside their target data — those live in different tables. Also note the brief mentions one rep who left the company but is still in the system. How would you exclude them?
Attainment = actual_amount / target_amount — use ROUND(..., 2) for clean output
NULL actual_amount values (Q4 2025) will make attainment NULL too — that's fine, don't filter them out