SQL for Data Analysis Questions

How they compare

• SUMIFS / COUNTIFS / AVERAGEIFS are best when I need a formula-driven output in a specific cell, such as one KPI tile or one row in a report.
• PivotTables are better when I need fast exploration, multiple groupings, drill-down, and a layout that non-technical users can filter.

When I prefer conditional aggregates

• The report needs a fixed template with stable cells.
• I want formulas that are easy to copy across months or categories.
• I need results tied directly to dashboard tiles or other formulas.
• I want transparent logic that can be audited cell by cell.

When I prefer PivotTables

• The question is exploratory or may change often.
• I need summaries by many dimensions, like region, product, and channel.
• I want slicers, timelines, drill-through, or a chart-ready summary.
• The dataset is large enough that formulas would become slow or cluttered.

When I avoid conditional aggregates

• If the workbook has many rows and many formulas, recalculation can get heavy.
• If the grouping needs to be dynamic, PivotTables are usually easier to maintain.
• If stakeholders need self-service filtering, a PivotTable is more user-friendly.

In practice, I often use both: a PivotTable for exploration and a few SUMIFS or COUNTIFS formulas for the final dashboard tiles.

Join each transaction to fx_rates on matching currency and rate_date <= transaction_date, then use ROW_NUMBER() partitioned by transaction and ordered by rate_date DESC to pick the single most recent rate at or before the transaction date. That's a last-observation-carried-forward join: instead of requiring an exact-date match, you rank all eligible prior rates and keep the top one.

The join pattern

A naive fx_rates.rate_date = transactions.transaction_date join fails the moment a rate is missing for a given day (a weekend, a holiday, a data gap), which drops that transaction from the result entirely. The fix is a range join (rate_date <= transaction_date) followed by a window function to collapse the resulting one-to-many matches down to the single best candidate per transaction:

WITH ranked AS (
  SELECT
    t.transaction_id,
    t.transaction_date,
    t.currency,
    t.amount,
    r.rate_date,
    r.usd_per_unit,
    ROW_NUMBER() OVER (
      PARTITION BY t.transaction_id
      ORDER BY r.rate_date DESC
    ) AS rn
  FROM transactions t
  LEFT JOIN fx_rates r
    ON r.currency_code = t.currency
    AND r.rate_date <= t.transaction_date
)
SELECT transaction_id, transaction_date, currency, amount,
       rate_date AS rate_used_from, usd_per_unit,
       ROUND(amount * usd_per_unit, 2) AS usd_amount
FROM ranked
WHERE rn = 1
ORDER BY transaction_id;

LEFT JOIN (not INNER JOIN) matters here too: it keeps a transaction in the output (with a NULL rate) if no prior rate exists at all yet, rather than dropping it silently, the same silent-drop risk as any other join.

Worked example

Run in sqlite3 with dates stored as ISO-8601 text (sqlite3 has no native DATE type, so string comparison and lexical ordering both happen to work). In Postgres, MySQL, SQL Server, Snowflake, or BigQuery, rate_date and transaction_date would be native DATE columns; the join condition and ROW_NUMBER() logic are unchanged, only the column typing differs.

Seed fx_rates(rate_date, currency_code, usd_per_unit) with EUR rates on 2026-07-01 and 2026-07-03 (no rate on 2026-07-02, a deliberate gap) and a GBP rate on 2026-07-01:

fx_rates: (2026-07-01, EUR, 1.08), (2026-07-03, EUR, 1.09), (2026-07-01, GBP, 1.27)

Seed 4 transactions, including one that lands exactly on the gap day:

transactions: (1, 2026-07-01, EUR, 100), (2, 2026-07-02, EUR, 200),
              (3, 2026-07-03, EUR, 50),  (4, 2026-07-01, GBP, 100)

Run output:

transaction_id  transaction_date  currency  amount  rate_used_from  usd_per_unit  usd_amount
1               2026-07-01        EUR       100     2026-07-01      1.08          108.0
2               2026-07-02        EUR       200     2026-07-01      1.08          216.0
3               2026-07-03        EUR       50      2026-07-03      1.09          54.5
4               2026-07-01        GBP       100     2026-07-01      1.27          127.0

Transaction 2, dated 2026-07-02 with no EUR rate that day, correctly falls back to the 2026-07-01 rate (1.08) rather than being dropped or getting a NULL rate. Transaction 3, dated exactly on 2026-07-03, uses that day's own rate (1.09) rather than the older 1.08, confirming the ORDER BY rate_date DESC picks the closest prior rate, not just any prior rate.

