Syntax order

Write the clauses in this order: choose the columns, name the table, filter the rows, group them, filter the groups, then sort.

SELECT column_name1, column_name2
FROM table_name
WHERE condition
GROUP BY column_name1
HAVING condition
ORDER BY column_name1;

CTE

Names the active users and the large orders first, then joins them so each email sits next to that user’s total.

WITH clear_target_customers AS (
    -- Step 1: Get active users
    SELECT user_id, email
    FROM users
    WHERE status = 'active'
),
high_value_orders AS (
    -- Step 2: Get large purchases
    SELECT user_id, SUM(amount) as total_spent
    FROM orders
    GROUP BY user_id
    HAVING SUM(amount) > 1000
)
-- Step 3: Join the two results
SELECT c.email, o.total_spent
FROM clear_target_customers c
JOIN high_value_orders o ON c.user_id = o.user_id;

Comparison Conditions (Exact Matches & Boundaries)

• = (Equals): Matches exact values.

  WHERE status = 'active'

• != or <> (Not Equal To): Excludes exact values.

  WHERE status <> 'closed'

• > (Greater Than): Filters for values strictly above a cutoff.

  WHERE amount > 1000

• < (Less Than): Filters for values strictly below a cutoff.

  WHERE amount < 50

• >= (Greater Than or Equal To): Filters for inclusive lower limits.

  WHERE amount >= 1000

• <= (Less Than or Equal To): Filters for inclusive upper limits.

  WHERE amount <= 50

In the pipeline

These are the staging models from the Data pipeline tab. Names the Stripe total, then selects, joins, filters, groups, and sorts Salesforce accounts.

WITH billed AS (
    SELECT CAST(customer_id AS text) AS customer_id,
           SUM(amount_usd) AS billed
    FROM stg_stripe__charges
    WHERE status = 'succeeded'
    GROUP BY CAST(customer_id AS text)
)
SELECT a.lead_source, SUM(b.billed) AS billed
FROM stg_salesforce__accounts a
JOIN billed b ON b.customer_id = CAST(a.customer_id AS text)
WHERE a.lead_source IS NOT NULL
GROUP BY a.lead_source
HAVING SUM(b.billed) > 0
ORDER BY billed DESC;

Common Table Expressions

A Common Table Expression (CTE) is a named result set defined with WITH. A SELECT, INSERT, UPDATE, or DELETE in that statement can reference it. It lasts for that statement.

Standard / Single CTEs

One WITH query, referenced by the main statement.

Example (Find departments with high average salaries):

WITH DeptAvgSalary AS (
    SELECT department, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department
)
SELECT department, avg_sal
FROM DeptAvgSalary
WHERE avg_sal > 80000;

Multiple Independent CTEs

You can define more than one CTE in a single query by separating them with a comma. This allows you to prepare different subsets of data before combining them in your final statement.

Example (Combine top-performing employees and top-performing departments):

WITH HighEarners AS (
    SELECT employee_id, name, salary
    FROM employees
    WHERE salary > 120000
),
TopDepts AS (
    SELECT department
    FROM sales_goals
    WHERE status = 'Exceeded'
)
SELECT e.name, e.salary
FROM HighEarners e
JOIN employees emp ON e.employee_id = emp.employee_id
WHERE emp.department IN (SELECT department FROM TopDepts);

Multiple Dependent (Chained) CTEs

Multiple CTEs can also reference one another sequentially. This lets you build a linear pipeline where each step refines or expands on the virtual table created just before it.

Example (Calculate total department spend and then find the overall average of those totals):

WITH DeptTotals AS (
    SELECT department, SUM(salary) AS total_spend
    FROM employees
    GROUP BY department
),
AverageDeptSpend AS (
    SELECT AVG(total_spend) AS global_dept_avg
    FROM DeptTotals
)
SELECT department, total_spend
FROM DeptTotals
WHERE total_spend > (SELECT global_dept_avg FROM AverageDeptSpend);

Recursive CTEs

A CTE that references itself. An anchor member and a recursive member are combined with UNION ALL.

Example (Generate an organizational hierarchy level from top-level managers down to subordinates):

WITH RECURSIVE OrgChart AS (
    -- Anchor Member: Find the CEO / Top Manager
    SELECT employee_id, name, manager_id, 1 AS org_level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive Member: Join employees back to the chart to find subordinates
    SELECT e.employee_id, e.name, e.manager_id, o.org_level + 1
    FROM employees e
    JOIN OrgChart o ON e.manager_id = o.employee_id
)
SELECT * FROM OrgChart ORDER BY org_level, name;

Data Modification CTEs (Writable CTEs)

Supported by databases like PostgreSQL, these CTEs perform an INSERT, UPDATE, or DELETE combined with a RETURNING clause. This allows you to modify rows in one table and immediately use those exact modified records in the main query logic.

Example (Move deactivated users to an archive table in a single query step):

WITH MovedUsers AS (
    DELETE FROM users
    WHERE last_login < '2024-01-01'
    RETURNING user_id, email, date_joined
)
INSERT INTO archived_users (user_id, email, archived_at)
SELECT user_id, email, NOW()
FROM MovedUsers;

Multiple datasets

These are the staging models from the Data pipeline tab. Names Salesforce, Stripe, and users, then keeps ids present in all three.

WITH salesforce AS (
    SELECT CAST(customer_id AS text) AS customer_id
    FROM stg_salesforce__accounts
),
stripe AS (
    SELECT CAST(customer_id AS text) AS customer_id
    FROM stg_stripe__charges
    WHERE status = 'succeeded'
),
app_users AS (
    SELECT CAST(id AS text) AS customer_id
    FROM users
)
SELECT s.customer_id
FROM salesforce s
JOIN stripe c ON s.customer_id = c.customer_id
JOIN app_users u ON s.customer_id = u.customer_id;

Subquery Conditions (Cross-Table Checks)

• EXISTS (...): True if a subquery returns at least one row.

  WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id)

• NOT EXISTS (...): True if a subquery returns zero rows.

  WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id)

• ANY (...) or SOME (...): True if a comparison matches at least one item in a list.

  WHERE amount > ANY (SELECT amount FROM refunds)

• ALL (...): True only if a comparison matches every single item in a list.

  WHERE amount > ALL (SELECT amount FROM refunds)

Multiple datasets

These are the staging models from the Data pipeline tab. Keeps a user who has a succeeded charge and a marketing row.

SELECT u.id
FROM users u
WHERE EXISTS (
    SELECT 1 FROM stg_stripe__charges c
    WHERE CAST(c.customer_id AS text) = CAST(u.id AS text)
      AND c.status = 'succeeded'
)
  AND EXISTS (
    SELECT 1 FROM marketing_segments m
    WHERE LOWER(TRIM(m.email)) = LOWER(TRIM(u.email))
);

Set Operators

• UNION / UNION ALL: Combines result sets of queries (removing or keeping duplicates)

  SELECT city FROM a UNION SELECT city FROM b

• INTERSECT: Returns common rows present in both result sets

  SELECT city FROM a INTERSECT SELECT city FROM b

• EXCEPT (or MINUS in Oracle): Returns rows from the first query that are not in the second. MySQL uses EXCEPT.

  SELECT city FROM a EXCEPT SELECT city FROM b

Multiple datasets

These are the staging models from the Data pipeline tab. Stacks customer ids from Salesforce, Stripe, and users. Both sides of each UNION ALL use the same column count, and the ids are cast to text so they line up by position.

SELECT CAST(customer_id AS text) AS customer_id FROM stg_salesforce__accounts
UNION ALL
SELECT CAST(customer_id AS text) FROM stg_stripe__charges
UNION ALL
SELECT CAST(id AS text) FROM users;

Standard Joins

• INNER JOIN: Returns records that have matching values in both tables.

  SELECT Employees.Name, Departments.DeptName
  FROM Employees
  INNER JOIN Departments ON Employees.DeptID = Departments.DeptID;

• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table. Records in the left table with no match in the right table get NULL values.

  SELECT Employees.Name, Departments.DeptName
  FROM Employees
  LEFT JOIN Departments ON Employees.DeptID = Departments.DeptID;

• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table. Records in the right table with no match in the left table get NULL values.

  SELECT Employees.Name, Departments.DeptName
  FROM Employees
  RIGHT JOIN Departments ON Employees.DeptID = Departments.DeptID;

• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table. Unmatched rows from either side are padded with NULL.

  SELECT Employees.Name, Departments.DeptName
  FROM Employees
  FULL OUTER JOIN Departments ON Employees.DeptID = Departments.DeptID;

Algorithmic & Specialized Joins

• CROSS JOIN: Returns the Cartesian product of the two tables, pairing every row from the first table with every row from the second table. It does not use an ON clause.

  SELECT Products.Name, Sizes.SizeName
  FROM Products
  CROSS JOIN Sizes;

• SELF JOIN: A regular join (inner or left) where a table is joined with itself. This is useful for querying hierarchical data. It requires using table aliases.

  SELECT E.Name AS Employee, M.Name AS Manager
  FROM Employees E
  LEFT JOIN Employees M ON E.ManagerID = M.EmployeeID;

• NATURAL JOIN: Automatically joins tables based on all columns that have the same name and data type in both tables. Note: Use with caution, as changing table schemas can break queries.

  SELECT *
  FROM Employees
  NATURAL JOIN Departments;

Beyond the structural ways to group tables, SQL also classifies joins based on the operators used in the filtering condition or special techniques designed to exclude matching data.

Conditional & Mathematical Classifications

• EQUI-JOIN: Any join that relies exclusively on the equality operator (=) in the ON or WHERE clause. Most of the standard inner and outer joins you write everyday are technically equi-joins.

  SELECT * FROM Employees
  INNER JOIN Departments ON Employees.DeptID = Departments.DeptID;

• NON-EQUI-JOIN (Theta Join): A join that uses any comparison operator other than equality (such as <, >, <=, >=, !=, or BETWEEN). This is commonly used for matching records against ranges, such as evaluating bonuses or grading tiers.

  SELECT E.Name, E.Salary, G.Grade
  FROM Employees E
  INNER JOIN SalaryGrades G ON E.Salary BETWEEN G.LowSalary AND G.HighSalary;

