SQL for Beginners: Write Simple Queries That Work

You don’t need to become a data engineer to answer real business questions with data. A handful of SQL patterns will get you 80% of the way: pull clean lists, filter by date or channel, join two sources, and roll up by campaign or month. This tutorial shows you the minimum SQL you actually need - plus copy/paste queries you can run against common marketing and sales tables.

If you run revenue, you run questions: Which channels created pipeline last month? What’s the lag from first touch to opportunity? Which SKUs drag down margin? SQL gives you a direct line to answers - without waiting a week for a dashboard request to climb a backlog.

SQL is not about becoming the analyst. It’s about independence. You can validate someone’s chart, spot a data definition mismatch, or pressure-test an assumption before you approve budget. The syntax is human-readable, and the concepts map to how you already think: lists, filters, groups, and joins.

And when you can run one query yourself, your conversations change. Instead of “Can someone pull this?” you say, “I sampled 3 months of transactions; paid search drove 38% of contribution margin after returns - here’s the query.” That’s an operator’s advantage.

Every query starts the same way: choose columns, choose a table, add filters. You’ll use these three more than anything else.

Imagine a leads table in your data warehouse or CRM export:

-- leads
id (int)
created_at (timestamp)
source (text)
campaign (text)
status (text) -- e.g., New, Qualified, Disqualified
owner (text)
company (text)

List the latest 100 leads to spot-check data quality:

SELECT id, created_at, source, campaign, status, owner
FROM leads
ORDER BY created_at DESC
LIMIT 100;

Filter to paid search leads from this quarter to focus your analysis:

SELECT id, created_at, campaign, status
FROM leads
WHERE source = 'Paid Search'
  AND created_at >= DATE_TRUNC('quarter', CURRENT_DATE);

Key ideas:

• SELECT is your columns list (what you want to see).
• FROM is where the data lives (table or view).
• WHERE narrows the set to what matters.

Most business questions start open-ended. Sorting and limiting help you see the shape of the data quickly, then zoom in.

Find the five most recent enterprise leads:

SELECT id, company, created_at
FROM leads
WHERE company ILIKE '%Inc%' OR company ILIKE '%LLC%'
ORDER BY created_at DESC
LIMIT 5;

Get unique campaigns you actually used last month to reconcile with your media plan:

SELECT DISTINCT campaign
FROM leads
WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
  AND created_at < DATE_TRUNC('month', CURRENT_DATE);

Sort by multiple columns when tie-breakers matter, like recent first and then by owner name:

SELECT id, owner, created_at
FROM leads
ORDER BY created_at DESC, owner ASC;

Rollups answer “how much,” “how many,” or “what’s the average.” Use GROUP BY for the dimension (campaign, channel, month) and aggregate functions for the metrics (COUNT, SUM, AVG).

Leads by source this month:

SELECT source, COUNT(*) AS leads
FROM leads
WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY source
ORDER BY leads DESC;

Qualified rate by campaign, showing only campaigns with at least 30 leads (filter aggregates with HAVING):

SELECT campaign,
       COUNT(*) AS leads,
       SUM(CASE WHEN status = 'Qualified' THEN 1 ELSE 0 END) AS qualified,
       ROUND(100.0 * SUM(CASE WHEN status = 'Qualified' THEN 1 ELSE 0 END) / COUNT(*), 1) AS qualified_rate_pct
FROM leads
WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '2 month')
GROUP BY campaign
HAVING COUNT(*) >= 30
ORDER BY qualified_rate_pct DESC;

Revenue by month helps you see seasonality fast:

SELECT DATE_TRUNC('month', o.order_date) AS month,
       SUM(o.net_revenue) AS revenue,
       SUM(o.gross_margin) AS margin
FROM orders o
GROUP BY 1
ORDER BY 1;

JOIN combines tables. INNER JOIN keeps matches only. LEFT JOIN keeps everything from the left table - even if there’s no match on the right. In business reporting, LEFT JOIN is your friend when you don’t want to lose rows (e.g., a lead without an opportunity yet).