Trade-offs and pitfalls

• Correlated-subquery alternative: instead of ROW_NUMBER(), you can use a correlated subquery (SELECT usd_per_unit FROM fx_rates WHERE currency_code = t.currency AND rate_date <= t.transaction_date ORDER BY rate_date DESC LIMIT 1) or a lateral join where the engine supports it. The window-function version above tends to be easier for the optimizer to turn into a single merge-join-like plan across many transactions at once, versus paying a per-row subquery cost.
• Indexing: fx_rates(currency_code, rate_date) as a composite index is what makes the range join cheap; without it, every transaction potentially scans the whole rate history for its currency.
• Missing rate entirely: if a currency has no rate on or before the transaction date at all (e.g. a brand-new currency added to transactions before fx_rates was backfilled), the LEFT JOIN leaves usd_per_unit NULL and usd_amount NULL rather than erroring; decide deliberately whether to flag those for manual review or fall back to a default, don't let them silently zero out in a downstream SUM.
• Staleness risk: this pattern can carry a rate forward indefinitely if there's a long gap (a currency stops being reported for months); consider capping how far back the fallback is allowed to reach, or flagging transactions whose matched rate is more than N days stale.
• Retroactive rate corrections: if fx_rates is later corrected for a historical date, any transaction converted using the old value needs to be reprocessed; this join is not idempotent against a fx_rates update unless you re-run it.

Direct answer

INNER JOIN keeps only rows where the join key matches in both tables. LEFT JOIN keeps every row from the left table and fills unmatched right-side columns with NULL. RIGHT JOIN is the mirror image (every row from the right table, unmatched left-side columns are NULL). FULL OUTER JOIN keeps every row from both sides, with NULLs on whichever side didn't match.

Structured elaboration

A useful mental model: start from LEFT JOIN (all of the left table, matched where possible), and each other join type is a variation of which side you insist on keeping in full.

Worked example (sqlite3, verified)

Sample data:

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER);

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');
INSERT INTO orders VALUES
  (101, 1, 50),
  (102, 1, 30),
  (103, 2, 20),
  (104, 9, 75);  -- order for a customer_id that does not exist (orphan row)

Carol (customer_id 3) has never ordered. Order 104 belongs to customer_id 9, who doesn't exist in customers, an intentional reconciliation-style gap (an order with no matching customer, the mirror case of a customer with no orders).

INNER JOIN:

SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

Result (3 rows): (1,'Alice',101,50), (1,'Alice',102,30), (2,'Bob',103,20). Carol and order 104 both vanish.

LEFT JOIN:

SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

Result (4 rows): the 3 inner rows plus (3,'Carol',NULL,NULL). Carol appears once with NULL order columns; order 104 still doesn't appear (it lives on the right side).

RIGHT JOIN:

SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

Result (4 rows): the 3 inner rows plus (NULL,NULL,104,75). Now order 104 survives with NULL customer columns; Carol drops out.

FULL OUTER JOIN:

SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

Result (5 rows): the 3 inner rows, (3,'Carol',NULL,NULL), and (NULL,NULL,104,75). Both unmatched sides are preserved.

Business-reporting framing (reconciliation). This is exactly the shape of a reconciliation report: "which customers show up in our CRM but never billed" versus "which billing records reference a customer we don't recognize." A FULL OUTER JOIN filtered to only the unmatched rows surfaces both at once:

SELECT c.customer_id AS cust_id, o.customer_id AS order_cust_id, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL OR o.order_id IS NULL;

Result (2 rows): (NULL, 9, 104) (an order referencing a missing customer) and (3, NULL, NULL) (a customer with no orders). In finance/reconciliation terms these are the two classic break types: unmatched source records and unmatched target records.

Trade-offs & pitfalls

• MySQL has no FULL OUTER JOIN keyword (and older MySQL/other engines also lack RIGHT JOIN in some restricted dialects, though RIGHT JOIN is broadly supported). The portable emulation is LEFT JOIN ... UNION ... RIGHT JOIN ... filtered appropriately, or LEFT JOIN ... UNION ... (INNER JOIN swapped). This answer's queries were run and verified against SQLite 3.39+, which supports RIGHT JOIN and FULL OUTER JOIN natively.
• RIGHT JOIN is rarely used in practice because you can always rewrite it as a LEFT JOIN by swapping the table order; most style guides prefer that for readability.
• A common mistake: putting a filter on the right table in the WHERE clause instead of the ON clause of a LEFT JOIN. WHERE o.amount > 10 after a LEFT JOIN silently turns it back into an INNER JOIN, because NULL rows fail that comparison. The filter belongs in the ON clause if you want to keep unmatched rows: LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.amount > 10.
• If the join key isn't unique on one side, a "safe" LEFT JOIN can still fan out rows (one customer row duplicated once per matching order), which quietly breaks downstream counts and sums if you forget to aggregate first.