Anti & Semi Joins

While SQL databases often execute these behind the scenes for optimization, you can write them explicitly to filter data from one table based on the existence of records in another table.

• LEFT ANTI-JOIN: Returns rows from the left table only if there is no matching record in the right table. It effectively subtracts the intersecting data.

  -- Written using a standard Left Join + NULL check
  SELECT Employees.Name
  FROM Employees
  LEFT JOIN Departments ON Employees.DeptID = Departments.DeptID
  WHERE Departments.DeptID IS NULL;

• SEMI-JOIN: Returns rows from the left table as soon as a match is found in the right table, without actually appending the columns of the right table. This avoids the duplicate row multiplication that a normal regular join might cause.

  -- Typically written using an EXISTS or IN subquery
  SELECT * FROM Employees E
  WHERE EXISTS (
      SELECT 1 FROM Departments D WHERE E.DeptID = D.DeptID
  );

Structural Workarounds

• STRAIGHT_JOIN: A database-specific join modifier (heavily used in MySQL) that forces the query optimizer to join the tables in the exact order they are listed in the FROM clause. This is used for advanced query tuning when the database's internal planner selects an inefficient table evaluation order.

  SELECT *
  FROM HugeTable
  STRAIGHT_JOIN SmallTable ON HugeTable.ID = SmallTable.ID;

Multiple datasets

These are the staging models from the Data pipeline tab. Puts Salesforce, a succeeded Stripe charge, and the user on one row.

SELECT a.customer_id, a.lead_source, c.amount_usd, u.email
FROM stg_salesforce__accounts a
INNER JOIN stg_stripe__charges c
    ON CAST(c.customer_id AS text) = CAST(a.customer_id AS text)
   AND c.status = 'succeeded'
INNER JOIN users u
    ON LOWER(TRIM(u.email)) = LOWER(TRIM(a.email));

To combine columns from separate or nested sub-datasets side-by-side into a single result set, PostgreSQL provides several structured joining mechanisms.

Standard Table Joins (Combining by Keys)

• INNER JOIN: Merges columns from both datasets only where the specified join keys match exactly.

  SELECT users.username, profiles.avatar_url
  FROM users
  INNER JOIN profiles ON users.id = profiles.user_id;

• LEFT JOIN (or LEFT OUTER JOIN): Keeps all columns from the first dataset and appends matching columns from the second dataset. Missing matches fill with NULL.

  SELECT products.name, inventory.shelf_location
  FROM products
  LEFT JOIN inventory ON products.id = inventory.product_id;

• RIGHT JOIN (or RIGHT OUTER JOIN): Keeps all columns from the second dataset and appends matching columns from the first dataset. Missing matches fill with NULL.

  SELECT tasks.title, projects.name
  FROM tasks
  RIGHT JOIN projects ON tasks.project_id = projects.id;

• FULL JOIN (or FULL OUTER JOIN): Combines columns from both datasets. If a row matches, columns are combined; if it does not, unmatched columns are populated with NULL.

  SELECT clients.company, leads.source
  FROM clients
  FULL JOIN leads ON clients.email = leads.email;

• CROSS JOIN: Combines columns by creating a Cartesian product, pairing every single row of the first dataset with every single row of the second dataset.

  SELECT clothes.item_name, sizes.size_code
  FROM clothes
  CROSS JOIN sizes;

• NATURAL JOIN: Automatically combines columns based on any columns in the two datasets that share the exact same name. (Note: Generally discouraged in production due to hidden schema dependency risk).

  -- Automatically joins on identical column names (e.g., 'department_id')
  SELECT employees.name, departments.dept_name
  FROM employees
  NATURAL JOIN departments;

Combining Subqueries & Advanced Dataset Schemas

• Subquery in the FROM or JOIN Clause: Pulls columns from an isolated, inline subquery and treats it like a temporary table to combine with your main dataset.

  SELECT orders.id, monthly_stats.avg_monthly_spend
  FROM orders
  INNER JOIN (
      SELECT customer_id, AVG(amount) AS avg_monthly_spend
      FROM sales GROUP BY customer_id
  ) AS monthly_stats ON orders.customer_id = monthly_stats.customer_id;

• LATERAL JOIN: Operates like a column-building loop, allowing a right-hand subquery to reference column values provided by rows on the left.

  SELECT categories.name, top_products.product_name, top_products.price
  FROM categories
  CROSS JOIN LATERAL (
      SELECT name AS product_name, price FROM products
      WHERE products.category_id = categories.id
      ORDER BY price DESC LIMIT 1
  ) AS top_products;

• Common Table Expression (WITH CTE) Joins: Isolates separate data logic into clean sub-datasets, then combines their columns together using traditional joins.

  WITH high_tier_users AS (
      SELECT id, username FROM users WHERE tier = 'premium'
  ),
  total_payments AS (
      SELECT user_id, SUM(amount) AS total_spent FROM invoices GROUP BY user_id
  )
  SELECT high_tier_users.username, total_payments.total_spent
  FROM high_tier_users
  INNER JOIN total_payments ON high_tier_users.id = total_payments.user_id;

• Correlated Scalar Subquery: Injects a single specific data column directly into the SELECT list by looking up a matching record from a sub-dataset for every outer row.

  SELECT companies.name,
         (SELECT COUNT(*) FROM employees WHERE employees.company_id = companies.id) AS employee_count
  FROM companies;

Multiple datasets

These are the staging models from the Data pipeline tab. Joins each user to a charge total and to marketing tags.

SELECT u.email, b.billed, m.tags
FROM users u
INNER JOIN (
    SELECT customer_id, SUM(amount_usd) AS billed
    FROM stg_stripe__charges
    WHERE status = 'succeeded'
    GROUP BY customer_id
) b ON CAST(b.customer_id AS text) = CAST(u.id AS text)
INNER JOIN marketing_segments m
    ON LOWER(TRIM(m.email)) = LOWER(TRIM(u.email));

To combine rows from separate or nested sub-datasets vertically (stacking rows on top of each other), PostgreSQL provides several set operations, subquery structures, and functional utilities.

Set Operations (Stacking Row Results)

• UNION ALL: Appends the row results of two or more datasets together. It retains all duplicate rows and is highly performant because it does not require a sorting pass to eliminate duplicates.

  SELECT item_id, price, 'retail' AS source FROM retail_store
  UNION ALL
  SELECT item_id, price, 'warehouse' AS source FROM warehouse_stock;

• UNION: Appends the row results of two or more datasets together but automatically filters out duplicate rows to ensure every combined row is completely unique.

  SELECT customer_email FROM standard_newsletter
  UNION
  SELECT customer_email FROM premium_newsletter;

• INTERSECT: Extracts only the rows that exist in both datasets, filtering out any rows that appear in only one of the queries.

  SELECT attendee_email FROM conference_2025
  INTERSECT
  SELECT attendee_email FROM conference_2026;

• INTERSECT ALL: Extracts duplicate rows present in both datasets. If a row appears 3 times in the first dataset and 5 times in the second, it will appear exactly 3 times in the final output.

  SELECT product_id FROM physical_returns
  INTERSECT ALL
  SELECT product_id FROM digital_returns;

• EXCEPT: Evaluates two datasets and returns rows from the first dataset that do not exist in the second dataset.

  SELECT subscriber_id FROM active_list
  EXCEPT
  SELECT subscriber_id FROM unsubscribed_list;

• EXCEPT ALL: Evaluates two datasets and subtracts occurrences. If a row appears 4 times in the first dataset and 2 times in the second, the result will output that row exactly 2 times.

  SELECT voucher_code FROM generated_vouchers
  EXCEPT ALL
  SELECT voucher_code FROM claimed_vouchers;

Subqueries & Conditional Filtering (Row Inclusion)

• IN / NOT IN Subquery: Dynamically expands or limits rows in a main query by matching a column against an entire vertical list of rows returned by a sub-dataset.

  SELECT * FROM transactions
  WHERE store_id IN (SELECT id FROM stores WHERE region = 'North');

• EXISTS / NOT EXISTS Subquery: Checks an isolated sub-dataset on a row-by-row basis, including a row in the main dataset only if the subquery returns at least one valid row.

  SELECT * FROM accounts a
  WHERE EXISTS (SELECT 1 FROM premium_features p WHERE p.account_id = a.id);

• ANY / SOME / ALL Subquery Comparisons: Compares a scalar value against an entire row-set returned by a sub-query using standard operators (>, <, =, etc.).

  -- Finds rows with a price higher than every row in the clearance sub-dataset
  SELECT * FROM products
  WHERE price > ALL (SELECT price FROM clearance_items);

Arrays & Table Functions (Expanding Rows)

• ARRAY Subquery Expressions: Evaluates a subquery row-set and collapses those rows into a single array value to be manipulated or expanded later.

  SELECT ARRAY(SELECT tag_name FROM tags WHERE category = 'tech');

• UNNEST(): Takes an array or sub-dataset of nested array values and unzips them vertically into separate rows.

  SELECT UNNEST(ordered_item_ids) FROM order_history;

In SQL, combining datasets vertically (stacking or appending rows from one table or query onto another) is handled using set operators. For an append to work, every query must return the same number of columns, and the corresponding columns must have compatible data types. PostgreSQL matches those columns by position, not by name. UNION removes duplicate rows. It does not promise a sort.

Standard Row Appending (Set Operators)

• UNION: Appends the result sets of two or more queries into a single distinct list. It removes duplicate rows across the combined results.

  SELECT City, Country FROM Customers
  UNION
  SELECT City, Country FROM Suppliers;

• UNION ALL: Appends the result sets of two or more queries exactly as they are. It retains all duplicate rows. Because it does not scan for duplicates, it is faster than UNION.

  SELECT City, Country FROM DomesticBranches
  UNION ALL
  SELECT City, Country FROM InternationalBranches;

Appending via Table Creation & Direct Insertion

These commands physically append rows into an existing table, or create a new combined table.

