SQL 101 for Salesforce Users
A practical intro to SQL for Salesforce admins and ops folks: the basics, a few advanced topics like UNION, CTEs and window functions, and ready-to-run examples for pipeline, bookings, win rate and lead conversion.
Joe Fusaro
Founder, Datachef
This post is for people who spend their day in Salesforce (admins, sales and marketing ops, RevOps analysts) and have never written a line of SQL. We'll start with the basics, move on to a few things reports and SOQL just can't do, and finish with example queries you can copy and run against your own data.
Here's the good news. If you've ever built a Salesforce report, you already know more SQL than you think. A report is just a question: which records, which fields, which filters, grouped how. SQL is another way to write that question down, minus the report-type limits.
Table of Contents
- How does SQL compare with SOQL?
- Where does the Salesforce data live?
- The basics
- Advanced topics
- Examples
How does SQL compare with SOQL?
SOQL (Salesforce Object Query Language) is what you use to query Salesforce directly, in the Developer Console, Workbench or Apex. It looks a lot like SQL, and plenty carries over: SELECT, FROM, WHERE, ORDER BY, LIMIT, GROUP BY, COUNT and SUM all work. But SOQL was made for fetching records, not analyzing them. Here's what you give up:
- Joins only through lookup fields. In SOQL, two objects can only be connected if there's a lookup or master-detail field between them, like an opportunity and its account. SQL will join any two tables on any value they have in common. Leads and contacts on email address, for instance.
- No UNION. There's no way to get leads and contacts into one list with a single query.
- No CTEs, and no subqueries in FROM. You get subqueries in a few narrow spots, like
WHERE Id IN (SELECT AccountId FROM Opportunity)to filter on another object's ids, and that's about it. Building a query in steps, or querying the output of another query, isn't possible. - No window functions. SOQL will give you each account's biggest open deal as a nested list. What it won't do is rank every deal within its account, number a rep's wins in order or keep a running total of bookings.
- Limited math and logic. There's no
CASE WHEN, and you can't calculate something likeamount * probabilityinside the query.
I go deeper on this in Overcoming the limits of SOQL. Several examples further down (the UNION, the CTE, the join on email) are things SOQL simply can't do.
Where does the Salesforce data live?
SQL needs the data to sit somewhere that can run SQL. Once Salesforce is synced, each object turns into a table: Opportunity is salesforce.opportunity, Account is salesforce.account, and so on.
Columns keep the API names you already know from Setup (stagename, amount, ownerid). Case doesn't matter, so StageName and stagename mean the same thing.
salesforce is just the name of my Salesforce connection. If yours is named something else, say salesforce_dev, the table becomes salesforce_dev.opportunity. Not sure? The table list in the SQL editor shows the exact names.
Other ways to run SQL on Salesforce data
There are a few common routes for getting Salesforce data into something you can query with SQL:
- A cloud data warehouse. Copy Salesforce into Snowflake, BigQuery or Redshift, normally with an ELT tool like Fivetran or Airbyte keeping it in sync. Most data teams run some version of this. It's powerful, but it's also several tools to run (the pipeline, the warehouse, usually a BI tool on top) and several bills to pay.
- A Postgres or MySQL database. Load Salesforce data into a database, either on your laptop or hosted, from Data Loader CSV exports or a script that hits the Salesforce API. It's cheap, and for a one-off analysis it's fine. Just keep in mind that you need to periodically refresh (i.e. upsert) your local copy, manage schema changes, etc.
- Data 360. Data 360 (formerly Data Cloud) is Salesforce's own data platform. It pulls your CRM data in next to your other sources, and you can query it with SQL from its Query Editor or the Query API. It can also share data with Snowflake, Databricks and others without making a copy. One caveat: the SQL in this post won't run there as-is, because Data 360 renames every object and field.
SELECT name, amount FROM salesforce.opportunitybecomes something likeSELECT "Name__c", "Amount__c" FROM "Opportunity_Home__dll", depending on how your data streams are set up. - A full-stack data platform like Datachef. It syncs Salesforce for you, keeps the tables up to date and gives you a SQL editor to query them, with no pipeline or warehouse to run.
The queries in this post are written in generic SQL that runs as-is in Datachef, Postgres and Snowflake. To keep them that way, they skip two handy shortcuts only some databases have, FILTER (WHERE ...) and QUALIFY, and use a CASE WHEN and a ROW_NUMBER() CTE instead. MySQL needs a few edits:
- Drop
NULLS LAST. MySQL doesn't support it, and already puts blanks last when you sortDESC. - Swap the date functions.
date_truncbecomesDATE_FORMAT, the minute arithmetic becomesTIMESTAMPDIFF, and casts go toDATETIMErather thanTIMESTAMP.
The basics
You'll use these in pretty much every query you write.
Selecting columns
SELECT name, stagename, amount, closedate
FROM salesforce.opportunity
SELECT lists the columns you want; FROM says which table. SELECT * gets you every column (the asterisk means "all"). Handy when you're poking around a table. But an opportunity has dozens of columns, so in a real query you'll usually want to name just the ones you need.
Rename a column with AS, like SELECT stagename AS stage. That shows up a lot below.
Filtering with WHERE
SELECT name, stagename, amount
FROM salesforce.opportunity
WHERE isclosed = false
AND amount > 50000
Think of WHERE as your report filter. Combine conditions with AND and OR. For "any of these," use IN: WHERE type IN ('New Business', 'Upsell').
For pipeline I filter on isclosed = false instead of listing out every open stage. That way, when someone adds a new stage next quarter, nothing breaks.
Sorting and limiting
SELECT name, amount
FROM salesforce.opportunity
WHERE isclosed = false
ORDER BY amount DESC NULLS LAST
LIMIT 10
ORDER BY amount DESC puts the biggest deals at the top. (ASC is the default and goes smallest first.) Add LIMIT 10 and you've got your top ten open deals.
Aggregate functions
SELECT COUNT(*) AS open_deals,
SUM(amount) AS pipeline,
AVG(amount) AS average_deal,
MAX(amount) AS biggest_deal
FROM salesforce.opportunity
WHERE isclosed = false
These are the summary numbers at the bottom of a report. COUNT(*) counts rows, SUM totals them, AVG averages them, and MIN and MAX give you the extremes. One gotcha: blank amounts get skipped. Decide whether a blank should mean zero before you trust that total.
COUNT(CASE WHEN ... THEN 1 END) counts only the rows that match a condition, because COUNT skips the blanks the CASE leaves for every other row. That's how you put total deals and won deals next to each other.
COUNT(DISTINCT ...) counts unique values rather than rows. So, how many accounts have open pipeline, and how many reps own it:
SELECT COUNT(*) AS open_deals,
COUNT(DISTINCT accountid) AS accounts,
COUNT(DISTINCT ownerid) AS reps
FROM salesforce.opportunity
WHERE isclosed = false
An account with three open deals adds three to open_deals but only one to accounts.
Grouping with GROUP BY
SELECT stagename AS stage,
COUNT(*) AS deals,
SUM(amount) AS pipeline
FROM salesforce.opportunity
WHERE isclosed = false
GROUP BY stagename
GROUP BY gives you one row per value, the same as grouping a report by Stage. Rule of thumb: if something's in your SELECT and it isn't wrapped in a function like SUM, it goes in the GROUP BY. GROUP BY 1 is shorthand for "group by the first column."
Labeling rows with CASE WHEN
CASE WHEN is SQL's version of if/then. It does the same job as a bucket field in a report:
SELECT CASE WHEN amount >= 100000 THEN 'Large'
WHEN amount >= 25000 THEN 'Medium'
WHEN amount > 0 THEN 'Small'
ELSE 'No amount'
END AS deal_size,
COUNT(*) AS open_deals,
SUM(amount) AS pipeline
FROM salesforce.opportunity
WHERE isclosed = false
GROUP BY 1
ORDER BY pipeline DESC NULLS LAST
Conditions are checked top to bottom, and a row gets the label of the first one that's true. A $150,000 deal is "Large," even though it's also over $25,000. Anything left over, blank amounts included, falls through to ELSE.
Joining tables
SELECT o.name AS opportunity,
a.name AS account,
o.amount
FROM salesforce.opportunity o
JOIN salesforce.account a ON a.id = o.accountid
An opportunity only stores its account's id; the name lives on the account. A JOIN connects the two using the value they share, accountid on the opportunity and id on the account. Salesforce calls that a lookup. SQL calls it a join.
o and a are just short nicknames for the tables, so it's clear whose name is whose.
A plain JOIN only keeps rows that match on both sides. A LEFT JOIN keeps every row from the first table whether it matches or not, and leaves the other side blank. Reach for it when "no match" is part of what you're asking, like finding accounts with no opportunities.
Advanced topics
Now for the stuff reports can't do.
Combining leads and contacts with UNION
People in Salesforce live in two places, leads and contacts. UNION ALL stacks two queries on top of each other:
SELECT 'Lead' AS type, name, email, company AS account_name
FROM salesforce.lead
WHERE isconverted = false
AND isdeleted = false
UNION ALL
SELECT 'Contact', c.name, c.email, a.name
FROM salesforce.contact c
LEFT JOIN salesforce.account a ON a.id = c.accountid
WHERE c.isdeleted = false
Both halves need the same columns in the same order. Don't skip isconverted = false: a converted lead also exists as a contact, and without that filter you'd count those people twice. isdeleted = false keeps out anything sitting in the Recycle Bin.
Plain UNION (no ALL) also strips out exact duplicate rows. It's slower and can hide problems, so I stick with UNION ALL unless I actually want the de-duplication.
Breaking up a query with CTEs
A CTE (the WITH at the top) lets you give a query a name and then use it like a table further down:
WITH people AS (
SELECT LOWER(TRIM(email)) AS email FROM salesforce.lead WHERE isconverted = false
UNION ALL
SELECT LOWER(TRIM(email)) FROM salesforce.contact
)
SELECT email, COUNT(*) AS records
FROM people
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY records DESC
This finds email addresses that show up on more than one lead or contact. HAVING is basically a WHERE for groups. It filters after the counting. LOWER(TRIM(...)) cleans up the emails first, since Jane@Acme.com and jane@acme.com are the same person.
The big win with CTEs is readability. Every step gets a name, and you can run each one by itself to check it.
Joining more than two tables
There's no limit on how many tables you can join. Here's a problem most Salesforce teams have: someone who's already a contact fills out a form, and Salesforce creates a brand-new lead for them. This query finds those leads by matching their email against existing contacts. Then it pulls in the contact's account, the lead owner, the account owner and how many open deals the account has. (You can't do this in a Salesforce report, because a lead isn't linked to a contact until it's converted.)
SELECT l.id AS lead_id,
l.name AS lead_name,
lo.name AS lead_owner,
a.name AS account,
ao.name AS account_owner,
COUNT(DISTINCT CASE WHEN o.isclosed = false THEN o.id END) AS open_deals
FROM salesforce.lead l
JOIN salesforce.contact c
ON LOWER(TRIM(c.email)) = LOWER(TRIM(l.email))
JOIN salesforce.account a ON a.id = c.accountid
LEFT JOIN salesforce.user lo ON lo.id = l.ownerid
JOIN salesforce.user ao ON ao.id = a.ownerid
LEFT JOIN salesforce.opportunity o ON o.accountid = a.id
WHERE l.isconverted = false
GROUP BY 1, 2, 3, 4, 5
ORDER BY open_deals DESC
A couple of details. COUNT(DISTINCT o.id) counts each deal once, even when the lead's email matches two contacts at the same account. And the lead id is in the GROUP BY because names aren't unique.
The user table appears twice, as lo (lead owner) and ao (account owner), since it's answering two different questions. The lead owner is a LEFT JOIN because leads can be owned by a queue instead of a user.
Sorting by open_deals floats leads at accounts with active deals to the top. Check those first, especially the ones where the lead owner and the account owner aren't the same person.
Picking one row per group with ROW_NUMBER
Say you want your biggest won deal in each industry, maybe to pick a reference customer or a case study. Not a total; the actual deal. For that you need a window function, which calculates across a group of rows but keeps every row:
WITH ranked AS (
SELECT COALESCE(a.industry, '(none)') AS industry,
a.name AS account,
o.name AS largest_deal,
o.amount,
o.closedate,
ROW_NUMBER() OVER (PARTITION BY a.industry
ORDER BY o.amount DESC NULLS LAST) AS rn
FROM salesforce.opportunity o
JOIN salesforce.account a ON a.id = o.accountid
WHERE o.iswon = true
)
SELECT industry, account, largest_deal, amount, closedate
FROM ranked
WHERE rn = 1
ORDER BY amount DESC NULLS LAST
ROW_NUMBER() OVER (PARTITION BY a.industry ORDER BY o.amount DESC NULLS LAST) numbers the won deals inside each industry, biggest first. The outer query keeps rn = 1, the top deal per industry. (Some databases, like Snowflake, have a shortcut for this called QUALIFY, which filters on the row number without the CTE.)
Swap the pieces around and you get other useful versions. PARTITION BY o.ownerid gives each rep's biggest win; ORDER BY o.closedate DESC gives the most recent win in each industry.
Examples
Open pipeline by stage
SELECT stagename AS stage,
COUNT(*) AS deals,
ROUND(SUM(amount), 2) AS pipeline_value,
ROUND(AVG(amount), 2) AS average_deal
FROM salesforce.opportunity
WHERE isclosed = false
GROUP BY 1
ORDER BY pipeline_value DESC NULLS LAST
Everything in flight right now and what it's worth. NULLS LAST pushes stages with no amounts to the bottom.
Bookings by month
SELECT date_trunc('month', CAST(closedate AS DATE)) AS month,
COUNT(*) AS deals_won,
ROUND(SUM(amount), 2) AS booked
FROM salesforce.opportunity
WHERE iswon = true
GROUP BY 1
ORDER BY 1
date_trunc('month', ...) rounds each close date back to the first of its month, which drops every deal into a month bucket. It looks good as a bar or line chart.
Accounts with the most open pipeline
SELECT a.id AS account_id,
a.name AS account,
a.industry,
COUNT(o.id) AS open_deals,
ROUND(SUM(o.amount), 2) AS open_pipeline
FROM salesforce.opportunity o
JOIN salesforce.account a ON a.id = o.accountid
WHERE o.isclosed = false
GROUP BY 1, 2, 3
ORDER BY open_pipeline DESC NULLS LAST
LIMIT 25
A join, a filter and a group: most of the basics in one query. Grouping by account id, and not just the name, keeps two accounts that happen to share a name apart.
Win rate by owner
SELECT u.id AS owner_id,
u.name AS owner,
COUNT(*) AS closed_deals,
COUNT(CASE WHEN o.iswon = true THEN 1 END) AS won,
ROUND(COUNT(CASE WHEN o.iswon = true THEN 1 END) * 100.0
/ NULLIF(COUNT(*), 0), 1) AS win_rate_pct,
ROUND(SUM(CASE WHEN o.iswon = true THEN o.amount END), 2) AS booked
FROM salesforce.opportunity o
JOIN salesforce.user u ON u.id = o.ownerid
WHERE o.isclosed = true
GROUP BY 1, 2
ORDER BY booked DESC NULLS LAST
Only closed deals count here, so open pipeline can't make the rate look better than it is. NULLIF(COUNT(*), 0) avoids dividing by zero, and * 100.0 turns the result into a percentage with decimals.
Lead engagement and conversion by source
For this one, a lead counts as engaged if it has at least one task (a call, an email, a to-do) logged after it was created:
WITH engaged AS (
SELECT DISTINCT l.id AS lead_id
FROM salesforce.lead l
JOIN salesforce.task t
ON t.whoid IN (l.id, l.convertedcontactid)
AND CAST(t.createddate AS TIMESTAMP) >= CAST(l.createddate AS TIMESTAMP)
WHERE t.isdeleted = false
)
SELECT COALESCE(l.leadsource, '(none)') AS source,
COUNT(*) AS leads,
COUNT(e.lead_id) AS engaged,
ROUND(COUNT(e.lead_id) * 100.0
/ NULLIF(COUNT(*), 0), 1) AS engaged_pct,
COUNT(CASE WHEN l.isconverted = true THEN 1 END) AS converted,
ROUND(COUNT(CASE WHEN l.isconverted = true THEN 1 END) * 100.0
/ NULLIF(COUNT(*), 0), 1) AS conversion_pct
FROM salesforce.lead l
LEFT JOIN engaged e ON e.lead_id = l.id
WHERE l.isdeleted = false
GROUP BY 1
ORDER BY leads DESC
The engaged CTE finds every lead with a task dated after the lead was created. Tasks point to a lead or contact through whoid, and when a lead converts, Salesforce moves its tasks over to the new contact. So the join checks both the lead id and convertedcontactid, which means converted leads still count as engaged. DISTINCT keeps it to one row per lead, no matter how many tasks it has.
The main query LEFT JOINs that list onto all leads, so COUNT(e.lead_id) only counts the engaged ones (it skips blanks). COALESCE gives leads with no source a row of their own, '(none)'.
With engaged and converted side by side, you can see where leads get stuck. Low engagement means nobody's working that source. High engagement but low conversion means it's being worked; it's just not turning into deals.
You'll need the Task object synced for this one.
Lead follow-up velocity and conversion
How quickly does someone follow up on a new lead, and do the fast follow-ups convert better? Here follow-up velocity means the time between a lead being created and the first task or event logged against it:
WITH activities AS (
SELECT whoid, createddate FROM salesforce.task WHERE isdeleted = false
UNION ALL
SELECT whoid, createddate FROM salesforce.event WHERE isdeleted = false
),
first_touch AS (
SELECT l.id,
l.isconverted,
EXTRACT(EPOCH FROM
date_trunc('minute', MIN(CAST(a.createddate AS TIMESTAMP)))
- date_trunc('minute', CAST(l.createddate AS TIMESTAMP))
) / 60 AS minutes_to_first_touch
FROM salesforce.lead l
LEFT JOIN activities a
ON a.whoid IN (l.id, l.convertedcontactid)
AND CAST(a.createddate AS TIMESTAMP) >= CAST(l.createddate AS TIMESTAMP)
WHERE l.isdeleted = false
GROUP BY l.id, l.isconverted, l.createddate
)
SELECT CASE WHEN minutes_to_first_touch IS NULL THEN '5. Never'
WHEN minutes_to_first_touch < 60 THEN '1. Within an hour'
WHEN minutes_to_first_touch < 1440 THEN '2. Within a day'
WHEN minutes_to_first_touch < 10080 THEN '3. Within a week'
ELSE '4. Over a week'
END AS follow_up,
COUNT(*) AS leads,
COUNT(CASE WHEN isconverted = true THEN 1 END) AS converted,
ROUND(COUNT(CASE WHEN isconverted = true THEN 1 END) * 100.0
/ NULLIF(COUNT(*), 0), 1) AS conversion_pct
FROM first_touch
GROUP BY 1
ORDER BY 1
This one uses a bit of everything from this post:
- UNION ALL stacks tasks and events into one list of activities, because a follow-up could be either.
- The
first_touchCTE gets each lead's earliest activity withMIN, anddate_diffturns the gap into minutes. Like the last example, it checks the converted contact too, since Salesforce moves a lead's activities there when it converts. TheLEFT JOINkeeps leads nobody ever followed up on (they show up blank). - CASE WHEN sorts the minutes into buckets. There are 1,440 minutes in a day and 10,080 in a week. The numbers at the start of each label are only there to keep them in order.
- Conversion % per bucket is the payoff. If "Within an hour" converts noticeably better than "Over a week," speed matters for your team.
Remember, this is a correlation. Reps might just be jumping on the leads that already look promising. If the pattern holds inside a single lead source (add leadsource to the SELECT and GROUP BY), you've got a much stronger case for a faster follow-up rule. You'll need Task and Event synced for this one.
Once a query becomes the way your team defines something, like pipeline or win rate, save it as a model. A model is a saved, versioned query that anyone in the workspace can query like a table, so everyone's working from the same number. And when Salesforce sits next to the rest of your data (Marketo, Google Ads, your product database), these same joins are how you tie it all together.
Related reading
Want to try these queries on your own Salesforce data? Watch a demo, or try Datachef free.