0Pricing
SQL Interview Prep · Lesson

Cumulative Distribution and Percent of Total

Running percentages and share-of-total within partitions.

Cumulative Distribution and Percent of Total is a free SQL Interview Prep lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Percent-of-Total Question

A staple reporting interview ask: "What percentage of total revenue does each category represent?" and its cumulative cousin "What is the running share of total?"

The trick is dividing each row's value by a window aggregate computed over the whole partition. Knowing you can put a grand total in a window function, no self-join needed, is the insight being tested.

Window SUM Without ORDER BY = Grand Total

Here is the key move: SUM(amount) OVER () with an empty OVER and no ORDER BY returns the total of the entire result set, repeated on every row.

Because there is no ORDER BY, there is no running frame, so the default frame is the whole partition. That total-on-every-row is exactly the denominator you need for a percent of total.

SELECT
  category,
  amount,
  SUM(amount) OVER () AS grand_total
FROM category_sales;

Computing Percent of Total

Divide the row value by the windowed grand total and multiply by 100. Cast to a decimal so integer division does not truncate to zero.

This single-pass query replaces the old pattern of a subquery for the total joined back to the detail rows. It is shorter, faster, and reads cleanly.

SELECT
  category,
  amount,
  ROUND(
    100.0 * amount / SUM(amount) OVER (),
    2
  ) AS pct_of_total
FROM category_sales;

The Integer Division Trap

A classic interview gotcha: in many databases amount / total on integer columns does integer division, so a result less than 1 becomes 0.

Fix it by multiplying by 100.0 (a numeric literal) first, or by casting one operand: amount::numeric / total. Forgetting this returns a column of zeros, which interviewers spot instantly.

SELECT
  category,
  amount * 1.0 / SUM(amount) OVER () AS share,
  CAST(amount AS DECIMAL) / SUM(amount) OVER () AS share_alt
FROM category_sales;

Percent of Total Within a Group

Add PARTITION BY to make each row's share relative to its group rather than the whole table. For example, each product's percentage of its own region's sales.

The denominator SUM(amount) OVER (PARTITION BY region) now resets per region, so the percentages within each region sum to 100.

SELECT
  region,
  product,
  amount,
  ROUND(
    100.0 * amount / SUM(amount) OVER (PARTITION BY region),
    2
  ) AS pct_of_region
FROM regional_sales;

Running Percent of Total

Combine a cumulative numerator with a fixed denominator to get a running share of total: how much of the grand total has accumulated by each row.

The numerator uses ORDER BY (cumulative), the denominator uses an empty OVER () (grand total). The last row always reaches 100%.

SELECT
  sale_date,
  amount,
  ROUND(
    100.0 * SUM(amount) OVER (ORDER BY sale_date)
          / SUM(amount) OVER (),
    2
  ) AS running_pct
FROM daily_sales;

CUME_DIST: Cumulative Distribution

SQL has a built-in for cumulative distribution: CUME_DIST(). It returns the fraction of rows with an ORDER BY value less than or equal to the current row, a number in (0, 1].

Unlike a manual running share of an amount, CUME_DIST is about row position, answering "what proportion of rows fall at or below this value?" Useful for percentile-style reporting.

SELECT
  score,
  CUME_DIST() OVER (ORDER BY score) AS cume_dist
FROM exam_results;

PERCENT_RANK and Its Difference

A close relative is PERCENT_RANK(), defined as (rank - 1) / (total_rows - 1), ranging from 0 to 1.

The interview distinction: CUME_DIST includes the current row in its numerator ("at or below"), while PERCENT_RANK is relative rank starting at 0 for the first row. They give different values, and confusing them is a common slip.

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

Pareto / 80-20 Analysis

Running percent of total powers Pareto analysis: "which top customers make up 80% of revenue?" Sort descending by amount, compute the running share, then filter where the cumulative share first crosses 80%.

Because window results cannot go in WHERE, wrap the calculation in a CTE and filter in the outer query, the same rule as every window function.

WITH ranked AS (
  SELECT
    customer_id,
    revenue,
    SUM(revenue) OVER (ORDER BY revenue DESC)
      / SUM(revenue) OVER () AS running_share
  FROM customer_revenue
)
SELECT *
FROM ranked
WHERE running_share <= 0.80;

Rounding and Reconciliation

Watch out: rounding each percentage to 2 decimals can make the column sum to 99.99 or 100.01 instead of exactly 100. Interviewers may ask how you guarantee the parts sum to the whole.

Common answers: round only for display, keep full precision for math, or apply a largest-remainder adjustment to one row. Naming the issue matters more than the fix.

Interview Summary Points

Key takeaways to verbalize:

  • SUM(x) OVER () with no ORDER BY = grand total on every row.
  • Multiply by 100.0 to dodge integer division.
  • PARTITION BY for per-group shares.
  • Cumulative numerator over a grand-total denominator = running share.
  • CUME_DIST and PERCENT_RANK for distribution; know how they differ.
  • Wrap in a CTE for Pareto/threshold filtering.

Quick Check

How do you get the grand total of the whole result set on every row?

Recap: Distribution and Percent of Total

Percent of total divides a row value by SUM(x) OVER (), the grand total returned on every row; always multiply by 100.0 to avoid integer division and add PARTITION BY for per-group shares. A cumulative numerator over a grand-total denominator yields a running share that ends at 100%, the basis of Pareto analysis.

For position-based distribution, reach for CUME_DIST and PERCENT_RANK, and remember the rounding-reconciliation caveat. That completes running totals and moving averages.

Frequently asked questions

Is the “Cumulative Distribution and Percent of Total” lesson free?

Yes — the full text of “Cumulative Distribution and Percent of Total” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Cumulative Distribution and Percent of Total”?

Running percentages and share-of-total within partitions. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Cumulative Distribution and Percent of Total” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Cumulative Sums With Window Frames
  2. ROWS vs RANGE Framing
  3. Moving Averages Over a Sliding Window
  4. Cumulative Distribution and Percent of Total
← Back to SQL Interview Prep