• INSERT INTO ... SELECT: Evaluates a query and appends its rows to the bottom of an existing table.

  INSERT INTO ArchiveTable (EmployeeID, LogDate, Action)
  SELECT EmployeeID, ActionDate, Activity
  FROM DailyLogs
  WHERE ActionDate < '2026-01-01';

• SELECT INTO (or CREATE TABLE AS SELECT): Creates a new table and fills it with the query result. PostgreSQL accepts SELECT INTO for this, and recommends CREATE TABLE AS for new code. In PL/pgSQL, SELECT INTO means something else: it stores values in variables.

  -- SQL Server, and historical PostgreSQL table creation
  SELECT * INTO MasterProductList
  FROM ActiveProducts
  UNION ALL
  SELECT * FROM DiscontinuedProducts;

  CREATE TABLE MasterProductList AS
  SELECT * FROM ActiveProducts
  UNION ALL
  SELECT * FROM DiscontinuedProducts;

Advanced or Specialized Appends

• CORRESPONDING (SQL standard): A modifier used with UNION by some systems, such as DB2. It matches and appends columns by name rather than by position, and ignores columns that do not exist in both tables. PostgreSQL and Oracle do not implement CORRESPONDING. PostgreSQL matches columns by position.

  SELECT * FROM EastCoastSales
  UNION CORRESPONDING
  SELECT * FROM WestCoastSales;

When you append three or more tables or queries, chain the set operators or insert from more than one source. The same rule still applies: every query must share the column count and compatible data types.

Chained Set Operators

• Chained UNION ALL: Stacks multiple queries in order. It keeps every duplicate and is the faster way to combine several sources, because the engine does not sort them to drop duplicates.

  SELECT ProductID, Price FROM Store_North
  UNION ALL
  SELECT ProductID, Price FROM Store_South
  UNION ALL
  SELECT ProductID, Price FROM Store_East
  UNION ALL
  SELECT ProductID, Price FROM Store_West;

• Chained UNION: Combines multiple queries and removes duplicates across the whole result.

  SELECT Email FROM Newsletter_Subscribers
  UNION
  SELECT Email FROM Premium_Customers
  UNION
  SELECT Email FROM Event_Attendees;

Mass Physical Appends (Multi-Table Insertion)

• INSERT INTO ... SELECT with Chained Appends: Evaluates several queries with UNION ALL and inserts the combined rows into one target table.

  INSERT INTO Global_Inventory (ItemName, StockCount)
  SELECT ItemName, Qty FROM Warehouse_A
  UNION ALL
  SELECT ItemName, Qty FROM Warehouse_B
  UNION ALL
  SELECT ItemName, Qty FROM Warehouse_C;

• Multi-Table INSERT ALL (Oracle): One SELECT can append rows into several destination tables at once, based on conditions. This syntax is Oracle.

  INSERT ALL
    WHEN Region = 'US' THEN INTO US_Customers (ID, Name) VALUES (CustID, CustName)
    WHEN Region = 'EU' THEN INTO EU_Customers (ID, Name) VALUES (CustID, CustName)
    ELSE INTO Global_Customers (ID, Name) VALUES (CustID, CustName)
  SELECT CustID, CustName, Region FROM Temporary_Staging_Table;

Combining Appending with Common Table Expressions (CTEs)

• With Chained CTEs: Names each source at the top of the query, then appends those named sets at the bottom.

  WITH
    Q1 AS (SELECT UserID, 'Active' AS Status FROM Current_Users),
    Q2 AS (SELECT UserID, 'Suspended' AS Status FROM Flagged_Users),
    Q3 AS (SELECT UserID, 'Archived' AS Status FROM Historical_Users)
  SELECT * FROM Q1
  UNION ALL
  SELECT * FROM Q2
  UNION ALL
  SELECT * FROM Q3;

Multiple datasets

These are the staging models from the Data pipeline tab. Stacks Salesforce ids and Stripe customer ids. Both sides cast the id to text and include a source label, so the columns line up by position.

SELECT CAST(customer_id AS text) AS customer_id, 'salesforce' AS source
FROM stg_salesforce__accounts
UNION ALL
SELECT CAST(customer_id AS text), 'stripe'
FROM stg_stripe__charges;

To make this exhaustive, we have to look at the entire data pipeline. This means moving from raw database integration techniques, through production-grade dbt design patterns, and up to complex statistical financial models.

The comprehensive, end-to-end master manual for combining, transforming, and modeling separate or unrelated sub-datasets in PostgreSQL and dbt covers everything you need.

Phase 1: Database Federation & Cross-Dataset Aggregation

Before running models, separate datasets must be brought into the same query context, regardless of whether they exist on the same server, separate database engines, or as unstructured data formats.

• FOREIGN DATA WRAPPERS (FDW) (Cross-Database/Cross-Server Federation): Connects entirely separate PostgreSQL instances or external engines (like MySQL, MongoDB, or CSV files) to your warehouse. This allows you to query external production databases side-by-side with local data as if they were local tables.

  CREATE EXTENSION IF NOT EXISTS postgres_fdw;
  
  CREATE SERVER production_billing_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'billing-db.internal', dbname 'stripe_ledger', port '5432');
  
  CREATE USER MAPPING FOR current_user
    SERVER production_billing_server
    OPTIONS (user 'analytics_user', password 'secure_pass');
  
  CREATE FOREIGN TABLE remote_invoice_ledger (
      id UUID, customer_id UUID, amount NUMERIC, issued_at TIMESTAMP WITH TIME ZONE
  ) SERVER production_billing_server OPTIONS (schema_name 'public', table_name 'invoices');

• JSONB Document Construction & Path Manipulation: Combines highly irregular, unstructured sub-datasets with structured relational tables. This mechanism eliminates the need to run dozens of outer joins by grouping one-to-many relationships into optimized JSON binary structures.

  SELECT
      u.id AS user_id,
      u.email,
      JSONB_BUILD_OBJECT(
          'total_invoiced', COALESCE(SUM(i.amount), 0),
          'invoice_count', COUNT(i.id),
          'last_invoice_date', MAX(i.issued_at)
      ) || JSONB_BUILD_OBJECT(
          'marketing_tags', COALESCE(m.tags, '[]'::jsonb)
      ) AS combined_sub_datasets
  FROM users u
  LEFT JOIN remote_invoice_ledger i ON u.id = i.customer_id
  LEFT JOIN marketing_segments m ON u.email = m.email
  GROUP BY u.id, u.email, m.tags;

• Advanced Window Partitioning (PARTITION BY ... ROWS BETWEEN): Injects complex statistical rolling metrics directly into individual data rows. This technique allows you to compare a specific data point against a separate slice of the table without collapsing rows via a GROUP BY clause.

  SELECT
      company_id, fiscal_quarter, quarterly_revenue,
      -- Calculate the moving average of the last 4 quarters for this specific partition
      AVG(quarterly_revenue) OVER (
          PARTITION BY company_id
          ORDER BY fiscal_quarter
          ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
      ) AS rolling_annual_avg_revenue
  FROM quarterly_financials;

Phase 2: Production Data Engineering with dbt (Data Build Tool)

To keep large, separate datasets clean and maintainable, you can structure your transformations using dbt’s staging, intermediate, and marts modeling layers.

• Directed Acyclic Graph (DAG) Modularity with source and ref: Decouples physical table boundaries. It dynamically maps dependencies across multiple separate staging tables, allowing you to combine dirty CRM variables with strict accounting ledgers.

  -- models/intermediate/finance/int_combined_customer_value.sql
  WITH crm_data AS (
      SELECT customer_id, customer_acquisition_cost, lead_source
      FROM {{ ref('stg_salesforce__accounts') }}
  ),
  ledger_data AS (
      SELECT customer_id, SUM(amount_usd) AS total_historical_billing
      FROM {{ ref('stg_stripe__charges') }}
      WHERE status = 'succeeded'
      GROUP BY 1
  )
  SELECT
      l.customer_id, l.total_historical_billing, c.customer_acquisition_cost, c.lead_source,
      (l.total_historical_billing - c.customer_acquisition_cost) AS net_customer_margin
  FROM ledger_data l
  INNER JOIN crm_data c ON l.customer_id = c.customer_id;

• Idempotent SCD Type 2 Snapshots: Tracks slowly changing dimensions over time. Capturing state changes inside mutating datasets allows you to piece together historical records to see exactly what an account’s subscription tier looked like at any specific moment in history.

  -- snapshots/snap_user_subscriptions.sql
  {% snapshot snap_user_subscriptions %}
  {{
      config(
        target_database='analytics_warehouse',
        target_schema='snapshots',
        unique_key='subscription_id',
        strategy='check',
        check_cols=['status', 'plan_tier', 'seat_count']
      )
  }}
  SELECT subscription_id, user_id, status, plan_tier, seat_count, updated_at
  FROM {{ source('production_api', 'subscriptions') }}
  {% endsnapshot %}

• Dynamic SQL Generation using Jinja and dbt-Utils: Automates repetitive row-to-column pivots or schema unifications across completely separate client tables, avoiding the need to write massive, repetitive blocks of manually hardcoded code.

  -- Automatically aggregates and pivots distinct payment status categories into separate metrics columns
  SELECT
      customer_id,
      {{ dbt_utils.pivot(
          column='payment_status',
          values=['settled', 'disputed', 'refunded', 'failed'],
          agg='count',
          then_value='invoice_id',
          else_value='NULL'
      ) }}
  FROM {{ ref('stg_billing__invoices') }}
  GROUP BY customer_id;

Phase 3: Strategic Financial Modeling & Forecast Engines

Once the datasets are unified, you can leverage advanced relational math to calculate core metrics like unit economics, degradation vectors, and growth lines.

