SQL for Data Analysis Questions

Direct answer

Give every row a rank within its entity using ROW_NUMBER() OVER (PARTITION BY entity_id ORDER BY recency_column DESC), then keep only rn = 1. The recency column should be whatever proves the row is newest (an updated_at or response_date timestamp), with a deterministic tiebreaker (the row's own id) for the rare case of an exact tie.

Structured elaboration

• Why ROW_NUMBER() and not MAX()/GROUP BY: you need the whole latest row (every column), not just the max of one column. GROUP BY on the entity id alone can't return "the rest of the row that goes with the max timestamp" without a second join. ROW_NUMBER() over a subquery gets there in one pass.
• Tiebreaker: if two writes land at the exact same timestamp (a genuine retry duplicate, not a later correction), order by a secondary tiebreak column, an auto-increment id, so the result is deterministic rather than "whichever row the engine happens to return first."
• This pattern generalizes: user-profile records overwritten by a retried client call, survey responses where a user resubmitted, event streams where a later correction should supersede an earlier one. The shape, partition by entity, order by recency, keep rank 1, is identical across all of them.

Worked example

Example 1: user_profile_writes, a retried client call, tested in DuckDB 1.5:

Writes 2 and 3 share the identical timestamp, 2024-01-03 10:00:00, a genuine tie (a retried write that landed in the same second as the original), so updated_at alone cannot decide between them.

SELECT write_id, user_id, email, updated_at
FROM (
  SELECT
    write_id,
    user_id,
    email,
    updated_at,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC, write_id DESC) AS rn
  FROM user_profile_writes
) ranked
WHERE rn = 1
ORDER BY user_id;

Result:

User 101's writes 2 and 3 are tied on updated_at. write_id DESC is what breaks the tie here, it picks write 3 over write 2. Without it, ranking the two tied rows by updated_at DESC alone is not guaranteed to pick the same one every time, since SQL does not define an order among rows that compare equal on every ORDER BY column, re-running the query without the write_id tiebreak against the same data returned write 2 as rank 1 on one run in testing.

Example 2: survey_responses, a user resubmitting:

SELECT user_id, score, response_date
FROM (
  SELECT
    user_id,
    score,
    response_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY response_date DESC, response_id DESC) AS rn
  FROM survey_responses
) ranked
WHERE rn = 1
ORDER BY user_id;

Result:

User 201's later, higher score (9) correctly supersedes their earlier response (6). This one has no tie, response_date DESC alone already picks response 2; response_id DESC is only there as a safety net for a future tie.

Trade-offs & pitfalls

• Complexity is driven by the ORDER BY inside each partition; an index on (entity_id, recency_column) lets most engines satisfy the ranking without a separate sort step.
• DISTINCT ON (Postgres and DuckDB) or QUALIFY (Snowflake/DuckDB/BigQuery) express the same "keep 1 row per group" pattern more concisely than ROW_NUMBER() plus an outer filter, but neither is available in MySQL, SQLite, or BigQuery's DISTINCT ON form, so ROW_NUMBER() is the version that is portable across every mainstream SQL engine, worth knowing even where the shorter syntax is available.
• If "latest" needs to weigh more than one signal (prefer a manually-verified record over a newer but unverified one), ROW_NUMBER()'s ORDER BY can take multiple columns, but at that point write out explicitly why each column is in the tiebreak order. An unexplained multi-column ORDER BY is a common source of "why did it pick THIS row" confusion during review.
• rn = 1 must be filtered in an outer query or CTE, not in the same SELECT as the window function itself, a window function's result is not available to a WHERE clause at the same query level.

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.

Direct answer

NULL means "unknown," not a value, so it doesn't equal anything, not even another NULL. col = NULL always evaluates to NULL (treated as false in WHERE), which is why col != 'x' also silently drops rows where col is NULL: NULL != 'x' is NULL, not true. You have to test for it explicitly with IS NULL / IS NOT NULL. COALESCE(expr1, expr2, ...) returns the first non-NULL argument, commonly used to substitute a default.

Structured elaboration

SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN. Any comparison involving NULL (=, !=, <, >) evaluates to UNKNOWN, and a WHERE clause only keeps rows where the condition is TRUE, so UNKNOWN rows are silently dropped, exactly like FALSE rows, with no error.

Aggregates mostly ignore NULLs rather than propagating the UNKNOWN logic: SUM, AVG, MAX, MIN skip NULL values, and COUNT(column) counts only non-NULL values in that column, while COUNT(*) counts rows regardless of NULLs.

