SLPRS CUSTOMER SLPRS_ ID SLPRS FNAME SLPRS LNAME SLPRS TELEPHONE SLPRS_HIRE_DATE

Question

SLPRS CUSTOMER SLPRS_ ID SLPRS FNAME SLPRS LNAME SLPRS TELEPHONE SLPRS_HIRE_DATE CUST ID CUST TITLE CUST TELEPHONE CUST_ADDRESS CUST CITY CUST STATE CUST PC Lead Lead ID SLPRS ID Customer ID Date Expected date Amount Possibility Status 1. Write an SQL query to produce the average possibility of leads for each salesperson listing only those salesperson with average possibility more than 0.6 2. Write an SQL query to produce the average possibility of leads with status "I" for each salesperson listing only those salesperson with average possibility more than 0.6 3. Write an SQL query to produce an efficiency rate per salesperson. That is the (total amount* average possibility) /100.

Explanation / Answer

Answer 1)

select avg(Possibility) from Lead
where Possibility > 0.6
group by SLPRS_ID;

//Used group to list using SLPRS_ID to list according to per salesperson. Used average to calculate average of Possibility.

Answer 2)

select avg(Possibility) from Lead
where Possibility > 0.6 and Status='I'
group by SLPRS_ID;

//Added Status='I' condition to the previous query

Answer 3)

select ((sum(Amount)*avg(Possibility))/100) as Efficiency_Rate from Lead
group by SLPRS_ID;

// Used Efficiency Rate calculation as (sum(Amount)*avg(Possibility))/100

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.