• Cohort Retention Matrix Calculations: Pairs raw customer creation logs with recurring invoice records. This gives you a clear view of contract value and logo degradation across distinct monthly user cohorts.

  WITH cohort_definition AS (
      SELECT id AS customer_id, DATE_TRUNC('month', signed_up_at) AS cohort_month
      FROM customers
  ),
  invoice_history AS (
      SELECT DISTINCT customer_id, DATE_TRUNC('month', service_period_start) AS activity_month, amount_usd
      FROM customer_invoices WHERE payment_status = 'paid'
  ),
  cohort_sizes AS (
      SELECT cohort_month, COUNT(DISTINCT customer_id) AS initial_cohort_size
      FROM cohort_definition GROUP BY 1
  )
  SELECT
      c.cohort_month,
      s.initial_cohort_size,
      -- Calculate the exact number of months elapsed between the cohort start and the activity
      EXTRACT(YEAR FROM AGE(i.activity_month, c.cohort_month)) * 12 + EXTRACT(MONTH FROM AGE(i.activity_month, c.cohort_month)) AS months_elapsed,
      COUNT(DISTINCT i.customer_id) AS active_logos,
      SUM(i.amount_usd) AS monthly_recurring_revenue
  FROM cohort_definition c
  JOIN invoice_history i ON c.customer_id = i.customer_id
  JOIN cohort_sizes s ON c.cohort_month = s.cohort_month
  GROUP BY 1, 2, 3 ORDER BY 1, 3;

• Recursive Forward-Looking Time-Series Modeling: Generates future calendar dimensions and models them alongside historical compound averages. This creates a flexible revenue forecasting tool built directly into your database.

  WITH RECURSIVE future_calendar(forecast_month) AS (
      -- Set the initial base date parameter
      SELECT '2026-11-01'::DATE
      UNION ALL
      -- Incrementally add 1 month until the target horizon boundary condition is reached
      SELECT (forecast_month + INTERVAL '1 month')::DATE
      FROM future_calendar WHERE forecast_month < '2028-12-01'
  ),
  historical_baseline AS (
      -- Calculate the Compound Monthly Growth Rate (CMGR) from historical data
      SELECT
          POWER(last_val / first_val, 1.0 / nullif(months_count - 1, 0)) - 1 AS cmgr,
          last_val AS current_mrr
      FROM (
          SELECT
              FIRST_VALUE(total_mrr) OVER (ORDER BY mrr_month) AS first_val,
              LAST_VALUE(total_mrr) OVER (
                  ORDER BY mrr_month
                  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
              ) AS last_val,
              COUNT(*) OVER () AS months_count
          FROM historical_monthly_mrr
          ORDER BY mrr_month
      ) x LIMIT 1
  )
  SELECT
      f.forecast_month,
      -- Apply the compounding growth formula dynamically using windowed row sequence sequences
      h.current_mrr * POWER(1 + h.cmgr, ROW_NUMBER() OVER (ORDER BY f.forecast_month)) AS projected_mrr
  FROM future_calendar f
  CROSS JOIN historical_baseline h;

Phase 4: Advanced Statistical Simulation (Monte Carlo Framework)

A Monte Carlo framework creates robust risk models by simulating tens of thousands of random variables based on your baseline business parameters (like variance in churn, closing rates, and customer acquisition costs).

[10,000 Generation Runs] ──> [Apply Box-Muller Transformation] ──> [Run Trial Calculations] ──> [Percentile Aggregations] (generate_series) (Uniform Random -> Gaussian Normal) (Simulated ARR/LTV) (Risk Scenarios)

• High-Volume Pseudo-Random Sample Generation (GENERATE_SERIES): Creates the thousands of independent iterations required to run an accurate statistical simulation, entirely independent of your production data rows.
• Box-Muller Gaussian Transformation (RANDOM()): Converts PostgreSQL’s standard uniform random numbers into normal distribution curves. This lets you map realistic variances (like a ±12% fluctuation in contract values) rather than just flat percentages.
• End-to-End Simulation Pipeline Execution: The final component joins your historical performance baselines with the simulated variables, providing clear visibility into best-case, worst-case, and median revenue outcomes.

  WITH simulation_base AS (
      -- Spawns exactly 20,000 independent trial iterations
      SELECT generate_series(1, 20000) AS trial_id
  ),
  stochastic_variables AS (
      SELECT
          trial_id,
          -- 1. Model New Customer Acquisition via Normal Distribution (Mean: 1,200 deals, StdDev: 150)
          (1200 + (SQRT(-2 * LN(RANDOM())) * COS(2 * PI() * RANDOM())) * 150)::INT AS simulated_new_logos,
  
          -- 2. Model Average Annual Contract Value (ACV) via Normal Distribution (Mean: $15,000, StdDev: $1,800)
          (15000 + (SQRT(-2 * LN(RANDOM())) * SIN(2 * PI() * RANDOM())) * 1800) AS simulated_acv,
  
          -- 3. Model Gross Revenue Churn via Uniform Distribution (Bounded strictly between 4% and 9%)
          (0.04 + (RANDOM() * 0.05)) AS simulated_churn_rate
      FROM simulation_base
  ),
  trial_evaluations AS (
      SELECT
          trial_id,
          -- Base formula: Existing ARR ($25M) adjusted for simulated churn, plus newly simulated pipeline revenue
          ((25000000 * (1 - simulated_churn_rate)) + (simulated_new_logos * simulated_acv)) AS simulated_ending_arr
      FROM stochastic_variables
  )
  -- Aggregate the statistical probabilities across all 20,000 runs
  SELECT
      MIN(simulated_ending_arr)::NUMERIC(15,2) AS absolute_worst_case_floor,
      PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY simulated_ending_arr)::NUMERIC(15,2) AS fifth_percentile_risk_boundary,
      PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY simulated_ending_arr)::NUMERIC(15,2) AS conservative_downside_target,
      PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY simulated_ending_arr)::NUMERIC(15,2) AS median_expected_outcome,
      PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY simulated_ending_arr)::NUMERIC(15,2) AS aggressive_upside_target,
      PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY simulated_ending_arr)::NUMERIC(15,2) AS ninety_fifth_percentile_upside,
      MAX(simulated_ending_arr)::NUMERIC(15,2) AS absolute_best_case_ceiling,
      AVG(simulated_ending_arr)::NUMERIC(15,2) AS expected_mean_value,
      STDDEV(simulated_ending_arr)::NUMERIC(15,2) AS simulation_standard_deviation
  FROM trial_evaluations;

Phase 5: Interactive Financial Exploration

To bridge database modeling and executive decisions, you can use interactive simulators to review how adjustments to your core variables affect long-term projections.

Combining Datasets Vertically (Appending Rows)

• UNION: Combines the result sets of two or more queries into a single distinct result set. It automatically removes duplicate rows.

  SELECT user_id, email FROM active_users
  UNION
  SELECT user_id, email FROM archived_users;

• UNION ALL: Combines result sets just like UNION, but retains all duplicate rows. This is much faster because Postgres doesn't have to sort the data to filter duplicates.

  SELECT product_id, price FROM retail_inventory
  UNION ALL
  SELECT product_id, price FROM warehouse_inventory;

• INTERSECT: Returns only the rows that are identical and present in both query outputs.

  SELECT customer_id FROM online_sales
  INTERSECT
  SELECT customer_id FROM in_store_sales;

• EXCEPT / EXCEPT ALL: Returns rows from the first query that do not exist in the second query.

  -- Customers who ordered online but never ordered in-store
  SELECT customer_id FROM online_sales
  EXCEPT
  SELECT customer_id FROM in_store_sales;

Combining Datasets Horizontally (Joining Columns)

• INNER JOIN: Returns rows when there is a match in both datasets based on the join condition.

  SELECT orders.id, customers.name
  FROM orders
  INNER JOIN customers ON orders.customer_id = customers.id;

• LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left dataset, and the matched rows from the right dataset. Missing matches on the right return NULL.

  SELECT customers.name, orders.id
  FROM customers
  LEFT JOIN orders ON customers.id = orders.customer_id;

• RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right dataset, and the matched rows from the left dataset. Missing matches on the left return NULL.

  SELECT orders.id, employees.name
  FROM orders
  RIGHT JOIN employees ON orders.sales_rep_id = employees.id;

• FULL JOIN (or FULL OUTER JOIN): Returns all rows when there is a match in either the left or right dataset. It fills gaps on either side with NULL.

  SELECT employees.name, projects.title
  FROM employees
  FULL JOIN projects ON employees.project_id = projects.id;

• CROSS JOIN: Creates a Cartesian product, matching every single row from the first dataset with every single row from the second dataset.

  SELECT sizes.size_name, colors.color_name
  FROM sizes
  CROSS JOIN colors;

Combining isolated Sub-datasets (Subqueries & Expressions)

• WITH (Common Table Expressions / CTEs): Breaks a complex query down into temporary named sub-datasets that you can reference like tables later in the same query.

  WITH regional_sales AS (
      SELECT region, SUM(amount) AS total_amount FROM sales GROUP BY region
  )
  SELECT region FROM regional_sales WHERE total_amount > 10000;

• Inline Subqueries (Derived Tables): Nesting a subquery directly inside the FROM or JOIN clause to act as a temporary, isolated sub-dataset.

  SELECT avg_data.avg_price
  FROM (SELECT AVG(price) AS avg_price FROM products) AS avg_data;

• Scalar Subqueries: Fetching a single data point from a separate dataset inside the SELECT projection list.

  SELECT name, (SELECT MAX(login_date) FROM logins WHERE logins.user_id = users.id) AS last_login
  FROM users;

• Lateral Joins (JOIN LATERAL): Acts like a SQL foreach loop. It allows a subquery in the FROM clause to reference columns from preceding datasets in the join list.

  SELECT departments.name, top_employees.name
  FROM departments
  CROSS JOIN LATERAL (
      SELECT name FROM employees
      WHERE employees.dept_id = departments.id
      ORDER BY salary DESC LIMIT 3
  ) AS top_employees;

Multiple datasets

These are the staging models from the Data pipeline tab. Stacks Salesforce and Stripe ids, then joins users to Salesforce and the charge total.