Example schema:

-- opportunities
id, lead_id, stage, amount, created_at, closed_at

-- orders
id, opportunity_id, order_date, net_revenue, gross_margin

Attach opportunity amounts to leads (keep all leads):

SELECT l.id AS lead_id, l.created_at AS lead_created,
       o.id AS opp_id, o.stage, o.amount
FROM leads l
LEFT JOIN opportunities o ON o.lead_id = l.id
WHERE l.created_at >= DATE_TRUNC('month', CURRENT_DATE);

Attribute revenue back to lead source (only for closed-won opps with orders):

SELECT l.source,
       SUM(ord.net_revenue) AS revenue
FROM opportunities o
INNER JOIN leads l ON l.id = o.lead_id
INNER JOIN orders ord ON ord.opportunity_id = o.id
WHERE o.stage = 'Closed Won'
GROUP BY l.source
ORDER BY revenue DESC;

Common pitfalls:

• Duplicate rows come from one-to-many joins (e.g., one opportunity with many orders). Aggregate after the join or de-duplicate first.
• Join on the correct key. lead_id to id, not email to id.
• When in doubt, test with COUNT(*) and sanity-check totals.

Time is the backbone of reporting. You’ll slice by month, week, or quarter; measure lag; and build cohorts.

Monthly lead-to-opportunity conversion:

WITH lead_to_opp AS (
  SELECT DATE_TRUNC('month', l.created_at) AS lead_month,
         COUNT(DISTINCT l.id) AS leads,
         COUNT(DISTINCT o.id) AS opps
  FROM leads l
  LEFT JOIN opportunities o ON o.lead_id = l.id
  GROUP BY 1
)
SELECT lead_month,
       leads,
       opps,
       ROUND(100.0 * opps / NULLIF(leads, 0), 1) AS lead_to_opp_rate_pct
FROM lead_to_opp
ORDER BY lead_month;

Average days from lead to first opportunity (simple lag):

SELECT ROUND(AVG(EXTRACT(EPOCH FROM (o.created_at - l.created_at)) / 86400.0), 1) AS avg_days_to_opp
FROM leads l
JOIN opportunities o ON o.lead_id = l.id
WHERE l.created_at >= DATE_TRUNC('year', CURRENT_DATE);

Cohort retention (e.g., share of customers who reorder in 90 days):

WITH first_order AS (
  SELECT customer_id, MIN(order_date) AS first_date
  FROM orders
  GROUP BY 1
), repeaters AS (
  SELECT o.customer_id
  FROM orders o
  JOIN first_order f ON f.customer_id = o.customer_id
  WHERE o.order_date > f.first_date AND o.order_date <= f.first_date + INTERVAL '90 days'
)
SELECT DATE_TRUNC('month', f.first_date) AS cohort_month,
       COUNT(*) AS customers,
       SUM(CASE WHEN r.customer_id IS NOT NULL THEN 1 ELSE 0 END) AS repeaters,
       ROUND(100.0 * SUM(CASE WHEN r.customer_id IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*), 1) AS repeat_rate_90d_pct
FROM first_order f
LEFT JOIN repeaters r ON r.customer_id = f.customer_id
GROUP BY 1
ORDER BY 1;

If you’re fluent in spreadsheets, half of SQL will feel familiar. Translate habits like this:

VLOOKUP becomes JOIN. Instead of matching on a key in Excel, you join two tables on that key:

-- Excel: =VLOOKUP(A2, Campaigns!A:B, 2, FALSE)
-- SQL equivalent:
SELECT l.id, c.budget
FROM leads l
LEFT JOIN campaigns c ON c.campaign = l.campaign;

SUMIF/COUNTIF becomes SUM/COUNT with CASE WHEN inside. You control conditions inline:

SELECT
  SUM(CASE WHEN source = 'Paid Search' THEN 1 ELSE 0 END) AS paid_search_leads,
  SUM(CASE WHEN status = 'Qualified' THEN 1 ELSE 0 END) AS qualified_leads