Worked example (sqlite3, verified)

Sample data:

CREATE TABLE users (user_id INTEGER PRIMARY KEY, email TEXT);
INSERT INTO users VALUES (1, 'alice@x.com'), (2, NULL), (3, 'carol@x.com');

CREATE TABLE payments (payment_id INTEGER PRIMARY KEY, user_id INTEGER, amount INTEGER);
INSERT INTO payments VALUES (1, 1, 100), (2, 1, NULL), (3, 2, 50), (4, 3, 75), (5, 3, NULL);

= NULL versus IS NULL:

SELECT COUNT(*) FROM users WHERE email = NULL;    -- 0 rows
SELECT COUNT(*) FROM users WHERE email IS NULL;    -- 1 row

Verified: 0 matches for = NULL, 1 match for IS NULL (user 2).

!= silently drops the NULL row:

SELECT user_id, email FROM users WHERE email != 'alice@x.com';

Result: only (3, 'carol@x.com'). User 2 (NULL email) is not "not equal to alice," it's UNKNOWN, so it never appears, even though intuitively a missing email is certainly different from a specific address.

COUNT(*) vs COUNT(column):

SELECT COUNT(*) AS row_count, COUNT(amount) AS non_null_amount_count, SUM(amount) AS total
FROM payments;

Result: (5, 3, 225). There are 5 payment rows, but only 3 have a non-NULL amount, and SUM adds only those 3 (100 + 50 + 75 = 225), silently ignoring the 2 NULLs rather than making the whole sum NULL.

COALESCE for defaults:

SELECT payment_id, amount, COALESCE(amount, 0) AS amount_or_zero FROM payments ORDER BY payment_id;

Result: payments 2 and 5 (NULL amount) show amount_or_zero = 0, everything else passes through unchanged.

NULL behavior after a LEFT JOIN (fan-out and double counting). Aggregating right after a multi-table LEFT JOIN is a common trap when a customer has more than one matching row on another joined table. Sample data:

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob');

CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER);
INSERT INTO orders VALUES (101, 1, 50), (102, 1, 30), (103, 2, 20);

CREATE TABLE refunds (refund_id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER);
INSERT INTO refunds VALUES (201, 1, 10), (202, 1, 5);

Alice has 2 orders and, separately, 2 refunds. Joining both to customers in one query multiplies her order rows by her refund count:

SELECT c.customer_id, COUNT(o.order_id) AS order_rows_seen, SUM(o.amount) AS sum_order_amount_wrong
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
LEFT JOIN refunds r ON r.customer_id = c.customer_id
GROUP BY c.customer_id;

Result: Alice shows order_rows_seen = 4 and sum_order_amount_wrong = 160, double the true values (2 orders, $80), because each of her 2 orders got paired with each of her 2 refunds (2x2 = 4 rows). Bob (1 order, 0 refunds, so no fan-out) correctly shows 1 and 20.

Fix, pre-aggregate orders before joining anything else:

WITH order_agg AS (
  SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS sum_order_amount
  FROM orders GROUP BY customer_id
)
SELECT c.customer_id, oa.order_count, oa.sum_order_amount
FROM customers c LEFT JOIN order_agg oa ON oa.customer_id = c.customer_id;

Result: Alice correctly shows (1, 2, 80). Note AVG(o.amount) in the broken version happened to still read 40.0, the same as the correct average, because Alice's duplication factor was uniform (every order duplicated exactly twice); that's a coincidence of this data, not something to rely on. COUNT and SUM are the numbers that reveal the bug reliably.

Trade-offs & pitfalls