WITH ids AS (
    SELECT CAST(customer_id AS text) AS customer_id, 'salesforce' AS source
    FROM stg_salesforce__accounts
    UNION ALL
    SELECT CAST(customer_id AS text), 'stripe'
    FROM stg_stripe__charges
),
billed AS (
    SELECT CAST(customer_id AS text) AS customer_id, SUM(amount_usd) AS billed
    FROM stg_stripe__charges
    WHERE status = 'succeeded'
    GROUP BY CAST(customer_id AS text)
)
SELECT u.email, a.lead_source, b.billed, i.source
FROM users u
JOIN stg_salesforce__accounts a
    ON LOWER(TRIM(a.email)) = LOWER(TRIM(u.email))
JOIN billed b ON b.customer_id = CAST(a.customer_id AS text)
JOIN ids i ON i.customer_id = CAST(a.customer_id AS text);

Alternatives & Examples

• EXISTS: True when each subquery returns a row.

  SELECT employee_id FROM Employees e
  WHERE EXISTS (SELECT 1 FROM Tech_Club t WHERE t.employee_id = e.employee_id)
    AND EXISTS (SELECT 1 FROM Sports_Club s WHERE s.employee_id = e.employee_id);

• IN: Keeps rows whose value is returned by each subquery.

  SELECT employee_id FROM Employees
  WHERE employee_id IN (SELECT employee_id FROM Tech_Club)
    AND employee_id IN (SELECT employee_id FROM Sports_Club);

• INNER JOIN: Keeps rows with a matching key in each joined table.

  SELECT e.employee_id 
  FROM Employees e
  INNER JOIN Tech_Club t ON e.employee_id = t.employee_id
  INNER JOIN Sports_Club s ON e.employee_id = s.employee_id;

• GROUP BY with HAVING: Keeps groups whose aggregate meets the condition.

  -- Finds members who belong to exactly 2 specific clubs
  SELECT employee_id 
  FROM Club_Memberships
  WHERE club_name IN ('Tech', 'Sports')
  GROUP BY employee_id
  HAVING COUNT(DISTINCT club_name) = 2;

More examples

• Inner Joins (INNER JOIN): Returns each user id that is in table1, table2, and table3, once.

  SELECT DISTINCT t1.user_id 
  FROM table1 t1
  INNER JOIN table2 t2 ON t1.user_id = t2.user_id
  INNER JOIN table3 t3 ON t1.user_id = t3.user_id;

• Semi-Joins via EXISTS: Returns user ids from table1 that also exist in table2 and table3, without adding those tables’ columns.

  SELECT t1.user_id 
  FROM table1 t1
  WHERE EXISTS (SELECT 1 FROM table2 t2 WHERE t2.user_id = t1.user_id)
    AND EXISTS (SELECT 1 FROM table3 t3 WHERE t3.user_id = t1.user_id);

• Value Matching via IN: Keeps a user id from table1 only when that same id is returned by both table2 and table3.

  SELECT user_id 
  FROM table1 
  WHERE user_id IN (SELECT user_id FROM table2)
    AND user_id IN (SELECT user_id FROM table3);

• Conditional Aggregation (GROUP BY with HAVING): Stacks the three tables, then keeps user ids that appear three times, once from each table.

  SELECT user_id 
  FROM (
      SELECT user_id FROM table1
      UNION ALL SELECT user_id FROM table2
      UNION ALL SELECT user_id FROM table3
  ) combined_tables
  GROUP BY user_id
  HAVING COUNT(*) = 3;

• Common Table Expressions (CTEs) with Joins: Names each table, then keeps user ids that are present in all three named sets.

  WITH set_a AS (SELECT user_id FROM table1),
       set_b AS (SELECT user_id FROM table2),
       set_c AS (SELECT user_id FROM table3)
  SELECT set_a.user_id 
  FROM set_a
  JOIN set_b ON set_a.user_id = set_b.user_id
  JOIN set_c ON set_a.user_id = set_c.user_id;

• Array / JSON Intersection Functions: Keeps an item when its tag list shares electronics or sale with the list on the right.

  -- PostgreSQL example using the array overlap operator (&&)
  SELECT item_id 
  FROM items 
  WHERE tags_array && ARRAY['electronics', 'sale'];

Multiple datasets

These are the staging models from the Data pipeline tab. Keeps Salesforce ids that also appear in a sub-dataset of Stripe and users.

SELECT CAST(customer_id AS text) AS customer_id
FROM stg_salesforce__accounts
INTERSECT
SELECT customer_id FROM (
    SELECT CAST(customer_id AS text) AS customer_id FROM stg_stripe__charges
    UNION ALL
    SELECT CAST(id AS text) FROM users
) nested;

Aggregate Functions

These functions perform a calculation on a set of values and return a single value.

• COUNT(): Counts the number of rows or non-NULL values.

  SELECT COUNT(*) FROM orders

• SUM(): Calculates the total sum of a numeric column.

  SELECT SUM(amount) FROM orders

• AVG(): Calculates the average value of a numeric column.

  SELECT AVG(amount) FROM orders

• MIN(): Finds the minimum value in a column.

  SELECT MIN(amount) FROM orders

• MAX(): Finds the maximum value in a column.

  SELECT MAX(amount) FROM orders

In the pipeline

These are the staging models from the Data pipeline tab. Counts accounts and sums succeeded Stripe charges by Salesforce lead source. The filter sits on the aggregate, and the condition inside it is not itself an aggregate.

SELECT a.lead_source,
       COUNT(*) AS account_count,
       SUM(c.amount_usd) FILTER (WHERE c.status = 'succeeded') AS billed
FROM stg_salesforce__accounts a
LEFT JOIN stg_stripe__charges c
    ON CAST(c.customer_id AS text) = CAST(a.customer_id AS text)
GROUP BY a.lead_source;

Advanced & Logical Functions

These expressions and functions handle conditional logic and data conversion.

• CASE: Evaluates a list of conditions and returns a specific value (SQL’s version of an IF-THEN statement).

  CASE WHEN amount > 1000 THEN 'high' ELSE 'low' END

• COALESCE(): Returns the first non-NULL value in a list of arguments.

  SELECT COALESCE(phone, email, 'none')

• IFNULL() / ISNULL(): Replaces a NULL value with a specified fallback value. IFNULL() is MySQL. ISNULL() is SQL Server. PostgreSQL uses COALESCE.

  SELECT IFNULL(phone, 'none')

• CAST() / CONVERT(): Changes the data type of a value into another data type.

  SELECT CAST(amount AS INTEGER)

In the pipeline

These are the staging models from the Data pipeline tab. Fills a missing email from the other table, and labels a row when the billed Stripe amount is above the Salesforce acquisition cost.

SELECT COALESCE(a.email, u.email) AS email,
       CASE
           WHEN b.billed > a.customer_acquisition_cost THEN 'above cost'
           ELSE 'at or below cost'
       END AS cost_check
FROM stg_salesforce__accounts a
LEFT JOIN users u
    ON LOWER(TRIM(u.email)) = LOWER(TRIM(a.email))
LEFT JOIN (
    SELECT CAST(customer_id AS text) AS customer_id,
           SUM(amount_usd) AS billed
    FROM stg_stripe__charges
    WHERE status = 'succeeded'
    GROUP BY CAST(customer_id AS text)
) b ON b.customer_id = CAST(a.customer_id AS text);

Window Functions

These functions perform calculations across a set of table rows that are related to the current row.

• ROW_NUMBER(): Assigns a unique sequential integer to each row.

  ROW_NUMBER() OVER (ORDER BY amount DESC)

• RANK(): Assigns a rank to each row, leaving gaps if there are ties.

  RANK() OVER (ORDER BY amount DESC)

• DENSE_RANK(): Assigns a rank to each row without leaving gaps for ties.

  DENSE_RANK() OVER (ORDER BY amount DESC)

• LEAD(): Fetches data from a subsequent row in the same result set.

  LEAD(amount) OVER (ORDER BY order_date)

• LAG(): Fetches data from a previous row in the same result set.

  LAG(amount) OVER (ORDER BY order_date)

In the pipeline

These are the staging models from the Data pipeline tab. Ranks each Salesforce account inside its lead source by the succeeded Stripe total.

SELECT lead_source,
       customer_id,
       billed,
       ROW_NUMBER() OVER (PARTITION BY lead_source ORDER BY billed DESC) AS source_rank
FROM (
    SELECT a.lead_source,
           a.customer_id,
           COALESCE(SUM(c.amount_usd) FILTER (WHERE c.status = 'succeeded'), 0) AS billed
    FROM stg_salesforce__accounts a
    LEFT JOIN stg_stripe__charges c
        ON CAST(c.customer_id AS text) = CAST(a.customer_id AS text)
    GROUP BY a.lead_source, a.customer_id
) totals;

Window & Ranking Functions

• ROW_NUMBER(): Assigns a unique, sequential integer to each row within a partition starting at 1.

  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date)

• RANK(): Assigns a rank to each row with gaps in ranking sequence in case of ties.

  RANK() OVER (ORDER BY amount DESC)

• DENSE_RANK(): Assigns a rank to rows without skipping any sequence numbers after a tie.

  DENSE_RANK() OVER (ORDER BY amount DESC)

• LAG(column, offset, default): Accesses data from a previous row at a specified physical offset.

  LAG(amount, 1, 0) OVER (ORDER BY order_date)

• LEAD(column, offset, default): Accesses data from a subsequent row at a specified physical offset.

  LEAD(amount, 1, 0) OVER (ORDER BY order_date)

• FIRST_VALUE(column): Computes the value evaluated at the row that is the first row of the window frame.

  FIRST_VALUE(amount) OVER (ORDER BY order_date)

• LAST_VALUE(column): Computes the value at the last row of the window frame. The frame below covers the whole partition. Without it, PostgreSQL stops at the current row.

  LAST_VALUE(amount) OVER (
      ORDER BY order_date
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  )

• NTH_VALUE(column, n): Returns the value evaluation from the n-th row of the window frame.

  NTH_VALUE(amount, 2) OVER (ORDER BY order_date)

• NTILE(num_buckets): Divides partition rows evenly into a specified number of ranked buckets.

  NTILE(4) OVER (ORDER BY amount)

• CUME_DIST(): Calculates the cumulative distribution (relative position) of a value in a group of values.

  CUME_DIST() OVER (ORDER BY amount)

