SQL for Data Analysis Questions

Direct answer

Monthly churn for month M compares each user's activity in month M-1 (the reference month) against month M (the evaluation month): a user counts as churned if they had at least one event in M-1 and zero events in M. The edge case that needs an explicit decision is exclusion: a user is only eligible to churn out of a month if they already existed as a user before the evaluation month began.

Structured elaboration

• Build a de-duplicated user-month activity table first (one row per user per month with any activity). Every other step is a self-join against this table, shifted by one month.
• "Active in M-1" defines the churn base, the denominator. "Inactive in M" defines who churned, the numerator.
• Edge case decided: a user who signs up mid-month and has one event that same month is treated as "active" for that month like anyone else. No partial-month handling.
• Edge case decided: users whose signup falls inside the evaluation month M itself are excluded from the base. In practice this guards mainly against stray pre-signup test events, since a genuinely new user has no prior-month activity to begin with, but it is worth naming explicitly rather than leaving implicit.
• Sparse months (zero users active in M-1) return a NULL rate rather than raising a divide-by-zero error. This only holds if the denominator (eligible_prev_active) is computed by joining the generated month spine to the eligible-user rows with a LEFT JOIN, as shown below. Grouping the eligible-user rows directly (an inner join under the hood) silently drops any month whose base is empty instead of returning a zero-row.

Worked example

Sample data, tested in DuckDB 1.5:

users:

events:

WITH months AS (
  SELECT UNNEST(generate_series(
    date_trunc('month', (SELECT MIN(event_time) FROM events))::date,
    date_trunc('month', (SELECT MAX(event_time) FROM events))::date,
    INTERVAL 1 MONTH
  )) AS month_start
),
user_month_activity AS (
  SELECT user_id, date_trunc('month', event_time)::date AS month_start
  FROM events
  GROUP BY 1, 2
),
prev_month_active AS (
  SELECT m.month_start, ua.user_id
  FROM months m
  JOIN user_month_activity ua ON ua.month_start = (m.month_start - INTERVAL 1 MONTH)::date
),
curr_month_active AS (
  SELECT m.month_start, ua.user_id
  FROM months m
  JOIN user_month_activity ua ON ua.month_start = m.month_start
),
eligible_prev AS (
  SELECT p.month_start, p.user_id
  FROM prev_month_active p
  JOIN users u ON u.user_id = p.user_id
  WHERE NOT (u.signup_date >= p.month_start AND u.signup_date < (p.month_start + INTERVAL 1 MONTH))
),
monthly_churn AS (
  SELECT e.month_start, COUNT(*) AS churned_users
  FROM eligible_prev e
  LEFT JOIN curr_month_active c ON e.user_id = c.user_id AND e.month_start = c.month_start
  WHERE c.user_id IS NULL
  GROUP BY 1
),
prev_counts AS (
  SELECT m.month_start, COUNT(e.user_id) AS eligible_prev_active
  FROM months m
  LEFT JOIN eligible_prev e ON e.month_start = m.month_start
  GROUP BY 1
)
SELECT
  pc.month_start,
  pc.eligible_prev_active,
  COALESCE(mc.churned_users, 0) AS churned_users,
  CASE WHEN pc.eligible_prev_active = 0 THEN NULL
       ELSE ROUND(100.0 * COALESCE(mc.churned_users,0) / pc.eligible_prev_active, 2)
  END AS churn_pct
FROM prev_counts pc
LEFT JOIN monthly_churn mc USING (month_start)
ORDER BY pc.month_start;

Result:

Trace: November's reference month (October 2023) has no data at all, so the base is 0 and the row correctly surfaces with churn_pct = NULL instead of being dropped. December's base is just user 4 (active in November), who stayed active in December, so 0 churned. January's base is users 1, 2, 4 (active in December); user 4 has no January event, so 1 churned (33.33%). February's base is users 1, 2, 3 (active in January); users 2 and 3 have no February event, so 2 churned (66.67%). User 3, who signed up January 10 and is active in January, is correctly included in February's base (not excluded), since the exclusion rule only protects against signing up during the evaluation month itself, not the reference month.

Trade-offs & pitfalls