Direct answer

Produce one output row per column by computing the same four metrics (null count, distinct count, min, max) for every column and stacking the results with UNION ALL, so the profile reads top-to-bottom as a column-by-column summary instead of one wide, unreadable row. For a table you'll profile repeatedly, generate that UNION ALL SQL programmatically from information_schema.columns instead of hand-writing it, so adding a column to the table doesn't require remembering to update the profiling query too.

Structured elaboration

1. One row per column, not one row per table. A wide row with customer_id_null_count, customer_id_distinct_count, email_null_count, ... for a 20-column table is unreadable; a UNION ALL stack with column_name, null_count, distinct_count, min_value, max_value scales to any width and is trivial to scan or plot.
2. Cast min/max to a common type (VARCHAR) across the UNION ALL branches, since different columns have different underlying types and UNION ALL requires matching types column-by-column.
3. Automate the generation for real use. Querying information_schema.columns for a table name and building the SELECT ... UNION ALL text programmatically means the profiling query never goes stale when a column is added or dropped. This is the single biggest lever for "profile hundreds of tables nightly" at scale, more than any per-query optimization.

Worked example

Seed data: a customers table, 7 rows, with NULLs scattered across every column except customer_id.

Hand-written profile:

SELECT 'customer_id' AS column_name,
       COUNT(*) FILTER (WHERE customer_id IS NULL) AS null_count,
       COUNT(DISTINCT customer_id) AS distinct_count,
       MIN(customer_id)::VARCHAR AS min_value, MAX(customer_id)::VARCHAR AS max_value
FROM customers
UNION ALL
SELECT 'email', COUNT(*) FILTER (WHERE email IS NULL), COUNT(DISTINCT email),
       MIN(email), MAX(email) FROM customers
UNION ALL
SELECT 'signup_date', COUNT(*) FILTER (WHERE signup_date IS NULL), COUNT(DISTINCT signup_date),
       MIN(signup_date)::VARCHAR, MAX(signup_date)::VARCHAR FROM customers
UNION ALL
SELECT 'country', COUNT(*) FILTER (WHERE country IS NULL), COUNT(DISTINCT country),
       MIN(country), MAX(country) FROM customers
UNION ALL
SELECT 'lifetime_value', COUNT(*) FILTER (WHERE lifetime_value IS NULL), COUNT(DISTINCT lifetime_value),
       MIN(lifetime_value)::VARCHAR, MAX(lifetime_value)::VARCHAR FROM customers;

Real output:

Now generate the same query programmatically from information_schema.columns (this is the piece that makes profiling scale to many tables):