• PERCENT_RANK(): Returns the percentile rank of the current row relative to partition parameters [(rank - 1) / (total_rows - 1)].

  PERCENT_RANK() OVER (ORDER BY amount)

[1] [2] [3]

Conditional & Utility Clauses / Functions

• COALESCE(val1, val2, ...): Evaluates arguments sequentially and returns the first non-null value encountered.

  SELECT COALESCE(phone, email, 'none')

• NULLIF(val1, val2): Returns NULL if val1 equals val2; otherwise, returns val1.

  SELECT NULLIF(status, 'unknown')

• GREATEST(val1, val2, ...): Selects the largest value from a list of expressions.

  SELECT GREATEST(10, 25, 4)

• LEAST(val1, val2, ...): Selects the smallest value from a list of expressions.

  SELECT LEAST(10, 25, 4)

• FILTER (WHERE condition): Clause applied to aggregate functions to filter rows conditionally prior to aggregation.

  COUNT(*) FILTER (WHERE status = 'active')

• DISTINCT ON (expression): Clause keeping only the first row of each set of rows where given expressions evaluate equal.

  SELECT DISTINCT ON (user_id) user_id, order_date FROM orders ORDER BY user_id, order_date DESC

[1] [2] [3]

Advanced Aggregation Extensions

• STRING_AGG(expression, delimiter): Concatenates input values into a string, separated by a delimiter string.

  SELECT STRING_AGG(city, ', ')

• ROLLUP (grouping_sets): Generates hierarchical subtotals and a grand total across grouped elements.

  GROUP BY ROLLUP (region, city)

• CUBE (grouping_sets): Computes all possible aggregate combinations (cross-tabulation) across dimensions.

  GROUP BY CUBE (region, city)

• GROUPING SETS (grouping_sets): Allows precise, custom definition of multiple grouping configurations in one query block.

  GROUP BY GROUPING SETS ((region), (city))

[1]

JSON & JSONB Operations / Functions

• jsonb_extract_path(target, path...) / -> / ->>: Extracts JSON sub-objects or text values via key or index paths.

  SELECT data ->> 'name'

• jsonb_agg(expression): Aggregates values, including nulls, as a JSON array.

  SELECT jsonb_agg(city)

• jsonb_object_agg(key, value): Aggregates name-value pairs into a JSON object.

  SELECT jsonb_object_agg(user_id, city)

• jsonb_array_elements(jsonb): Expands a JSON array into a set of JSON values.

  SELECT jsonb_array_elements('[1, 2, 3]'::jsonb)

• json_to_record(json): Builds a standard SQL record structure from a JSON object string.

  SELECT * FROM json_to_record('{"name":"John"}') AS x(name text)

[1] [2]

String & Pattern Matching Functions

• REGEXP_MATCH(string, pattern): Returns captured substring matches using regular expressions.

  SELECT REGEXP_MATCH('John Doe', 'J\w+')

• REGEXP_REPLACE(string, pattern, replacement): Replaces substrings matching POSIX regular expression patterns.

  SELECT REGEXP_REPLACE('John Doe', 'Doe', 'Smith')

• SPLIT_PART(string, delimiter, field): Splits a string on a delimiter and picks the requested numeric field index.

  SELECT SPLIT_PART('a-b-c', '-', 2)

• TRANSLATE(string, from, to): Replaces each character in the string that matches a character in the from set with the corresponding character in the to set.

  SELECT TRANSLATE('123', '12', 'ab')

[1]

Date & Time Temporal Functions

• DATE_TRUNC(field, source): Truncates a timestamp or interval value down to precision boundary field.

  SELECT DATE_TRUNC('month', CURRENT_TIMESTAMP)

• AGE(timestamp, timestamp): Subtracts arguments, producing a symbolic years, months, and days human-readable structural interval.

  SELECT AGE(CURRENT_DATE, DATE '2000-01-01')

• EXTRACT(field FROM source): Retrieves sub-fields like year, epoch, or dow (day of week) from temporal structures.

  SELECT EXTRACT(YEAR FROM CURRENT_DATE)

[1]

In the pipeline

These are the staging models from the Data pipeline tab. One mart query truncates the charge month, sums succeeded Stripe charges, and ranks the month inside each Salesforce lead source.

SELECT lead_source,
       charge_month,
       billed,
       ROW_NUMBER() OVER (PARTITION BY lead_source ORDER BY billed DESC) AS source_rank
FROM (
    SELECT a.lead_source,
           DATE_TRUNC('month', c.created_at) AS charge_month,
           SUM(c.amount_usd) FILTER (WHERE c.status = 'succeeded') AS billed
    FROM stg_salesforce__accounts a
    JOIN stg_stripe__charges c
        ON CAST(c.customer_id AS text) = CAST(a.customer_id AS text)
    GROUP BY a.lead_source, DATE_TRUNC('month', c.created_at)
) mart;

Single-Line Comments (--)

• Standard Syntax: Start the comment with two consecutive hyphens (--).
• Requirement for Space: MySQL requires a whitespace character (space or tab) immediately after the second hyphen (-- comment). PostgreSQL and SQL Server accept --comment with no space.
• Inline Placement: You can place them at the very beginning of a line or at the end of a code statement on the same line.
• Termination: The comment automatically ends at the break of the line. Anything written on the next line will be executed as standard SQL.

The note after email is ignored. The query still returns user id and email for rows whose status is active.

SELECT user_id, email -- active accounts only
FROM users
WHERE status = 'active';

Multi-Line Comments (/* ... */)

• Standard Syntax: Open the comment block with a forward slash and an asterisk (/*) and close it with an asterisk and a forward slash (*/).
• Span Multiple Lines: Use this format to wrap large blocks of text, documentation, or multiple lines of code across several lines.
• Inline Use: You can use them in the middle of a single line of code to document specific elements.

  SELECT column /* comment here */ FROM table;

• Nesting: PostgreSQL block comments nest. MySQL block comments do not, and a nested /* there ends at the first */.

The block above the SELECT is ignored. The query still returns user id and email for rows whose status is active.

/* Step 1: pull active users
   Step 2: keep only those with a recent order */
SELECT user_id, email
FROM users
WHERE status = 'active';

Dialect-Specific Rules

