Back to questions
As a data analyst on the Oracle Sales Operations team, you are given a list of salespeople’s deals, and the annual quota they need to hit.
Write a query that outputs each employee id and whether they hit the quota or not ('yes' or 'no'). Order the results by employee id in ascending order.
Definitions:
Column Name | Type |
---|---|
employee_id | integer |
deal_size | integer |
employee_id | deal_size |
---|---|
101 | 400000 |
101 | 300000 |
201 | 500000 |
301 | 500000 |
Column Name | Type |
---|---|
employee_id | integer |
quota | integer |
employee_id | quota |
---|---|
101 | 500000 |
201 | 400000 |
301 | 600000 |
employee_id | made_quota |
---|---|
101 | yes |
201 | yes |
301 | no |
User 101 had $700k in sales, beating their $500k quota. User 201 had $500k in sales, beating their $400k quota. User 301 had $500k in sales, but had a $600k quota, so they didn't hit their goal.
The dataset you are querying against may have different input & output - this is just an example!
These are the summarized steps:
Let's start off by exploring the dataset. Note that it includes several deals per employee. Thus, as the first step, we should group the dataset, and obtain the total deal amount for each employee.
As the last step, we use conditional statement and comparison operator to compare the total deal size and the quota, and allocate 'yes' or 'no' respectively. Also, do not forget to order results by !