Calculate a running total percent in SQL

In this previous post, I show how to calculate a running total in SQL using window functions. In the example, I calculated the running total of the daily amount of payments.

In this post, I want to get the daily running total over the month as well as the percentage of this running total over the month total.

Month	Amount	Running Total	Running Total Percent
1	1	1	5.9%
1	13	14	82.4%
1	1	15	88.2%
1	2	17	100.0%
2	4	4	26.7%
2	19	23	60.5%
2	15	38	100.0%
3	4	4	18.2%
3	18	22	100.0%

I create a CTE where I calculate the payments amount per day. I also add a column with the month using the data_part() function on the payment date.

WITH daily_amount AS (
    SELECT 
        payment_date::date
        , date_part('month', payment_date) AS month
        , SUM(amount) AS amount
    FROM 
        payment
    GROUP BY 
        payment_date::date
        , date_part('month', payment_date)
)

Then, from this CTE, I create another one that returns the monthly total and the running total over each month. To do so, I use a window function with a PARTITION BY month statement that partitions my data on the month. Because I partitioned my data, when I use the ROWS UNBOUNDED PRECEDING statement, only all the amounts from the beginning of the current month are added.

monthly_running_total AS (
SELECT 
    payment_date
    , month
    , amount
    , SUM(amount) OVER(PARTITION BY month) AS monthly_total
    , SUM(amount) OVER(PARTITION BY month ORDER BY payment_date ROWS UNBOUNDED PRECEDING) AS running_total
FROM
    daily_amount
)

Finally, I execute a SELECT statement on this CTE where I calculate the percentage of the running total over the month's total:

\(running\ total\ percent = \frac{running\ total}{month\ total}\times100\)

ROUND(running_total * 100 / monthly_total, 1) || '%' AS running_percent

Here's the final query:

WITH daily_amount AS (
    SELECT 
        payment_date::date
        , date_part('month', payment_date) AS month
        , SUM(amount) AS amount
    FROM 
        payment
    GROUP BY 
        payment_date::date
        , date_part('month', payment_date)
), monthly_running_total AS (
SELECT 
    payment_date
    , month
    , amount
    , SUM(amount) OVER(PARTITION BY month) AS monthly_total
    , SUM(amount) OVER(PARTITION BY month ORDER BY payment_date ROWS UNBOUNDED PRECEDING) AS running_total
FROM
    daily_amount
)
SELECT
    payment_date
    , month
    , amount
    , monthly_total
    , running_total
    , ROUND(running_total * 100 / monthly_total, 1) || '%' AS running_percent
FROM
    monthly_running_total
;

The returned results set looks like this:

 payment_date | month | amount  | monthly_total | running_total | running_percent 
--------------+-------+---------+---------------+---------------+-----------------
 2007-02-14   |     2 |  116.73 |       8351.84 |        116.73 | 1.4%
 2007-02-15   |     2 | 1188.92 |       8351.84 |       1305.65 | 15.6%
 2007-02-16   |     2 | 1154.18 |       8351.84 |       2459.83 | 29.5%
 2007-02-17   |     2 | 1188.17 |       8351.84 |       3648.00 | 43.7%
 2007-02-18   |     2 | 1275.98 |       8351.84 |       4923.98 | 59.0%
 2007-02-19   |     2 | 1290.90 |       8351.84 |       6214.88 | 74.4%
 2007-02-20   |     2 | 1219.09 |       8351.84 |       7433.97 | 89.0%
 2007-02-21   |     2 |  917.87 |       8351.84 |       8351.84 | 100.0%
 2007-03-01   |     3 | 2808.24 |      23886.56 |       2808.24 | 11.8%
 2007-03-02   |     3 | 2550.05 |      23886.56 |       5358.29 | 22.4%
 2007-03-16   |     3 |  299.28 |      23886.56 |       5657.57 | 23.7%
 2007-03-17   |     3 | 2442.16 |      23886.56 |       8099.73 | 33.9%
 2007-03-18   |     3 | 2701.76 |      23886.56 |      10801.49 | 45.2%
 2007-03-19   |     3 | 2617.69 |      23886.56 |      13419.18 | 56.2%
 2007-03-20   |     3 | 2669.89 |      23886.56 |      16089.07 | 67.4%
 2007-03-21   |     3 | 2868.27 |      23886.56 |      18957.34 | 79.4%
 2007-03-22   |     3 | 2586.79 |      23886.56 |      21544.13 | 90.2%
 2007-03-23   |     3 | 2342.43 |      23886.56 |      23886.56 | 100.0%
 2007-04-05   |     4 |  273.36 |      28559.46 |        273.36 | 1.0%
 2007-04-06   |     4 | 2077.14 |      28559.46 |       2350.50 | 8.2%
 2007-04-07   |     4 | 1984.28 |      28559.46 |       4334.78 | 15.2%
 2007-04-08   |     4 | 2227.84 |      28559.46 |       6562.62 | 23.0%
 2007-04-09   |     4 | 2067.86 |      28559.46 |       8630.48 | 30.2%
 2007-04-10   |     4 | 1973.18 |      28559.46 |      10603.66 | 37.1%
 2007-04-11   |     4 | 1940.32 |      28559.46 |      12543.98 | 43.9%
 2007-04-12   |     4 | 1930.48 |      28559.46 |      14474.46 | 50.7%
 2007-04-26   |     4 |  347.21 |      28559.46 |      14821.67 | 51.9%
 2007-04-27   |     4 | 2673.57 |      28559.46 |      17495.24 | 61.3%
 2007-04-28   |     4 | 2622.73 |      28559.46 |      20117.97 | 70.4%
 2007-04-29   |     4 | 2717.60 |      28559.46 |      22835.57 | 80.0%
 2007-04-30   |     4 | 5723.89 |      28559.46 |      28559.46 | 100.0%
 2007-05-14   |     5 |  514.18 |        514.18 |        514.18 | 100.0%
(32 rows)