SQL for Automation Operators: The 10 Queries You Actually Need

Why automation operators need SQL

Most automation work hides SQL behind a visual builder. Make.com, GoHighLevel, HubSpot — all let you do CRUD operations without writing a query.

But eventually, you'll need direct SQL:

• Debugging why a contact isn't appearing in a list
• Finding duplicates that visual tools can't detect
• Running a one-time cleanup you can't express in a workflow
• Building custom reports the tool doesn't offer
• Working with Supabase (common in modern automation stacks)

Learning these 10 queries covers the vast majority of real-world needs.

---

Setup: where to run these

If you're using Supabase:

• Supabase dashboard → SQL Editor
• Runs against your production database
• Full SQL support (PostgreSQL dialect)

If you're using Postgres/MySQL elsewhere:

• Same concepts apply, syntax is ~95% identical
• Tools: DBeaver (free), TablePlus, pgAdmin

We'll use a simple schema: contacts table with columns: id, email, name, phone, source, created_at, updated_at.

---

Query 1: SELECT with WHERE (filter)

Use case: Find all contacts from a specific source.

SELECT * FROM contacts 
WHERE source = 'facebook-ads';

Common additions:

-- Multiple conditions
SELECT * FROM contacts 
WHERE source = 'facebook-ads' 
AND created_at > '2026-01-01';

-- Pattern matching
SELECT * FROM contacts 
WHERE email LIKE '%@gmail.com';

-- In a list
SELECT * FROM contacts 
WHERE source IN ('facebook-ads', 'google-ads', 'linkedin');

Gotcha: = for exact match. LIKE with % for wildcards. IN (...) for list matching.

---

Query 2: COUNT with GROUP BY

Use case: How many contacts from each source?

SELECT source, COUNT(*) as total 
FROM contacts 
GROUP BY source 
ORDER BY total DESC;

Extended:

-- Count by month
SELECT 
  DATE_TRUNC('month', created_at) AS month,
  COUNT(*) AS total
FROM contacts
GROUP BY month
ORDER BY month;

---

Query 3: JOIN two tables

Use case: Get contacts with their associated deals.

SELECT 
  c.email,
  c.name,
  d.amount,
  d.stage
FROM contacts c
JOIN deals d ON d.contact_id = c.id
WHERE d.stage = 'closed-won';

Types of joins:

• JOIN (aka INNER JOIN): only records that exist in both tables
• LEFT JOIN: all from left table, matching from right (NULL if no match)
• RIGHT JOIN: reverse of left
• FULL JOIN: all records from both

Most common in business data: LEFT JOIN when you want "all contacts, their deals if any exist."

---

Query 4: Find duplicates

Use case: Find contacts with the same email (indicates duplicates).

SELECT 
  LOWER(email) AS normalized_email,
  COUNT(*) AS duplicate_count,
  STRING_AGG(id::text, ', ') AS ids
FROM contacts
GROUP BY normalized_email
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;

What this does:

• Lowercases emails to catch case-variant dupes
• Groups by normalized email
• HAVING COUNT(*) > 1 keeps only groups with duplicates
• STRING_AGG lists the IDs of all duplicates so you can decide which to keep

---

Query 5: Date filtering

Use case: Contacts created in the last 30 days.

SELECT * FROM contacts 
WHERE created_at >= NOW() - INTERVAL '30 days';

Variants:

-- Specific date range
SELECT * FROM contacts 
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';

-- Only in April 2026
SELECT * FROM contacts 
WHERE EXTRACT(MONTH FROM created_at) = 4 
AND EXTRACT(YEAR FROM created_at) = 2026;

-- Today
SELECT * FROM contacts 
WHERE created_at >= CURRENT_DATE;

---

Query 6: UPDATE (carefully)

Use case: Lowercase all emails.

UPDATE contacts 
SET email = LOWER(email) 
WHERE email != LOWER(email);

Rules for UPDATE:

• Always include WHERE. Without WHERE, it updates every row.
• Run SELECT first with the same WHERE to see what will be affected.
• Use a transaction for important changes:

BEGIN;
UPDATE contacts SET email = LOWER(email) WHERE email != LOWER(email);
-- Verify with:
SELECT COUNT(*) FROM contacts WHERE email != LOWER(email);
-- If correct:
COMMIT;
-- If wrong:
-- ROLLBACK;

---

Query 7: DELETE (very carefully)

Use case: Remove obvious spam leads.

-- First, SEE what will be deleted
SELECT * FROM contacts 
WHERE email LIKE '%test%@%' 
OR name = 'asdf';

-- Then, delete
DELETE FROM contacts 
WHERE email LIKE '%test%@%' 
OR name = 'asdf';