FROM leads;

PIVOT tables become GROUP BY. Rows turn into grouped aggregations, then you can pivot in BI if needed:

SELECT source, DATE_TRUNC('month', created_at) AS month, COUNT(*) AS leads
FROM leads
GROUP BY source, DATE_TRUNC('month', created_at)
ORDER BY month, source;

SQL is powerful. Use it safely so you don’t degrade systems or expose data you shouldn’t see.

Golden rules:

• Read-only connection for ad-hoc queries. Never write or delete from production tables.
• Sample first. LIMIT 100 and WHERE clauses before scanning millions of rows.
• Protect PII. Mask email, phone, and addresses when exporting or sharing.

If your team runs on HubSpot or a similar CRM, route analytics through your warehouse or a read-replica. Operational systems are for running the business; warehouses are for analysis. If you don’t have clean objects and lifecycle stages in your CRM, start there; you’ll fight your schema forever if the source is messy. If you need help operationalizing CRM data models, a pragmatic starting point is a focused CRM implementation that aligns lifecycle stages, required fields, and data syncs.

You don’t need a massive data stack. You need a reliable place to query and a few well-modeled tables. A minimal setup looks like this: data source exports (CRM, ads, billing) land in a warehouse; a handful of views simplify the schema; your team queries those views via a SQL editor.

What matters most is naming and consistency. Call a thing what it is. Standardize column names like created_at, updated_at, source, campaign, amount. Add views that turn raw, event-y data into business entities: leads_enriched, opportunities_enriched, orders_clean. These are your “business-ready” tables for everyday questions.

Pick one easy SQL surface your team likes - BigQuery console, Redshift Query Editor, Snowflake Worksheets, or even a lightweight client. Store your useful queries in a shared repo or folder and add a short header to each with the purpose, inputs, and known caveats. That tiny habit saves hours.

An operator’s note: scaling without hiring a full team

Many teams stall here - not because SQL is hard, but because ops work gets scattered across agencies, freelancers, and internal owners. One senior operator can stitch GTM, data, and channels into a single system. That’s the model at Anton Chisliuk · AI Marketing Ops: one builder who ships CRM, SEO, paid, and automation in 90-day sprints. It’s not theory - e.g., for Cleverence (B2B SaaS) we went 0→80K organic visits/month and 0→2K inbound leads/month. If you’re curious how an operator model works in practice and the economics behind it, skim the about page.

Here are practical snippets you can adapt to your schema. Replace table/column names to match your environment.

1) Leads by channel with qualified rate:

SELECT source,
       COUNT(*) AS leads,
       SUM(CASE WHEN status = 'Qualified' THEN 1 ELSE 0 END) AS qualified,
       ROUND(100.0 * SUM(CASE WHEN status = 'Qualified' THEN 1 ELSE 0 END) / COUNT(*), 1) AS qualified_rate_pct
FROM leads
WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY source
ORDER BY leads DESC;

2) Opportunities created vs. closed by week:

WITH created AS (
  SELECT DATE_TRUNC('week', created_at) AS wk, COUNT(*) AS created_cnt
  FROM opportunities
  GROUP BY 1
), closed AS (
  SELECT DATE_TRUNC('week', closed_at) AS wk, COUNT(*) AS closed_cnt
  FROM opportunities
  WHERE stage = 'Closed Won'
  GROUP BY 1
)
SELECT c.wk,
       c.created_cnt,
       COALESCE(cl.closed_cnt, 0) AS closed_cnt
FROM created c
LEFT JOIN closed cl ON cl.wk = c.wk
ORDER BY c.wk;

3) Pipeline by stage and owner:

SELECT owner, stage, COUNT(*) AS opps, SUM(amount) AS pipeline
FROM opportunities
WHERE stage NOT IN ('Closed Won', 'Closed Lost')
GROUP BY owner, stage
ORDER BY owner, stage;

4) CAC by channel (using ad spend table):

