This query shows the active sales reps ranked by revenue. Amount is tallied from closed and won opportunities by the sales reps derived from Salesforce data.
WITH salesforce_user AS (
SELECT
id, email, firstname || ' ' || lastname AS "rep"
FROM
public.salesforce_user
WHERE
salesforce_user.IsActive
)
SELECT
us.rep AS "sales_rep",
us.email,
sum(op.amount) AS "revenue"
FROM
public.salesforce_opportunity op
JOIN
salesforce_user us
ON op.ownerid = us.id
WHERE
op.stagename = 'Closed Won'
AND DATE_TRUNC('quarter', "closedate") = DATE_TRUNC('quarter', current_date)
GROUP BY
1,
2
ORDER BY
revenue DESC
Column | Description |
---|---|
sales_rep |
Opportunity Owner (Sales Rep) |
email |
Sales Rep Email |
revenue |
Sum of amount from closed/won opportunities by sales rep. |