• This needs one row per user per active month, not per event, so pre-aggregating activity into a monthly rollup table is worth it once the raw events table is large. Recomputing MIN/MAX over the whole events table for every month-spine generation, as shown here, does not scale, precompute the bounds instead in production.
• The "excluded if new this month" rule only bites on unusual data (pre-signup test events). Most of its practical effect is defensive. It is easy to mistake it for something bigger, like a grace period, so state its actual scope explicitly to stakeholders.
• generate_series / date_trunc / INTERVAL syntax shown is DuckDB/PostgreSQL. MySQL and SQL Server need a numbers-table or recursive CTE substitute for generating the month spine.
• Reactivation (a churned user returning two months later) is not double-penalized or double-rewarded, each month's churn is evaluated independently against only the immediately preceding month.
• Building the denominator (prev_counts) by grouping straight off an inner-joined CTE looks equivalent to grouping off the month spine with a LEFT JOIN, but it is not: any month whose base is genuinely empty vanishes from the result set instead of appearing with a NULL rate. Always drive the final row set from the spine, not from the innermost joined table, when a zero-count month needs to be visible.

Find each entity's earliest occurrence of the first event and earliest occurrence of the second event with two separate MIN() aggregations grouped by the entity key, then LEFT JOIN them together and take the date difference. The LEFT JOIN direction matters: entities that never reached the second event (a visitor who never purchased, a ticket that never got a response) must still appear, with a NULL delta, or an average silently reflects only the entities that converted.

Approach

1. first_visit: MIN(visit_time) per entity (here, per user and channel, since the business question is time-to-purchase by channel).
2. first_purchase: MIN(purchase_time) per user.
3. LEFT JOIN first_purchase onto first_visit, never the reverse, so unconverted entities remain in the result.
4. Take the date difference with the engine's date-diff mechanism. SQLite's julianday() subtraction gives fractional days directly; AVG() grouped by channel gives the answer per channel.
5. Report COUNT(*) (everyone seen) alongside COUNT(days_to_purchase) (everyone who actually converted), so the average is never shown without its own denominator.

Worked example

CREATE TABLE visits (
  user_id    INTEGER,
  channel    TEXT,
  visit_time TEXT
);

CREATE TABLE purchases (
  user_id       INTEGER,
  purchase_time TEXT
);

INSERT INTO visits VALUES
  (1, 'organic', '2026-01-01'),
  (2, 'paid',    '2026-01-02'),
  (2, 'paid',    '2026-01-03'),
  (3, 'organic', '2026-01-05'),
  (4, 'paid',    '2026-01-01');

INSERT INTO purchases VALUES
  (1, '2026-01-04'),
  (2, '2026-01-10'),
  (4, '2026-01-01');

WITH first_visit AS (
  SELECT user_id, channel, MIN(visit_time) AS first_visit_time
  FROM visits
  GROUP BY user_id, channel
),
first_purchase AS (
  SELECT user_id, MIN(purchase_time) AS first_purchase_time
  FROM purchases
  GROUP BY user_id
),
joined AS (
  SELECT
    fv.user_id,
    fv.channel,
    fv.first_visit_time,
    fp.first_purchase_time,
    julianday(fp.first_purchase_time) - julianday(fv.first_visit_time) AS days_to_purchase
  FROM first_visit fv
  LEFT JOIN first_purchase fp ON fv.user_id = fp.user_id
)
SELECT
  channel,
  COUNT(*)                       AS users,
  COUNT(days_to_purchase)        AS converters,
  ROUND(AVG(days_to_purchase),2) AS avg_days_to_purchase
FROM joined
GROUP BY channel
ORDER BY channel;

Result:

┌─────────┬───────┬────────────┬──────────────────────┐
│ channel │ users │ converters │ avg_days_to_purchase │
├─────────┼───────┼────────────┼──────────────────────┤
│ organic │ 2     │ 1          │ 3.0                  │
│ paid    │ 2     │ 2          │ 4.0                  │
└─────────┴───────┴────────────┴──────────────────────┘