• MySQL Executable Comments: MySQL supports a unique syntax (/*! SQL_CODE */) where the code inside the comment is executed only by MySQL, while other databases treat it as a standard comment.
• MySQL Hash Comment: MySQL allows the hash symbol (#) to initiate a single-line comment. This is non-standard and will fail in databases like PostgreSQL or SQL Server.

In the pipeline

These are the staging models from the Data pipeline tab. The same billed-by-source query, with a line comment on each source and a block comment naming the step.

/* Step: billed amount by lead source */
WITH billed AS (
    -- Stripe charges
    SELECT CAST(customer_id AS text) AS customer_id,
           SUM(amount_usd) AS billed
    FROM stg_stripe__charges
    WHERE status = 'succeeded'
    GROUP BY CAST(customer_id AS text)
)
SELECT a.lead_source, SUM(b.billed) AS billed
FROM stg_salesforce__accounts a -- Salesforce accounts
JOIN billed b ON b.customer_id = CAST(a.customer_id AS text)
WHERE a.lead_source IS NOT NULL
GROUP BY a.lead_source
HAVING SUM(b.billed) > 0
ORDER BY billed DESC;

Range & Set Conditions (Group Matching)

• BETWEEN ... AND ...: Filters values within an inclusive range.

  WHERE amount BETWEEN 10 AND 50

• NOT BETWEEN ... AND ...: Excludes values within a specific range.

  WHERE amount NOT BETWEEN 10 AND 50

• IN (...): Matches any value found inside a specified list.

  WHERE status IN ('active', 'trial')

• NOT IN (...): Rejects any value found inside a specified list.

  WHERE status NOT IN ('closed', 'banned')

In the pipeline

These are the staging models from the Data pipeline tab. BETWEEN, NOT BETWEEN, IN, and NOT IN are boolean columns. IN reads lead_source from the other Salesforce rows, beside the Stripe total.

SELECT a.customer_id,
       (b.billed BETWEEN 0 AND a.customer_acquisition_cost) AS billed_in_cost,
       (b.billed NOT BETWEEN 0 AND a.customer_acquisition_cost) AS billed_outside_cost,
       (a.lead_source IN (
           SELECT other.lead_source
           FROM stg_salesforce__accounts other
           WHERE other.customer_id <> a.customer_id
       )) AS source_seen_elsewhere,
       (a.lead_source NOT IN (
           SELECT other.lead_source
           FROM stg_salesforce__accounts other
           WHERE other.customer_id <> a.customer_id
       )) AS source_only_here
FROM stg_salesforce__accounts a
JOIN (
    SELECT CAST(customer_id AS text) AS customer_id, SUM(amount_usd) AS billed
    FROM stg_stripe__charges
    WHERE status = 'succeeded'
    GROUP BY CAST(customer_id AS text)
) b ON b.customer_id = CAST(a.customer_id AS text);

Pattern Matching Conditions (Text Scanning)

• LIKE: Scans text using case-sensitive wildcards (% and _).

  WHERE email LIKE '%@gmail.com'

• NOT LIKE: Excludes text matching a specific case-sensitive pattern.

  WHERE email NOT LIKE '%@gmail.com'

• ILIKE: Scans text ignoring capitalization (PostgreSQL/Snowflake).

  WHERE city ILIKE 'london'

• NOT ILIKE: Excludes text matching a pattern, ignoring capitalization.

  WHERE city NOT ILIKE 'london'

In the pipeline

These are the staging models from the Data pipeline tab. LIKE, NOT LIKE, ILIKE, and NOT ILIKE are boolean columns of the user email against the marketing tag. ILIKE is PostgreSQL.

SELECT u.email,
       (u.email LIKE '%' || m.tags || '%') AS tag_like,
       (u.email NOT LIKE '%' || m.tags || '%') AS tag_not_like,
       (u.email ILIKE '%' || m.tags || '%') AS tag_ilike,
       (u.email NOT ILIKE '%' || m.tags || '%') AS tag_not_ilike
FROM users u
JOIN marketing_segments m
    ON LOWER(TRIM(m.email)) = LOWER(TRIM(u.email));

Null Conditions (Missing Data)

• IS NULL: Identifies completely empty or missing values.

  WHERE email IS NULL

• IS NOT NULL: Filters out empty values to find complete records.

  WHERE email IS NOT NULL

In the pipeline

These are the staging models from the Data pipeline tab. Left-joins users to marketing. IS NULL and IS NOT NULL are boolean columns on the segment.

SELECT u.email,
       (m.email IS NULL) AS missing_segment,
       (m.email IS NOT NULL) AS has_segment
FROM users u
LEFT JOIN marketing_segments m
    ON LOWER(TRIM(m.email)) = LOWER(TRIM(u.email));

Logical Operators (Condition Chainers)

• AND: Joins conditions requiring all of them to be true.

  WHERE status = 'active' AND amount > 1000

• OR: Joins conditions requiring at least one to be true.

  WHERE status = 'active' OR status = 'trial'

• NOT: Inverts any condition from true to false (or false to true).

  WHERE NOT status = 'closed'

In the pipeline

These are the staging models from the Data pipeline tab. AND, OR, and NOT are boolean columns on the Salesforce lead source and the Stripe charge. They are not extra filters on the same row.

SELECT a.customer_id,
       (a.lead_source = 'webinar' AND c.status = 'succeeded') AS webinar_and_paid,
       (a.lead_source = 'webinar' OR c.status = 'succeeded') AS webinar_or_paid,
       (NOT (c.status = 'succeeded')) AS charge_not_succeeded
FROM stg_salesforce__accounts a
JOIN stg_stripe__charges c
    ON CAST(c.customer_id AS text) = CAST(a.customer_id AS text);

Character Data Types

• CHAR(n): Fixed-length character string. Space-padded to the specified length n.
  • Syntax: CHAR(size)

  CHAR(2) storing 'CA'

• VARCHAR(n): Variable-length character string with a maximum length n.
  • Syntax: VARCHAR(max_size)

  VARCHAR(100) storing 'John Doe'

• TEXT / LONGVARCHAR: Variable-length character data for large blocks of text.
  • Syntax: TEXT

  TEXT storing a full blog post body.

In the pipeline

These are the staging models from the Data pipeline tab. Casts the customer id to VARCHAR on both sides before Salesforce matches Stripe.

SELECT a.customer_id, c.amount_usd
FROM stg_salesforce__accounts a
JOIN stg_stripe__charges c
    ON CAST(c.customer_id AS VARCHAR) = CAST(a.customer_id AS VARCHAR)
WHERE c.status = 'succeeded';

Number Data Types

• INT / INTEGER: Whole numbers without decimals.
  • Syntax: INT or INTEGER

  INT storing 42 or -105

• DECIMAL / NUMERIC: Exact numeric values with a set precision and scale.
  • Syntax: DECIMAL(precision, scale) where precision is total digits and scale is digits after the decimal.

  DECIMAL(10, 2) storing 1999.95

• FLOAT / REAL / DOUBLE PRECISION: Approximate numeric values with floating-point decimals.
  • Syntax: FLOAT(precision) or DOUBLE PRECISION

  FLOAT storing 3.14159265

In the pipeline

These are the staging models from the Data pipeline tab. Casts the succeeded Stripe total to DECIMAL(12, 2) and the charge count to INT, grouped by Salesforce lead source.

SELECT a.lead_source,
       CAST(SUM(c.amount_usd) AS DECIMAL(12, 2)) AS billed,
       CAST(COUNT(c.customer_id) AS INT) AS charge_count
FROM stg_salesforce__accounts a
LEFT JOIN stg_stripe__charges c
    ON CAST(c.customer_id AS text) = CAST(a.customer_id AS text)
   AND c.status = 'succeeded'
GROUP BY a.lead_source;

String Literals Syntax

• Single Quotes: The quotes mark the text. The stored value is Hello World.

  'Hello World'

• Escaping Quotes: Two quotes in a row store one quote. This value is O’Reilly.

  'O''Reilly'

In the pipeline

These are the staging models from the Data pipeline tab. Stacks Salesforce and Stripe ids. Both sides cast the id to text, then label the row with a string literal.

SELECT CAST(customer_id AS text) AS customer_id, 'salesforce' AS source
FROM stg_salesforce__accounts
UNION ALL
SELECT CAST(customer_id AS text), 'stripe'
FROM stg_stripe__charges;

Number Literals Syntax

• Integers: A whole number is written with digits only. This value is 150.

  150

• Decimals: A period separates the whole number from the fraction. This value is 99.99.

  99.99

• Negative Values: A minus sign in front makes the number negative. This value is -500.

  -500

In the pipeline

These are the staging models from the Data pipeline tab. Left-joins Salesforce to the Stripe total and uses 0 when an account has no succeeded charges.

SELECT a.customer_id, COALESCE(b.billed, 0) AS billed
FROM stg_salesforce__accounts a
LEFT JOIN (
    SELECT CAST(customer_id AS text) AS customer_id, SUM(amount_usd) AS billed
    FROM stg_stripe__charges
    WHERE status = 'succeeded'
    GROUP BY CAST(customer_id AS text)
) b ON b.customer_id = CAST(a.customer_id AS text);

Arithmetic Operators

• +: Adds tax onto price.

  SELECT price + tax

• -: Subtracts the discount from price.

  SELECT price - discount

• *: Multiplies price by the number of units.

  SELECT price * quantity

• /: Divides the total by the quantity to get an amount per unit.

  SELECT total / quantity

• %: Divides 10 by 3 and returns the remainder, which is 1.

  SELECT 10 % 3

In the pipeline

These are the staging models from the Data pipeline tab. Adds, subtracts, multiplies, divides, and takes the remainder of the Salesforce acquisition cost and the succeeded Stripe total. Division and remainder use NULLIF so a zero count or a zero cost does not divide.

SELECT a.customer_id,
       a.customer_acquisition_cost + b.billed AS cost_plus_billed,
       a.customer_acquisition_cost - b.billed AS cost_minus_billed,
       b.billed * b.charge_count AS billed_times_count,
       b.billed / NULLIF(b.charge_count, 0) AS billed_per_charge,
       b.billed % NULLIF(a.customer_acquisition_cost, 0) AS billed_remainder
FROM stg_salesforce__accounts a
JOIN (
    SELECT CAST(customer_id AS text) AS customer_id,
           SUM(amount_usd) AS billed,
           COUNT(*) AS charge_count
    FROM stg_stripe__charges
    WHERE status = 'succeeded'
    GROUP BY CAST(customer_id AS text)
) b ON b.customer_id = CAST(a.customer_id AS text);

Comparison Operators

• =: True when amount is exactly 100.

  amount = 100

• != or <>: True when amount is anything other than 100.

  amount <> 100

• >: True when amount is above 100.

  amount > 100

• <: True when amount is below 100.

  amount < 100

• >=: True when amount is 100 or higher.

  amount >= 100

• <=: True when amount is 100 or lower.

  amount <= 100

• !>: True when amount is not above 100. This operator is dialect-specific.

  amount !> 100

• !<: True when amount is not below 100. This operator is dialect-specific.

  amount !< 100

In the pipeline

These are the staging models from the Data pipeline tab. Each comparison is a boolean column on acquisition cost and billed amount. The WHERE keeps succeeded charges. A filter that required billed to equal cost and billed to be greater than cost would return no rows.

SELECT a.customer_id,
       b.billed,
       a.customer_acquisition_cost AS cost,
       (b.billed = a.customer_acquisition_cost) AS cost_equals_billed,
       (b.billed <> a.customer_acquisition_cost) AS cost_differs,
       (b.billed > a.customer_acquisition_cost) AS billed_above_cost,
       (b.billed < a.customer_acquisition_cost) AS billed_below_cost,
       (b.billed >= a.customer_acquisition_cost) AS billed_at_least_cost,
       (b.billed <= a.customer_acquisition_cost) AS billed_at_most_cost
FROM stg_salesforce__accounts a
JOIN (
    SELECT CAST(customer_id AS text) AS customer_id, SUM(amount_usd) AS billed
    FROM stg_stripe__charges
    WHERE status = 'succeeded'
    GROUP BY CAST(customer_id AS text)
) b ON b.customer_id = CAST(a.customer_id AS text);

-- SQL Server. PostgreSQL writes the same check as NOT (billed > cost).
-- SQL Server spelling: billed !> cost
-- SQL Server spelling: billed !< cost
SELECT NOT (b.billed > a.customer_acquisition_cost) AS billed_not_greater
FROM stg_salesforce__accounts a
JOIN (
    SELECT CAST(customer_id AS text) AS customer_id, SUM(amount_usd) AS billed
    FROM stg_stripe__charges
    WHERE status = 'succeeded'
    GROUP BY CAST(customer_id AS text)
) b ON b.customer_id = CAST(a.customer_id AS text);

Logical and Relational Operators

• ALL: Compares a value to all values in another set or subquery

  WHERE amount > ALL (SELECT amount FROM refunds)

• AND: Combines multiple conditions (all must be true)

  WHERE status = 'active' AND amount > 1000

• ANY / SOME: Compares a value to any value in a set or subquery

  WHERE amount > ANY (SELECT amount FROM refunds)

• BETWEEN: Checks if a value falls within a specific range (inclusive)

  WHERE amount BETWEEN 10 AND 50

• EXISTS: Tests for the existence of rows in a subquery

  WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id)

• IN: Checks if a value matches any value in a specified list

  WHERE status IN ('active', 'trial')

• LIKE: Searches for a pattern using wildcards (%, _)

  WHERE email LIKE '%@gmail.com'

• NOT: Reverses or negates a logical condition

  WHERE NOT status = 'closed'

• OR: Combines conditions (at least one must be true)

  WHERE status = 'active' OR status = 'trial'

• IS NULL / IS NOT NULL: Tests for missing or non-missing (null) values

  WHERE email IS NULL

In the pipeline

