Advanced SQL: Window Functions, CTEs, and Subqueries Questions

Approach
I would model the customer segment as a slowly changing dimension, meaning a history table with valid_from and valid_to timestamps. Then I would do an as-of join, which matches each order to the dimension row that was valid when the order happened, not the current row.

SELECT
  DATE_TRUNC('month', o.order_ts) AS month,
  d.segment,
  SUM(o.revenue) AS revenue
FROM orders o
JOIN dim_customer_history d
  ON o.customer_id = d.customer_id
 AND o.order_ts >= d.valid_from
 AND o.order_ts <  d.valid_to
GROUP BY 1, 2;

Why this is auditable

• The join is driven by the order timestamp, so reclassifying a customer today does not rewrite history.
• I would keep immutable raw orders, the dimension history, and the monthly mart as separate layers.
• I would store the load batch or snapshot date so finance can reproduce the exact closing report.

Late data handling
If an order is backfilled after month close, I would rerun the affected month from raw facts, but only against the dimension history that was effective at the order time. That keeps the numbers consistent and explainable during reconciliation.

One deterministic row per customer
Use ROW_NUMBER(), which assigns 1 to a single row after you specify a full tie-break order. ROW_NUMBER() is a window function, meaning it ranks rows inside each customer group without collapsing them.

WITH ranked AS (
  SELECT
    o.*,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_ts DESC, order_id DESC
    ) AS rn
  FROM orders o
)
SELECT *
FROM ranked
WHERE rn = 1;

The timestamp decides recency, and order_id DESC makes ties deterministic.

If business later wants every row tied for the latest timestamp
Switch to RANK() and filter on 1.

WITH ranked AS (
  SELECT
    o.*,
    RANK() OVER (
      PARTITION BY customer_id
      ORDER BY order_ts DESC
    ) AS rnk
  FROM orders o
)
SELECT *
FROM ranked
WHERE rnk = 1;

Example
If customer 7 has rows at 10:00, 10:00, and 09:30, the first query returns one row. The second returns both 10:00 rows.

A correlated subquery that counts rows from a related table silently overcounts when that table has duplicate rows for the correlation key, because COUNT(column) counts every row that matches, not every distinct value. If order_items has two rows for the same product on the same order (a duplicate import, a split line-item, a merged data source), a query counting "distinct products per order" using plain COUNT will count that duplicated product twice, inflating the total and mis-classifying orders that don't actually meet the threshold.

The bug

-- Wrong when order_items has duplicate rows for the same product
SELECT o.id, o.customer_id
FROM orders o
WHERE (
  SELECT COUNT(oi.product_id)     -- counts every row, including duplicates
  FROM order_items oi
  WHERE oi.order_id = o.id
) > 3;

If an order has products A, B, C but A appears as two separate rows in order_items, this subquery returns 4, and the order is wrongly flagged as having more than 3 distinct products when it actually has 3.

Fix 1: DISTINCT inside the subquery

SELECT o.id, o.customer_id
FROM orders o
WHERE (
  SELECT COUNT(DISTINCT oi.product_id)   -- counts unique products only
  FROM order_items oi
  WHERE oi.order_id = o.id
) > 3;

Fix 2: pre-aggregate with GROUP BY, then JOIN

SELECT o.id, o.customer_id
FROM orders o
JOIN (
  SELECT order_id, COUNT(DISTINCT product_id) AS unique_products
  FROM order_items
  GROUP BY order_id
) t ON t.order_id = o.id
WHERE t.unique_products > 3;

Key points

• COUNT(col) and COUNT(*) both count rows, not distinct values; COUNT(DISTINCT col) is the only one of the three that de-duplicates.
• Fix 1 is the minimal, correct patch and stays correlated, so it still conceptually re-runs the count per outer row.
• Fix 2 pre-aggregates order_items once with GROUP BY, before joining, so it's no longer correlated at all; the join replaces the per-row re-execution with a single aggregation pass plus a join.

Complexity