Organic's one converter (user 1) took 3 days (Jan 1 to Jan 4). Paid has two converters: user 2 took 8 days (Jan 2 to Jan 10) and user 4 took 0 days (visited and purchased same day, Jan 1), averaging to (8 + 0) / 2 = 4.0, matching the shown result exactly. User 3 (organic, no purchase) is still counted in users but correctly excluded from converters and from the average.

Trade-offs & pitfalls

• Complexity: two GROUP BY aggregations (a linear scan each) plus one join; cheap even at scale. Table size, not this technique, is the real cost driver.
• Edge case: an entity with the second event but no logged first event (a purchase from a user whose visit was never recorded, a data-quality gap) won't appear at all when the query starts FROM the first-event table. Decide upfront whether the base of the query should be the union of both event tables' entity keys, not just one side.
• Edge case: a second event that predates the recorded first event (a backfilled or duplicated visit row inserted after the purchase) produces a negative delta. Validate for negative deltas rather than trusting AVG() to average them away unnoticed.
• A mean over a skewed delay distribution (a handful of very slow converters) is easily dominated by outliers; a median or percentile is often the better headline number for a stakeholder, which is a natural extension of this query, not part of the base version.
• Common wrong turn: using INNER JOIN instead of LEFT JOIN. That drops every never-converted entity and quietly turns "average time to convert, overall" into "average time to convert, among the subset who already converted," two different numbers wearing the same label.

Direct answer

LTV here means simple, undiscounted, all-time net revenue booked per customer to date: SUM(orders.amount) per customer, including customers with zero orders shown as $0 rather than dropped. That's a deliberate scope choice: a full discounted or predicted lifetime-value model (survival curves, cohort decay) is a distinct, more advanced technique and out of scope for this cumulative-sum version. Buckets should be derived from the actual revenue distribution in the result (a ranking function like NTILE), not from hardcoded dollar thresholds picked without looking at the data.

Structured elaboration

Approach:

1. LEFT JOIN from customers to orders (not INNER JOIN), so customers who signed up but never purchased still appear, with COALESCE(SUM(amount), 0) giving them $0 instead of vanishing.
2. Rank customers by total revenue and split into three groups with NTILE(3), low/medium/high by definition of the split, not by a business-defined cutoff.
3. Inspect the actual boundary values before presenting the buckets as final; a data-driven split still needs a sanity check against whether it makes business sense.

Key points:

• NTILE(3) always produces exactly three equal-sized groups, whatever the underlying shape of the distribution. That's a genuinely different guarantee than "three business-meaningful tiers," and the two are easy to conflate.
• Fixed dollar thresholds (e.g., "high = over $1,000") are the alternative, better once a business actually has a defined target, worse as a first pass when nobody has validated where the real breakpoints in the data are.

Worked example

Seed data: 9 customers, one with zero orders, revenue totals ranging $0 to $5,000.

WITH ltv AS (
  SELECT
    c.customer_id,
    c.signup_date,
    COALESCE(SUM(o.amount), 0) AS total_revenue
  FROM customers c
  LEFT JOIN orders o ON o.user_id = c.customer_id
  GROUP BY c.customer_id, c.signup_date
)
SELECT
  customer_id,
  signup_date,
  total_revenue,
  NTILE(3) OVER (ORDER BY total_revenue) AS revenue_tertile,
  CASE NTILE(3) OVER (ORDER BY total_revenue)
    WHEN 1 THEN 'low'
    WHEN 2 THEN 'medium'
    WHEN 3 THEN 'high'
  END AS ltv_bucket
FROM ltv
ORDER BY total_revenue;

Result (run in DuckDB):

customer_id | signup_date | total_revenue | revenue_tertile | ltv_bucket
------------+-------------+---------------+-----------------+-----------
          1 |  2025-01-05 |          0.00 |               1 | low
          2 |  2025-01-20 |         50.00 |               1 | low
          3 |  2025-02-01 |        120.00 |               1 | low
          4 |  2025-02-14 |        300.00 |               2 | medium
          5 |  2025-03-01 |        450.00 |               2 | medium
          6 |  2025-03-15 |        900.00 |               2 | medium
          7 |  2025-04-01 |       1200.00 |               3 | high
          8 |  2025-04-20 |       2500.00 |               3 | high
          9 |  2025-05-01 |       5000.00 |               3 | high