-- ad_spend: date, channel, spend
-- leads: created_at, source, id
SELECT s.channel,
       SUM(s.spend) AS spend,
       COUNT(l.id) AS leads,
       ROUND(SUM(s.spend) / NULLIF(COUNT(l.id), 0), 2) AS cac
FROM ad_spend s
LEFT JOIN leads l
  ON l.source = s.channel
 AND DATE_TRUNC('month', l.created_at) = DATE_TRUNC('month', s.date)
WHERE s.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '2 month')
GROUP BY s.channel
ORDER BY cac;

5) Revenue attribution by first-touch source:

-- first_touch_source on lead; orders linked via opportunity
SELECT l.source AS first_touch,
       SUM(o.net_revenue) AS revenue
FROM orders o
JOIN opportunities opp ON opp.id = o.opportunity_id
JOIN leads l ON l.id = opp.lead_id
GROUP BY l.source
ORDER BY revenue DESC;

6) Average sales cycle length by segment:

SELECT segment,
       ROUND(AVG(EXTRACT(EPOCH FROM (closed_at - created_at)) / 86400.0), 1) AS avg_days
FROM opportunities
WHERE stage = 'Closed Won'
GROUP BY segment
ORDER BY avg_days;

7) Product mix and margin contribution:

SELECT sku,
       SUM(quantity) AS units,
       SUM(net_revenue) AS revenue,
       SUM(gross_margin) AS margin,
       ROUND(100.0 * SUM(gross_margin) / NULLIF(SUM(net_revenue), 0), 1) AS margin_pct
FROM order_lines
GROUP BY sku
ORDER BY margin DESC;

8) RFM-style recency segments:

WITH last_order AS (
  SELECT customer_id, MAX(order_date) AS last_dt
  FROM orders
  GROUP BY 1
)
SELECT customer_id,
       CASE
         WHEN last_dt >= CURRENT_DATE - INTERVAL '30 days' THEN '0-30d'
         WHEN last_dt >= CURRENT_DATE - INTERVAL '90 days' THEN '31-90d'
         ELSE '90d+'
       END AS recency_bucket
FROM last_order;

9) Return rate by channel:

-- returns table links to order_lines
SELECT l.source,
       ROUND(100.0 * SUM(CASE WHEN rl.returned = TRUE THEN rl.quantity ELSE 0 END)
             / NULLIF(SUM(ol.quantity), 0), 1) AS return_rate_pct
FROM order_lines ol
JOIN orders o ON o.id = ol.order_id
JOIN opportunities opp ON opp.id = o.opportunity_id
JOIN leads l ON l.id = opp.lead_id
LEFT JOIN return_lines rl ON rl.order_line_id = ol.id
GROUP BY l.source
ORDER BY return_rate_pct DESC;

10) Active pipeline aging (days in stage):

SELECT id,
       stage,
       EXTRACT(EPOCH FROM (CURRENT_DATE - COALESCE(updated_at, created_at))) / 86400.0 AS days_in_stage
FROM opportunities
WHERE stage NOT IN ('Closed Won', 'Closed Lost')
ORDER BY days_in_stage DESC
LIMIT 50;

SQL answers are only useful if they trigger action. Schedule a weekly query that finds stuck opportunities over 20 days in-stage and send a Slack message to owners. Or flag new high-LTV cohorts and push a list to your ad platform for a lookalike audience.

In practice, this looks like: a saved query, a job that runs it daily, and a downstream action (update CRM field, send email, trigger webhook). Tools like Make or n8n can watch your warehouse, run a query, and move clean lists into your systems without manual CSV uploads.

If you want to connect SQL outputs directly into ops (e.g., automatic list syncs, Slack nudges, or budget reallocation rules), that’s classic automation territory. A small, well-scoped engagement on marketing automation can turn your favorite queries into real workflows in under a month.

Ad platforms grade their own homework. SQL lets you reconcile platform-reported conversions with CRM status and billing truth. You don’t need a huge model - just a few joins.