These are the staging models from the Data pipeline tab. The WHERE can be true together: acquisition cost is greater than every succeeded charge for that customer, a charge exists, and the lead source is in the list. AND, OR, NOT, BETWEEN, LIKE, and IS NULL are columns on that same row.

SELECT a.customer_id,
       (a.lead_source = 'webinar' AND c.status = 'succeeded') AS webinar_and_paid,
       (a.lead_source = 'webinar' OR c.status = 'succeeded') AS webinar_or_paid,
       (NOT (c.status = 'failed')) AS not_failed,
       (b.billed BETWEEN 0 AND a.customer_acquisition_cost) AS billed_in_cost,
       (u.email LIKE '%@%') AS email_has_at,
       (m.email IS NULL) AS missing_segment
FROM stg_salesforce__accounts a
JOIN stg_stripe__charges c
    ON CAST(c.customer_id AS text) = CAST(a.customer_id AS text)
   AND c.status = 'succeeded'
JOIN users u
    ON LOWER(TRIM(u.email)) = LOWER(TRIM(a.email))
LEFT JOIN marketing_segments m
    ON LOWER(TRIM(m.email)) = LOWER(TRIM(u.email))
JOIN (
    SELECT CAST(customer_id AS text) AS customer_id, SUM(amount_usd) AS billed
    FROM stg_stripe__charges
    WHERE status = 'succeeded'
    GROUP BY CAST(customer_id AS text)
) b ON b.customer_id = CAST(a.customer_id AS text)
WHERE a.customer_acquisition_cost > ALL (
    SELECT amount_usd
    FROM stg_stripe__charges s
    WHERE CAST(s.customer_id AS text) = CAST(a.customer_id AS text)
      AND s.status = 'succeeded'
)
  AND EXISTS (
    SELECT 1
    FROM stg_stripe__charges s
    WHERE CAST(s.customer_id AS text) = CAST(a.customer_id AS text)
)
  AND a.lead_source IN ('webinar', 'referral', 'organic');

Bitwise Operators

• &: Keeps only the bits set in both 6 and 3. The result is 2.

  SELECT 6 & 3

• |: Keeps bits set in either 6 or 3. The result is 7.

  SELECT 6 | 3

• #: Keeps bits set in one number but not both. In PostgreSQL, 6 XOR 3 is 5. SQL Server and MySQL write XOR as ^.

  SELECT 6 # 3

• ~: Flips every bit in 6.

  SELECT ~6

• <<: Shifts 1 two places left, which multiplies it by 4 and returns 4.

  SELECT 1 << 2

• >>: Shifts 8 one place right, which divides it by 2 and returns 4.

  SELECT 8 >> 1

In the pipeline

These integer masks are named for this step. They are not columns on the Salesforce or Stripe extracts. # is the PostgreSQL XOR.

SELECT accounts.feature_mask & plans.feature_mask AS both_flags,
       accounts.feature_mask | plans.feature_mask AS either_flag,
       accounts.feature_mask # plans.feature_mask AS xor_flags,
       ~accounts.feature_mask AS flipped,
       accounts.feature_mask << 1 AS shifted_left,
       accounts.feature_mask >> 1 AS shifted_right
FROM accounts
JOIN plans ON plans.account_id = accounts.account_id;

Compound (Assignment) Operators

These operators are SQL Server. PostgreSQL has no += style operators.

• +=: Adds 10 to total and stores the new total.

  SET total += 10

• -=: Subtracts 10 from total and stores the new total.

  SET total -= 10

• *=: Multiplies total by 2 and stores the new total.

  SET total *= 2

• /=: Divides total by 2 and stores the new total.

  SET total /= 2

• %=: Replaces total with the remainder of total divided by 2.

  SET total %= 2

• &=: Keeps only the bits set in both flags and 3, then stores that result.

  SET flags &= 3

• |=: Turns on any bit set in 4, then stores the result in flags.

  SET flags |= 4

• ^=: Flips the bits in flags that are set in 1, then stores the result.

  SET flags ^= 1

In the pipeline

These statements are SQL Server. Each one updates customer_totals.total from the Stripe sum or the plan mask. The masks are not columns on the Salesforce or Stripe extracts. ^ is the SQL Server XOR.

UPDATE customer_totals
SET total += (
    SELECT COALESCE(SUM(amount_usd), 0)
    FROM stg_stripe__charges
    WHERE status = 'succeeded'
      AND CAST(customer_id AS text) = CAST(customer_totals.customer_id AS text)
);

UPDATE customer_totals
SET total -= (
    SELECT COALESCE(SUM(amount_usd), 0)
    FROM stg_stripe__charges
    WHERE status = 'succeeded'
      AND CAST(customer_id AS text) = CAST(customer_totals.customer_id AS text)
);

UPDATE customer_totals
SET total *= (
    SELECT COUNT(*)
    FROM stg_stripe__charges
    WHERE CAST(customer_id AS text) = CAST(customer_totals.customer_id AS text)
);

UPDATE customer_totals
SET total /= NULLIF((
    SELECT COUNT(*)
    FROM stg_stripe__charges
    WHERE CAST(customer_id AS text) = CAST(customer_totals.customer_id AS text)
), 0);

UPDATE customer_totals
SET total %= NULLIF((
    SELECT COALESCE(SUM(amount_usd), 0)
    FROM stg_stripe__charges
    WHERE status = 'succeeded'
      AND CAST(customer_id AS text) = CAST(customer_totals.customer_id AS text)
), 0);

UPDATE customer_totals
SET total &= plans.feature_mask
FROM plans
WHERE plans.customer_id = customer_totals.customer_id;

UPDATE customer_totals
SET total |= plans.feature_mask
FROM plans
WHERE plans.customer_id = customer_totals.customer_id;

UPDATE customer_totals
SET total ^= plans.feature_mask
FROM plans
WHERE plans.customer_id = customer_totals.customer_id;

String Functions

These functions are used to manipulate text data.

• CONCAT(): Joins two or more strings together.

  SELECT CONCAT('John', ' ', 'Doe')

• SUBSTRING() / SUBSTR(): Extracts a specific portion of a string.

  SELECT SUBSTRING('John Doe' FROM 1 FOR 4)

• LENGTH() / LEN(): Returns the number of characters in a string. LENGTH() is PostgreSQL. LEN() is SQL Server.

  SELECT LENGTH('John Doe')

• UPPER(): Converts a string to uppercase letters.

  SELECT UPPER('john')

• LOWER(): Converts a string to lowercase letters.

  SELECT LOWER('JOHN')

• TRIM(): Removes leading and trailing spaces from a string.

  SELECT TRIM('  John  ')

• REPLACE(): Replaces occurrences of a specified string with another string value.

  SELECT REPLACE('John Doe', 'Doe', 'Smith')

In the pipeline

These are the staging models from the Data pipeline tab. Matches Salesforce and users on the trimmed, lowercased email from both tables.

SELECT a.customer_id, u.email
FROM stg_salesforce__accounts a
JOIN users u
    ON LOWER(TRIM(u.email)) = LOWER(TRIM(a.email));

Numeric & Mathematical Functions

These functions are used to perform mathematical operations on numeric data.

• ROUND(): Rounds a number to a specified number of decimal places.

  SELECT ROUND(3.14159, 2)

• CEILING() / CEIL(): Rounds a number up to the nearest integer.

  SELECT CEILING(3.2)

• FLOOR(): Rounds a number down to the nearest integer.

  SELECT FLOOR(3.8)

• ABS(): Returns the absolute (positive) value of a number.

  SELECT ABS(-42)

• MOD(): Returns the remainder of a division operation.

  SELECT MOD(10, 3)

• POWER(): Raises a number to the power of a specified exponent.

  SELECT POWER(2, 3)

In the pipeline

These are the staging models from the Data pipeline tab. Rounds the succeeded Stripe total to cents, then subtracts the Salesforce acquisition cost.

SELECT a.customer_id,
       ROUND(b.billed, 2) - a.customer_acquisition_cost AS margin
FROM stg_salesforce__accounts a
JOIN (
    SELECT CAST(customer_id AS text) AS customer_id, SUM(amount_usd) AS billed
    FROM stg_stripe__charges
    WHERE status = 'succeeded'
    GROUP BY CAST(customer_id AS text)
) b ON b.customer_id = CAST(a.customer_id AS text);

Date & Time Functions

These functions allow you to manipulate and format date and time values.

• NOW() / CURRENT_TIMESTAMP: Returns the current date and time.

  SELECT CURRENT_TIMESTAMP

• CURDATE() / CURRENT_DATE: Returns the current date only. CURDATE() is MySQL. CURRENT_DATE is PostgreSQL.

  SELECT CURRENT_DATE

• DATEADD() / DATE_ADD(): Adds a specific time interval to a date. DATEADD() is SQL Server. DATE_ADD() is MySQL. PostgreSQL adds an interval.

  SELECT DATE_ADD('2026-10-01', INTERVAL 7 DAY)

  SELECT DATE '2026-10-01' + INTERVAL '7 days'

• DATEDIFF(): Calculates the number of days between two dates. DATEDIFF() is SQL Server. PostgreSQL subtracts the dates.

  SELECT DATEDIFF('2026-10-15', '2026-10-01')

  SELECT DATE '2026-10-15' - DATE '2026-10-01'

• YEAR() / MONTH() / DAY(): Extracts the specific year, month, or day component from a date. These names are MySQL. PostgreSQL uses EXTRACT.

  SELECT YEAR('2026-10-15')

  SELECT EXTRACT(YEAR FROM DATE '2026-10-15')

In the pipeline

These are the staging models from the Data pipeline tab. Truncates the Stripe charge month beside the Salesforce account month.

SELECT DATE_TRUNC('month', c.created_at) AS charge_month,
       DATE_TRUNC('month', a.created_at) AS account_month,
       SUM(c.amount_usd) AS billed
FROM stg_stripe__charges c
JOIN stg_salesforce__accounts a
    ON CAST(c.customer_id AS text) = CAST(a.customer_id AS text)
WHERE c.status = 'succeeded'
GROUP BY 1, 2;