In this scenario, you’re tasked with analyzing sales data to determine if salespeople at Oracle have met their annual quotas. You need to aggregate the sales data for each employee, compare it against their assigned quota, and output whether they met or exceeded their target.

Problem Statement

You have two tables:

• deals: Contains individual deals made by each salesperson.
• sales_quotas: Contains the annual sales quota for each salesperson.

Write a query to determine if each salesperson met their annual quota. Output the employee_id and a flag indicating if they met their quota ('yes' or 'no'). The result should be ordered by employee_id in ascending order.

Table 1: deals

Table 2: sales_quotas

Example Input:

deals Table

sales_quotas Table

Example Output:

Explanation:

• Employee 101: Total sales = 400,000 + 300,000 = 700,000. Quota = 500,000. Result: yes
• Employee 201: Total sales = 500,000. Quota = 400,000. Result: yes
• Employee 301: Total sales = 500,000. Quota = 600,000. Result: no

Step-by-Step Solution

1. Aggregate Sales per Employee: Calculate the total sales for each employee using the deals table.
2. Join with Quota Data: Use the sales_quotas table to get the quota for each employee.
3. Compare Sales and Quota: Determine if the total sales meet or exceed the quota.
4. Output Results: Return the employee_id and 'yes' or 'no' based on whether they met the quota.

SQL Query

Here’s how to write the SQL query to achieve this:

SELECT 
    q.employee_id,
    CASE 
        WHEN SUM(d.deal_size) >= q.quota THEN 'yes'
        ELSE 'no'
    END AS made_quota
FROM 
    sales_quotas q
LEFT JOIN 
    deals d ON q.employee_id = d.employee_id
GROUP BY 
    q.employee_id
ORDER BY 
    q.employee_id ASC;

Explanation of the Query

1. Join the Tables (LEFT JOIN):

• LEFT JOIN is used to join sales_quotas with deals on employee_id to ensure that all employees from sales_quotas are included, even if they don’t have any deals.

1. Aggregate Deals (SUM and GROUP BY):

• SUM(d.deal_size): Sums up all deal_size values for each employee_id.
• GROUP BY q.employee_id: Groups the results by employee_id to perform the sum for each employee.

1. Conditional Logic (CASE Statement):

• CASE WHEN SUM(d.deal_size) >= q.quota THEN 'yes' ELSE 'no' END: Compares the total sales against the quota and outputs 'yes' if the quota is met or exceeded, otherwise 'no'.

1. Ordering Results (ORDER BY):

• ORDER BY q.employee_id ASC: Sorts the results by employee_id in ascending order.

Conclusion

This SQL query helps identify which salespeople have met or exceeded their sales quotas, providing valuable performance insights. It demonstrates how to use aggregate functions, joins, and conditional logic in SQL to analyze and compare data across related tables.