Rules for DELETE:

• Always run SELECT first with same WHERE clause.
• Back up the data before large deletes (export to CSV).
• Use transactions for safety:

BEGIN;
DELETE FROM contacts WHERE source = 'test';
-- Check affected count
-- If correct:
COMMIT;

---

Query 8: UPSERT (INSERT or UPDATE if exists)

Use case: Add a contact if new, update if exists (prevents duplicates).

Postgres syntax (Supabase):

INSERT INTO contacts (email, name, phone, source) 
VALUES ('user@example.com', 'Jane', '+15551234567', 'google-ads')
ON CONFLICT (email) 
DO UPDATE SET 
  name = EXCLUDED.name,
  phone = EXCLUDED.phone,
  updated_at = NOW();

What this does:

• Insert a new row
• If there's already a row with the same email (unique constraint), update the existing row instead
• EXCLUDED refers to the values you tried to insert

This requires a UNIQUE constraint on email:

ALTER TABLE contacts ADD CONSTRAINT unique_email UNIQUE (email);

---

Query 9: Aggregation with conditions (CASE)

Use case: Count contacts by source, with sub-counts for qualified vs. unqualified.

SELECT 
  source,
  COUNT(*) AS total,
  SUM(CASE WHEN tags LIKE '%qualified%' THEN 1 ELSE 0 END) AS qualified,
  SUM(CASE WHEN tags NOT LIKE '%qualified%' THEN 1 ELSE 0 END) AS unqualified
FROM contacts
GROUP BY source;

When to use: Building reports with multiple metrics per group.

---

Query 10: Finding records without matches (LEFT JOIN + NULL)

Use case: Contacts that have no deals yet.

SELECT c.* 
FROM contacts c
LEFT JOIN deals d ON d.contact_id = c.id
WHERE d.id IS NULL;

What this does:

• LEFT JOIN keeps all contacts
• If no matching deal, deal fields are NULL
• WHERE d.id IS NULL keeps only contacts without deals

This pattern finds "contacts not yet in pipeline," "leads not yet called," "customers not yet onboarded."

---

Bonus: Creating indexes for speed

Queries on WHERE email = ... will be slow on large tables unless email is indexed.

CREATE INDEX idx_contacts_email ON contacts (LOWER(email));
CREATE INDEX idx_contacts_phone ON contacts (phone);
CREATE INDEX idx_contacts_source ON contacts (source);
CREATE INDEX idx_contacts_created_at ON contacts (created_at);

Rule of thumb: index any column you frequently filter on (WHERE) or join on.

---

Common mistakes

1. Running destructive queries without WHERE. DELETE FROM contacts; deletes every row. Always include WHERE.

2. Not backing up before cleanup. Export to CSV (or Supabase backup) before major UPDATE/DELETE.

3. Missing indexes. Queries on a 100k-row table without indexes take 30+ seconds. Indexes drop them to milliseconds.

4. Trusting case sensitivity. WHERE email = 'USER@EXAMPLE.COM' won't match user@example.com in case-sensitive comparisons. Use LOWER() on both sides or normalize at insert.

5. Forgetting NULL behavior. NULL = NULL is NULL (not TRUE). Use IS NULL and IS NOT NULL. Filters like WHERE source != 'x' skip rows where source is NULL.

---

Safety tips for production databases

1. Always start with SELECT. Before UPDATE or DELETE, run SELECT with the same WHERE to preview.

2. Use transactions for important changes:

BEGIN;
-- Make changes
-- Verify
COMMIT; -- or ROLLBACK if something looks wrong

3. Limit large operations:

UPDATE contacts SET status = 'archived' 
WHERE created_at < '2023-01-01' 
LIMIT 100;

Then run repeatedly. Faster than one huge update and easier to stop if needed.

4. Keep a query log. If you run ad-hoc queries against production, save them somewhere. You'll want them again.

---

Where these queries actually come up

• Debugging: "Why isn't this contact in the campaign?" → SELECT with conditions
• Cleanup: "We have duplicates from the import" → find dupes, delete dupes
• Reports: "How many leads came from each source last month?" → GROUP BY + date filter
• Migrations: "Moving from HubSpot to GHL" → UPSERT in batch
• Audits: "Contacts without any activity in 90 days" → LEFT JOIN + NULL

---

Sources

SQL standard references from PostgreSQL documentation (postgresql.org/docs) and MySQL documentation. All syntax is standard PostgreSQL/SQL compliant. Examples are simplified but reflect patterns used in production Supabase deployments.

Need help writing specific queries for your automation stack's database? Let's talk — I do a lot of SQL-level CRM cleanup in client engagements.