Start monthly: line up ad spend by channel against qualified leads, opportunities, and closed revenue. Validate that tracking parameters (utm_source, campaign_id) are landing in your lead records. Fix the 10% of records that break your attribution logic; that 10% often distorts 50% of your decisions.

When you’re spending real money, do this cadence: daily anomaly checks (spend spikes, CPA outliers), weekly mix review (channel shift), and monthly cohort review (real revenue by first touch). If your team wants help hardening this loop, see how we run performance marketing with warehouse-backed guardrails.

SQL loses power if it lives with one person. Socialize it: add a #sql-snippets Slack channel; do a 30-minute Friday “query show and tell”; keep a shared folder of approved views and example queries; and pair a marketer and analyst monthly to retire a dashboard you no longer need.

Define data vocabulary. “Lead” should mean the same thing in CRM, ads, and your warehouse. Add a short README next to your queries with definitions (lead, MQL, SQL, opportunity, pipeline) and the exact conditions (e.g., Qualified = lifecycle_stage IN ('MQL','SQL')).

Finally, close the loop. Every query should inform a decision: ship budget, change a page, rewrite an ad, coach a rep. If a query doesn’t change behavior, park it.

DIY SQL gets you immediate clarity. But if you’re stuck reconciling five versions of the truth, or your CRM fields don’t reflect your sales process, it’s time to tighten the foundation. Two to four weeks of focused schema cleanup, field governance, and a handful of business-ready views will save you quarters of noise.

You don’t have to hire a full-time data team. A senior operator can standardize your CRM objects, normalize channel naming, and wire the warehouse to your daily workflows. That’s cheaper and faster than pushing this into a generic agency backlog or waiting for headcount.

If you’re already spending across Google, Meta, and LinkedIn, connect your ad extracts, CRM, and billing first. Then answer one question end-to-end: which campaigns created profitable customers last month? Use that win to set your operating cadence and expand coverage.

SQL isn’t a new job - it’s a sharper pencil. With SELECT, WHERE, GROUP BY, and a couple of JOINs, you can answer high-signal questions about channels, pipeline, and revenue in minutes. Start small: one query that influences a decision this week. Save it, share it, and build from there.

If you want a clean path to “answers on demand,” we can scope a 90-day plan to align CRM, build business-ready views, and wire automation around the queries you’ll use daily. Book a free Growth Audit to see what that could look like against your stack and targets.

Written by Anton Chisliuk - Leads, sales, content, and systems - one senior operator.

- What database should I practice SQL on if we don’t have a warehouse?

Spin up a lightweight, free option like SQLite or Postgres locally, or use your BI tool’s sample data. The point is fluency with syntax and patterns. When ready, connect to a read-only replica of your real data to answer live questions safely.

- How do I avoid slow queries that time out?

Filter early with WHERE, test with LIMIT, and only SELECT the columns you need. Add appropriate indexes in production (with engineering). For analytics warehouses, partition by date and use DATE_TRUNC filters to keep scans tight.

- Should I learn window functions now?

They’re powerful (running totals, rankings), but not required on day one. Master SELECT, WHERE, GROUP BY, HAVING, and JOIN first. Add window functions when you need cohort calculations or stage aging without complex subqueries.

- How do I keep definitions consistent across teams?

Publish a data glossary with canonical definitions (e.g., what makes a lead “Qualified”). Encapsulate logic in views (qualified_leads) so every query uses the same rule. Review the glossary quarterly when your process changes.

- Can I do this directly in HubSpot or Salesforce reports?

Yes for simple lists and rollups. For multi-source questions (ads + CRM + billing), use your warehouse. Keep operational reports in the CRM; move analytical questions to SQL so you can document and repeat them.

- How often should I run these queries?

Daily for anomaly checks, weekly for channel and pipeline mix, monthly for cohort and profitability. The cadence should match your decision rhythm - no more, no less.

- What’s the quickest win to prove value?

Run one attribution sanity check: join ad spend by channel with qualified leads and closed revenue for last month. If decisions change after that view, you’ve justified the habit.