SELECT string_agg(
  'SELECT ''' || column_name || ''' AS column_name, ' ||
  'COUNT(*) FILTER (WHERE ' || column_name || ' IS NULL) AS null_count, ' ||
  'COUNT(DISTINCT ' || column_name || ') AS distinct_count, ' ||
  'MIN(' || column_name || ')::VARCHAR AS min_value, ' ||
  'MAX(' || column_name || ')::VARCHAR AS max_value FROM customers',
  ' UNION ALL '
  ORDER BY ordinal_position
) AS generated_sql
FROM information_schema.columns
WHERE table_name = 'customers';

This produced the exact SELECT ... UNION ALL ... text shown above, generated from the table's actual column list rather than typed by hand. Running that generated text produced the identical result set shown above, confirming the generator and the hand-written version agree.

Trade-offs & pitfalls

• MIN/MAX on a text column is lexical, not semantic ('Zebra' < 'apple' in a case-sensitive collation because uppercase sorts before lowercase); treat min/max on strings as "what shape the values take," not a business ordering.
• Sample values (a LIMIT 3 of distinct non-null values, via ORDER BY random() or similar) are more useful than min/max for spotting formatting problems (mixed casing, stray whitespace) that a lexical min/max won't surface; add that alongside min/max, not instead of it.
• COUNT(DISTINCT ...) on a huge table is a real cost (a full distinct scan per column); for a nightly profile over very large tables, an approximate distinct count (e.g. HyperLogLog-based functions where the engine supports them) trades a small amount of accuracy for a large reduction in scan cost.
• Regenerating and running the profiling SQL from information_schema on every run means it never goes stale, but also means it profiles a column you may not care about (an internal audit column, say); filter information_schema.columns by name pattern or a maintained allowlist if that's a concern.

A self-join and an anti-join both reuse the same JOIN mechanics as an INNER/LEFT JOIN, but they solve different problems. A self-join relates rows in a table to other rows in the same table (an employee to their manager, one event to the next), so you alias the table twice and join those aliases together. An anti-join answers a negative existence question: which rows in A have no matching row in B at all (customers with no orders, users who viewed a product but never bought it). A plain INNER/LEFT JOIN combines matching rows across two different tables; it isn't built for either of those shapes without extra logic.

Self-join: relating a table to itself

Reach for a self-join when the relationship you care about is within one entity: a hierarchy (employee to manager), a peer comparison (products in the same category), or comparing adjacent rows (though for ordered "next row" comparisons, a window function like LAG/LEAD is usually cleaner than a self-join today).

Mechanics: alias the table twice (e.g. e and m), then join on the self-referencing key or comparison condition. Use a LEFT JOIN if some rows legitimately have no self-match (a CEO with no manager).

Anti-join: rows with no match at all

Reach for an anti-join whenever the question is phrased as "which X have never / no Y": customers with no orders, employees with no timesheet this month, products never sold. There are three equivalent ways to write it:

• NOT EXISTS (correlated subquery): reads as "for each row in A, does not exist any row in B matching." Null-safe and usually optimizes into an efficient anti-join.
• LEFT JOIN ... WHERE b.key IS NULL: join everything, then keep only the rows where the right side never matched.
• NOT IN (subquery): works, but is unsafe if the subquery can return a NULL, which silently empties the whole result. That NULL trap is deep enough to deserve its own treatment; the short version is: prefer NOT EXISTS or LEFT JOIN ... IS NULL unless you can guarantee the subquery column is NOT NULL.

Worked example

Run in sqlite3; the syntax used (LEFT JOIN, NOT EXISTS) is standard ANSI SQL and portable across Postgres, MySQL 8+, SQL Server, Snowflake, and BigQuery unchanged.

Self-join on an employees(employee_id, name, manager_id) table:

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
ORDER BY e.employee_id;

Run against seeded data (Ada has no manager, Grace and Alan report to Ada, Linus reports to Grace):

employee  manager
--------  -------
Ada
Grace     Ada
Alan      Ada
Linus     Grace

Anti-join on customers(customer_id, name) and orders(order_id, customer_id, amount), seeded with customers 1-4 where only 1 and 3 have orders:

SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_id;

customer_id  name
-----------  ------------
2            Bolt Inc
4            Dune Traders

The LEFT JOIN ... IS NULL form returns the identical two rows:

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

Alternate scenario, same pattern: the anti-join is also the standard shape for a reconciliation check, where you're looking for transactions missing from a ledger rather than customers missing from orders. Given transactions(txn_id, amount) seeded with 4 rows and ledger(ledger_id, txn_id, amount) seeded with only the first 3 of those transaction ids recorded:

SELECT t.txn_id, t.amount
FROM transactions t
WHERE NOT EXISTS (
  SELECT 1 FROM ledger l WHERE l.txn_id = t.txn_id
)
ORDER BY t.txn_id;

txn_id  amount
------  ------
4       999

Transaction 4 never made it into the ledger. Same query shape (NOT EXISTS anti-join), different business problem: unreconciled transactions instead of customers with no orders.

Trade-offs and pitfalls

• Self-join dedup: joining a table to itself with a symmetric condition (e.g. "products in the same category") returns both (A, B) and (B, A), plus (A, A). Add a canonical ordering condition like a.id < b.id if you only want each pair once.
• Self-join cost: without an index on the join key (here, manager_id and employee_id), a self-join on a large table is as expensive as any other join between two same-size tables; it isn't free just because it's "the same table."
• Anti-join indexing: NOT EXISTS and LEFT JOIN ... IS NULL both benefit heavily from an index on the correlated/join column (orders.customer_id); without it, the engine has to scan the right table once per left row (or build a full hash of it), which is the main cost driver at scale.
• NOT IN's NULL trap: worth naming here even though the fix belongs to a separate discussion: if the subquery column can contain NULL, NOT IN silently returns zero rows instead of erroring, which is far more dangerous than a slow query.