• Never write = NULL or != NULL; both silently return zero rows and produce no error, which makes this bug easy to ship unnoticed.
• NOT IN (subquery) is a related NULL trap: if the subquery returns even one NULL, NOT IN returns no rows at all for every outer row, because comparing against a NULL element makes the whole NOT IN expression UNKNOWN. Prefer NOT EXISTS when the inner column can contain NULLs.
• When aggregating after joining multiple one-to-many relationships in a single query, pre-aggregate each one-to-many table separately (as in the fix above) before joining, or use COUNT(DISTINCT ...) as a partial guard, though DISTINCT alone won't fix a corrupted SUM.
• GROUP BY treats all NULLs as one group (they're considered equal for grouping purposes, unlike in a WHERE ... = NULL comparison), and DISTINCT does the same, collapsing multiple NULLs into a single NULL row.

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.

Direct answer

Build a date spine, one row per day covering the full window you want reported, then left join per-day aggregated event counts onto it, and pivot each event type into its own column with SUM(CASE WHEN event_name = 'x' THEN 1 ELSE 0 END). The spine is what guarantees a day with zero events still appears in the report as a row of zeros instead of silently vanishing.

Approach

WITH RECURSIVE days(day) AS (
  SELECT date('2026-07-13')
  UNION ALL
  SELECT date(day, '+1 day') FROM days WHERE day < date('2026-07-19')
),
agg AS (
  SELECT
    event_date,
    SUM(CASE WHEN event_name = 'view'        THEN 1 ELSE 0 END) AS views,
    SUM(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) AS add_to_carts,
    SUM(CASE WHEN event_name = 'purchase'    THEN 1 ELSE 0 END) AS purchases
  FROM events
  WHERE event_date BETWEEN '2026-07-13' AND '2026-07-19'
  GROUP BY event_date
)
SELECT
  d.day AS report_date,
  COALESCE(a.views, 0)        AS views,
  COALESCE(a.add_to_carts, 0) AS add_to_carts,
  COALESCE(a.purchases, 0)    AS purchases
FROM days d
LEFT JOIN agg a ON a.event_date = d.day
ORDER BY d.day;

This recursive CTE builds the day spine in passes: the first SELECT produces one base row (2026-07-13), and each following pass takes the previous pass's output row and builds the next day from it (date(day, '+1 day')), re-running against days's own growing result. It keeps adding rows until a pass's output fails the WHERE condition (day < date('2026-07-19')), at which point the recursion stops.

Key points

• The date spine (days) is generated independently of the events table, so it does not depend on data existing to produce a row; it is the fix for the classic "missing days silently disappear from the report" bug.
• LEFT JOIN from the spine (not from the events aggregate) preserves every day even when agg has no matching row; COALESCE(..., 0) then turns the resulting NULL from unmatched days into an explicit zero.
• WITH RECURSIVE builds the spine portably, and works unchanged across engines that support recursive CTEs (which is most of them).

Complexity

O(d) to build the spine for d days, O(n) to scan and aggregate the events in range, and O(d) for the final left join and pivot, so overall O(n + d), linear in the event count plus the (small, fixed) number of days in the window.

Edge cases

• A day with genuinely zero events: this is the case the spine exists to handle correctly, verified in the worked example below (2026-07-17 correctly reports zeros instead of being absent).
• event_name values that don't match view, add_to_cart, or purchase: they are still scanned and counted toward nothing (same three-valued-logic behavior as any CASE WHEN pivot), so a new event type added upstream needs a new WHEN branch here or it silently stops showing up.

Worked example (executed, SQLite)

Seed: events across 2026-07-14 through 2026-07-19, with 2026-07-17 deliberately left with zero events to test the spine.

('2026-07-13', 0, 0, 0)   <- no events seeded for this day either, correctly zero
('2026-07-14', 2, 1, 0)
('2026-07-15', 1, 0, 0)
('2026-07-16', 1, 1, 1)
('2026-07-17', 0, 0, 0)   <- the day with zero events; the spine kept the row
('2026-07-18', 1, 0, 1)
('2026-07-19', 2, 1, 0)
row_count=7

All 7 days appear exactly once, in order, with 2026-07-17 correctly showing as a zero row rather than being missing from the result set.

Trade-offs and pitfalls

• Dialect alternatives to the recursive CTE above: Postgres offers generate_series() (e.g. generate_series('2026-07-13'::date, '2026-07-19'::date, '1 day')). BigQuery offers GENERATE_DATE_ARRAY(start_date, end_date, INTERVAL 1 DAY). Snowflake uses the GENERATOR() table function combined with SEQ4()/ROW_NUMBER() and DATEADD(). All produce the same day spine as the recursive CTE, only the generation syntax changes.

This pattern does not scale to a fixed set of hand-written columns once there are hundreds of event types; at that point you either pivot dynamically outside SQL (build the CASE WHEN list programmatically from a lookup of known event types before running the query), or keep the data in long (one row per event type per day) format and let a BI tool do the wide pivot at render time instead of the database. A common wrong turn is left-joining the spine onto the aggregate the wrong direction (agg LEFT JOIN days instead of days LEFT JOIN agg), which silently undoes the whole point of building the spine, since it goes back to only showing days that already have at least one event.