23 Who Made Quota Oracle SQL Interview Question
--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:
--deal_size: Deals acquired by a salesperson in the year. Each salesperson may have more than 1 deal.
--quota: Total annual quota for each salesperson.
CREATE TABLE DEALS
(
EMPLOYEE_ID INT,
DEAL_SIZE INT
)
INSERT INTO DEALS VALUES(101,400000)
INSERT INTO DEALS VALUES(101,300000)
INSERT INTO DEALS VALUES(201,500000)
INSERT INTO DEALS VALUES(301,500000)
INSERT INTO DEALS VALUES(405,100000)
INSERT INTO DEALS VALUES(405,35000)
INSERT INTO DEALS VALUES(1,150000)
INSERT INTO DEALS VALUES(1,600000)
INSERT INTO DEALS VALUES(1,250000)
INSERT INTO DEALS VALUES(1,99000)
CREATE TABLE sales_quotas
(
EMPLOYEE_ID INT,
QUOTA INT
)
INSERT INTO sales_quotas VALUES(101,500000)
INSERT INTO sales_quotas VALUES(301,600000)
INSERT INTO sales_quotas VALUES(405,150000)
INSERT INTO sales_quotas VALUES(1,950000)
INSERT INTO sales_quotas VALUES(201,400000)
SELECT * FROM DEALS
SELECT * FROM sales_quotas