Joins

Combine data from multiple sources with SQL joins.

Cross-Source Joins

The power of rawquery: join tables from different data sources in one query.

sql

-- Join Stripe customers with HubSpot contacts
SELECT
  s.email,
  s.name AS stripe_name,
  h.firstname || ' ' || h.lastname AS hubspot_name,
  h.lifecyclestage
FROM my_stripe.customers s
JOIN crm.contacts h ON s.email = h.email;

-- Join Stripe customers with HubSpot contacts
SELECT
  s.email,
  s.name AS stripe_name,
  h.firstname || ' ' || h.lastname AS hubspot_name,
  h.lifecyclestage
FROM my_stripe.customers s
JOIN crm.contacts h ON s.email = h.email;

Join Types

INNER JOIN

Returns only rows that match in both tables.

sql

SELECT c.email, ch.amount
FROM my_stripe.customers c
INNER JOIN my_stripe.charges ch ON ch.customer = c.id
WHERE ch.status = 'succeeded';

SELECT c.email, ch.amount
FROM my_stripe.customers c
INNER JOIN my_stripe.charges ch ON ch.customer = c.id
WHERE ch.status = 'succeeded';

LEFT JOIN

Returns all rows from the left table, with matching rows from the right (or NULL).

sql

-- All customers, with their charges if any
SELECT
  c.email,
  COUNT(ch.id) AS charge_count,
  COALESCE(SUM(ch.amount), 0) AS total
FROM my_stripe.customers c
LEFT JOIN my_stripe.charges ch ON ch.customer = c.id
GROUP BY c.email;

-- All customers, with their charges if any
SELECT
  c.email,
  COUNT(ch.id) AS charge_count,
  COALESCE(SUM(ch.amount), 0) AS total
FROM my_stripe.customers c
LEFT JOIN my_stripe.charges ch ON ch.customer = c.id
GROUP BY c.email;

FULL OUTER JOIN

Returns all rows from both tables, matching where possible.

sql

-- All emails from both Stripe and HubSpot
SELECT
  COALESCE(s.email, h.email) AS email,
  s.id IS NOT NULL AS in_stripe,
  h.id IS NOT NULL AS in_hubspot
FROM my_stripe.customers s
FULL OUTER JOIN crm.contacts h ON s.email = h.email;

-- All emails from both Stripe and HubSpot
SELECT
  COALESCE(s.email, h.email) AS email,
  s.id IS NOT NULL AS in_stripe,
  h.id IS NOT NULL AS in_hubspot
FROM my_stripe.customers s
FULL OUTER JOIN crm.contacts h ON s.email = h.email;

Common Patterns

Revenue by CRM Lifecycle Stage

sql

SELECT
  h.lifecyclestage,
  COUNT(DISTINCT s.id) AS customer_count,
  SUM(ch.amount) / 100.0 AS revenue
FROM crm.contacts h
JOIN my_stripe.customers s ON h.email = s.email
JOIN my_stripe.charges ch ON ch.customer = s.id
WHERE ch.status = 'succeeded'
GROUP BY h.lifecyclestage
ORDER BY revenue DESC;

SELECT
  h.lifecyclestage,
  COUNT(DISTINCT s.id) AS customer_count,
  SUM(ch.amount) / 100.0 AS revenue
FROM crm.contacts h
JOIN my_stripe.customers s ON h.email = s.email
JOIN my_stripe.charges ch ON ch.customer = s.id
WHERE ch.status = 'succeeded'
GROUP BY h.lifecyclestage
ORDER BY revenue DESC;

Customers Missing from CRM

sql

-- Paying Stripe customers not in HubSpot
SELECT DISTINCT s.email, s.name
FROM my_stripe.customers s
JOIN my_stripe.charges ch ON ch.customer = s.id
LEFT JOIN crm.contacts h ON s.email = h.email
WHERE ch.status = 'succeeded'
  AND h.id IS NULL;

-- Paying Stripe customers not in HubSpot
SELECT DISTINCT s.email, s.name
FROM my_stripe.customers s
JOIN my_stripe.charges ch ON ch.customer = s.id
LEFT JOIN crm.contacts h ON s.email = h.email
WHERE ch.status = 'succeeded'
  AND h.id IS NULL;

Self-Join for Comparison

sql

-- Compare this month vs last month
WITH monthly AS (
  SELECT
    DATE_TRUNC('month', created) AS month,
    SUM(amount) AS revenue
  FROM my_stripe.charges
  WHERE status = 'succeeded'
  GROUP BY 1
)
SELECT
  curr.month,
  curr.revenue AS current_revenue,
  prev.revenue AS previous_revenue,
  curr.revenue - prev.revenue AS change
FROM monthly curr
LEFT JOIN monthly prev
  ON prev.month = curr.month - INTERVAL '1 month'
ORDER BY curr.month DESC;

-- Compare this month vs last month
WITH monthly AS (
  SELECT
    DATE_TRUNC('month', created) AS month,
    SUM(amount) AS revenue
  FROM my_stripe.charges
  WHERE status = 'succeeded'
  GROUP BY 1
)
SELECT
  curr.month,
  curr.revenue AS current_revenue,
  prev.revenue AS previous_revenue,
  curr.revenue - prev.revenue AS change
FROM monthly curr
LEFT JOIN monthly prev
  ON prev.month = curr.month - INTERVAL '1 month'
ORDER BY curr.month DESC;