Question

1. Construct a query to count how many encounters resulted in the customer declining to make a purchase. Use the COUNT function. (Hint: a customer can have many encounters)

2. Customers with low annual income or poor credit ratings often have trouble qualifying for credit. A new, local credit agency may be better able to serve this market segment than the current options can. The manager would like to talk to a few salespeople who have had the most encounters with these customers. Create a list that shows each salesperson (first and last name) and the number of encounters each has had with customers that have an annual income of $25,000 or less or have a credit description that is "Very Poor" or "Extremely Poor." Order the results alphabetically by the salesperson’s last name. Your query should have three columns: sFirstName, sLastName, encounter_count.

Answer 1

To count declined purchase encounters, a COUNT query is used. For the list of salespeople encountering customers with low income or poor credit, a JOIN is implemented along with GROUP BY and ORDER BY clauses, filtering according to income and credit conditions.

Step-by-step explanation:

To count how many encounters resulted in the customer declining a purchase, you could use a query similar to the following:

SELECT COUNT(*) FROM Encounters WHERE outcome = 'Declined'

Note: The actual table and column names might vary and should be replaced with the correct names from your database.

To create a list of salespeople with the number of encounters they've had with customers with low annual income or poor credit ratings, use this query:

SELECT sFirstName, sLastName, COUNT(*) as encounter_count
FROM Encounters
JOIN Customers ON Encounters.customerID = Customers.customerID
JOIN Salespeople ON Encounters.salespersonID = Salespeople.salespersonID
WHERE annualIncome <= 25000
OR creditDescription IN ('Very Poor', 'Extremely Poor')
GROUP BY sFirstName, sLastName
ORDER BY sLastName;

This query joins the necessary tables, filters by the specified conditions for income and credit rating, groups by salesperson, and orders the results.

Answer 2

1.

SELECT COUNT(*) FROM encounters WHERE customer_purchased = 'No'

2.

SELECT sFirstName, sLastName, COUNT(*) AS encounter_count

FROM encounters

JOIN salespeople ON encounters.salesperson_id = salespeople.id

WHERE encounters.customer_income <= 25000 OR encounters.customer_credit_description IN ('Very Poor', 'Extremely Poor')

GROUP BY salespeople.id

ORDER BY sLastName ASC;

Step-by-step explanation:

1. The first query uses the COUNT function to count the number of encounters where the customer declined to make a purchase. It selects the count of all rows in the encounters table where the value of the customer_purchased column is 'No'.
2. The second query joins the encounters table with the salespeople table using the salesperson_id column. It then selects the first and last names of the salespeople, and counts the number of encounters for each salesperson where the customer has an annual income of $25,000 or less, or a credit description of "Very Poor" or "Extremely Poor". The results are grouped by salesperson ID and ordered alphabetically by the salesperson's last name. The SELECT clause uses an alias to rename the COUNT(*) column as encounter_count for readability.