The correlated fix (Fix 1) still costs one grouped count per outer order, logically O(orders) × O(cost of counting that order's items); an index on order_items(order_id, product_id) keeps each individual count cheap, but the query still repeats work per order unless the optimizer rewrites it. Fix 2 aggregates order_items once, in a single GROUP BY pass over the whole table, then does one join back to orders, which is the same total amount of aggregation work done once instead of potentially once per order, and lets the engine choose a hash or merge join for the join step.

Worked example

Two orders: order 1 has three order_items rows for products A, B, C, but A is duplicated (two rows for the same product); order 2 has four genuinely distinct products D, E, F, G. The buggy query (COUNT without DISTINCT) returns both orders as having more than 3 products, because order 1's row count is 4 even though it only has 3 distinct products. Both fixes correctly return only order 2. (Verified by executing all three queries against SQLite 3.51 with this exact duplicate-row setup.)

Trade-offs and pitfalls

This bug class isn't limited to COUNT: any correlated aggregate that assumes one row per logical entity (a SUM of amounts, an AVG of prices) will silently misbehave the same way if the correlated table has duplicate rows for reasons unrelated to the business logic being modeled. The deeper fix is a data-quality one: if order_items shouldn't have duplicate (order_id, product_id) rows in the first place, add a unique constraint or de-duplicate upstream in the pipeline, rather than teaching every downstream query to defensively DISTINCT around a data problem. Between the two SQL-level fixes, prefer Fix 2 (pre-aggregate then join) once the query runs against a large table or gets reused in multiple places: it does the aggregation exactly once instead of repeating it per outer row, and it separates "what counts as duplicate" logic into one place instead of scattering DISTINCT across every query that touches order_items.

Direct answer: PERCENT_RANK() reports where a row sits by rank position, scaled to a 0-to-1 range; CUME_DIST() (cumulative distribution) reports what fraction of all rows have a value at or below the current row's, which is the more familiar meaning of "percentile." They diverge specifically on ties: CUME_DIST() gives every row tied at the same value the identical, correct fraction of the whole dataset at or below that value, while PERCENT_RANK() is derived from RANK()'s gapped position, so tied rows also share a value, but that value reflects rank position rather than the true proportion of the dataset at or below it. For "each user's spend percentile within their own country," where the intent is "what fraction of people in my country spend at or below what I spend," CUME_DIST() is the right function; PERCENT_RANK() would answer a related but different question about relative rank position instead.

Structured elaboration

$\text{PERCENT\_RANK} = \frac{\text{rank} - 1}{n - 1}$

$\text{CUME\_DIST} = \frac{\text{number of rows with value} \le \text{current row's value}}{n}$

where rank is the row's RANK() (tied rows share a rank, leaving gaps) and n is the partition's row count.

Worked example

Verified in DuckDB against t(id, score) = (1,50), (2,80), (3,80), ordered ascending:

SELECT id, score,
  PERCENT_RANK() OVER (ORDER BY score) AS pr,
  CUME_DIST()    OVER (ORDER BY score) AS cd
FROM t ORDER BY score;

For score 80 (rank 2 of 3, since RANK() on the ascending order gives 1, 2, 2): $\text{PERCENT\_RANK} = \frac{2-1}{3-1} = 0.5$. CUME_DIST for score 80 is $\frac{3}{3} = 1.0$, since all 3 rows, including both rows tied at 80, have a value at or below 80. PERCENT_RANK's 0.5 reads as "midway between the lowest and highest rank position"; CUME_DIST's 1.0 reads, correctly, as "100% of this dataset is at or below this score," which is the honest answer given that this row's value is in fact the maximum present.

Applying this to spend percentile within country

SELECT user_id, country, spend,
  CUME_DIST() OVER (PARTITION BY country ORDER BY spend) AS spend_percentile
FROM user_spend
ORDER BY country, spend;

Verified in DuckDB against user_spend(user_id, country, spend) with US rows at 50, 100, 200, 200 and FR rows at 100, 300: US spend percentiles come back 0.25, 0.5, 1.0, 1.0 (the two users tied at 200 both correctly show "100% of US users spend at or below me," since together they account for the top of the US distribution), and FR spend percentiles come back 0.5, 1.0.

Why CUME_DIST is the right choice here, not PERCENT_RANK: the business question, "what percentile is this user's spend at within their country," is asking for the true proportion of the country's population at or below that spend level, which is exactly CUME_DIST's definition. PERCENT_RANK would technically run and produce numbers in the same 0-to-1 range, but its value for the two tied US users at 200 would come from their shared, gapped rank rather than directly reflecting that 100% of the US population spends at or below them, making it a less faithful answer to the actual stakeholder question, even though both functions are syntactically interchangeable here.

Trade-offs & pitfalls

Both functions require the same underlying computation, one sort of the partition plus a single pass (no meaningful performance difference between them), so the choice is entirely about which definition matches the business question, never about cost. A common wrong turn is picking whichever function "sounds like percentile" without checking its tie behavior against a dataset that actually has ties (real spend or score data usually does); on data with no ties at all, PERCENT_RANK and CUME_DIST can look deceptively similar in a quick spot check, and the difference only surfaces once duplicate values appear, often well after the query has already shipped to a dashboard.

Direct answer: NTILE(10) divides a partition's rows into 10 groups as evenly as possible by row count, not by value; when the row count doesn't divide evenly by 10, the earliest buckets in sort order absorb the extra rows, one at a time, so bucket sizes differ by at most 1. On a small partition (fewer rows than buckets), every row gets its own bucket number in sequence and the remaining bucket numbers simply never appear in the output at all, which can look like "empty buckets" even though NTILE never actually emits a placeholder for a bucket with zero rows. When the goal is a true value-based percentile rather than an equal-row-count bucket, PERCENT_RANK() or CUME_DIST() are the right tools instead, since they express a row's standing as a continuous proportion rather than assigning it to one of a fixed number of buckets.

Approach

SELECT user_id, spend,
  NTILE(10) OVER (ORDER BY spend DESC) AS decile
FROM user_spend
ORDER BY decile, spend DESC;

Uneven group sizes: verified in DuckDB against 23 rows bucketed into 10 deciles: bucket sizes come back 3, 3, 3, 2, 2, 2, 2, 2, 2, 2 (the first 3 buckets get 3 rows each, the remaining 7 get 2 each: 3×3 + 7×2 = 9 + 14 = 23). NTILE computes this by taking the total row count, dividing by the bucket count, and distributing the remainder one extra row at a time to the earliest buckets in sort order; it never silently drops a row or leaves a bucket short by more than one row relative to any other.

Small-partition behavior: verified in DuckDB with only 3 rows and NTILE(10): every row gets a distinct bucket number, 1, 2, 3, and buckets 4 through 10 simply don't appear anywhere in the result set. This is not an error and not a bug: NTILE never assigns more buckets than there are rows to put in them, so on a partition smaller than the requested bucket count, the "extra" bucket numbers are just absent from the output. A report that expects to see all 10 decile labels represented (e.g., to plot a full histogram) needs to explicitly generate the missing labels with zero counts, since the query itself won't produce them.

Key points

• NTILE cares only about row position after sorting, never about the underlying value's magnitude; two rows with nearly identical spend can land in different deciles if they straddle a bucket boundary, and two rows with wildly different spend can land in the same decile if the partition is dense there.
• NTILE can also split identical values across adjacent buckets: with several users tied at the exact same spend value near a boundary, some of those tied rows can be assigned to one decile and the rest to the next, purely because of row position, not because their spend actually differs.
• With a partition smaller than the bucket count, missing bucket labels in the output are expected behavior, not a data quality problem; downstream reporting code that assumes every decile 1 through 10 always appears needs a defensive join against a generated list of bucket numbers.

When to reach for PERCENT_RANK or CUME_DIST instead

Both compute a continuous relative standing (a fraction between 0 and 1) rather than assigning a row to one of a fixed count of discrete buckets, which is the right tool when: the partition is too small for the requested bucket count to be meaningful (a 3-row partition has no real notion of "deciles"); the report needs to say "this user is in the top X%" as a precise, comparable number rather than "this user is in bucket 3 of 10"; or ties need to be reflected as an honest shared percentile rather than being arbitrarily split across neighboring buckets the way NTILE can split them.

Complexity
NTILE, PERCENT_RANK, and CUME_DIST all require the same underlying work: one sort of the partition by the ordering expression (O(n log n)), followed by a single pass that assigns each row's bucket number or percentile as a function of its position (O(n)). None of the three changes the asymptotic cost relative to the others; the choice between them is about what the output means, not about performance.

Edge cases

• Bucket count larger than the row count (the small-partition case above): handled gracefully by all engines tested here, producing fewer distinct bucket values than requested rather than an error.
• NULL values in the ordering column: sort to one end (engine-dependent NULLS FIRST/NULLS LAST default) and get assigned bucket numbers like any other row; decide explicitly whether NULL spend belongs in the bucketing at all, or should be filtered out before NTILE runs.
• Ties spanning a bucket boundary: as noted above, NTILE can split identical values between adjacent buckets; if that's unacceptable for the report, bucket on DENSE_RANK() of the distinct values instead of raw row position, so every tied row is guaranteed to land in the same bucket.

Trade-offs & pitfalls

The common wrong turn is treating NTILE's bucket number as a value-based percentile in downstream reporting ("decile 1 = top 10% by spend"), when it's actually an equal-row-count bucket that can have wildly different spend ranges from one bucket to the next on a skewed distribution; the top decile of a heavy-tailed spend distribution might span a huge dollar range while the bottom deciles are all tightly clustered near zero. If the report's real claim is about dollar-value percentiles rather than population deciles, PERCENT_RANK/CUME_DIST, or an explicit value-based quantile function, are the honest tools; NTILE answers a different, row-count-based question that only coincides with a value-based percentile when the underlying distribution happens to be roughly uniform.