Customer 1, who never placed an order, correctly appears at $0.00 in the "low" bucket rather than being silently absent from the report.

Bucket boundaries, checked explicitly before shipping the split:

WITH ltv AS (
  SELECT c.customer_id, COALESCE(SUM(o.amount), 0) AS total_revenue
  FROM customers c
  LEFT JOIN orders o ON o.user_id = c.customer_id
  GROUP BY c.customer_id
),
bucketed AS (
  SELECT *, NTILE(3) OVER (ORDER BY total_revenue) AS revenue_tertile
  FROM ltv
)
SELECT revenue_tertile, COUNT(*) AS n_customers,
  MIN(total_revenue) AS min_revenue, MAX(total_revenue) AS max_revenue
FROM bucketed
GROUP BY revenue_tertile
ORDER BY revenue_tertile;

Result:

revenue_tertile | n_customers | min_revenue | max_revenue
----------------+-------------+-------------+------------
              1 |           3 |        0.00 |      120.00
              2 |           3 |      300.00 |      900.00
              3 |           3 |     1200.00 |     5000.00

Here the tertiles happen to line up with reasonably clean gaps ($120 to $300, $900 to $1200), so the data-driven split looks defensible. That won't always be true, which is exactly why this boundary table should be checked, not assumed.

Complexity

A single LEFT JOIN and GROUP BY is O(n) in order rows given an index on orders.user_id, followed by an O(m log m) sort for the NTILE window function over m customers. Neither step changes complexity class if the bucket count changes.

Edge cases

• Customers with zero orders (customer 1 above): must appear at $0.00, not be dropped, which is exactly why the join direction matters.
• Ties at a bucket boundary: NTILE splits deterministically by row order once ties are broken by the ORDER BY; if two customers have identical revenue near a boundary, add a stable tiebreaker (e.g., customer_id) to the ORDER BY so the split is reproducible on re-run.
• Refunds or negative-amount orders: not modeled here; if orders.amount can go negative, decide whether LTV nets them out (usually yes) and confirm the seed assumption before trusting the totals.

Trade-offs & pitfalls

• Using INNER JOIN instead of LEFT JOIN is a very common version of this mistake: it quietly drops zero-order customers, which both understates total customer count and inflates the reported average LTV (since the zero-revenue customers, who pull the average down, are simply missing).
• NTILE(3) forces exactly three equal-sized groups even against a distribution that isn't naturally three clusters, for example a handful of whale customers against a long tail of small buyers. For heavily skewed revenue, consider log-scale bucketing or business-defined dollar thresholds instead of forcing equal-sized groups.
• "Since signup" implies a specific time boundary. This worked example treats LTV as all-time cumulative revenue; a rolling or time-windowed variant (e.g., 90-day LTV) is a different, equally valid metric and should be labeled distinctly rather than silently substituted.
• Keep amounts in DECIMAL, not floating point, to avoid rounding drift accumulating across many small orders per customer.

How I would clean it without losing raw data

• Keep the original CSV untouched on a Raw sheet or, better, load it into Power Query as the source layer.
• Build a separate Clean output table for analysis.
• Never analyze directly from the raw import.

Cleaning steps

1. Dates: standardize them in Power Query or with helper columns so every date becomes a true Excel date.
2. Spaces and casing: use TRIM, CLEAN, and consistent case rules to normalize text.
3. Headers: unmerge cells in the header area, fill missing header labels, and promote a single clean header row.
4. Product names: create a mapping table for known variants, such as spelling differences or mixed casing, and convert them to one canonical name.
5. Structure: turn the cleaned output into an Excel Table so formulas and pivots can refresh reliably.

Why I prefer Power Query for this

• It preserves the source data.
• It makes the cleanup steps repeatable.
• It is easier to refresh when a new CSV arrives.

Example logic

• Raw product name: widget pro, Widget Pro, WIDGET PRO
• Cleaned product name: Widget Pro

If I had to do it manually, I would still keep the raw import separate, create a staging sheet, and only then publish the final analysis table. That protects the audit trail and makes the workbook easier to trust.

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.