payment_date |amount |percentage -------------|--------|---------- 2005-05-24 |29.92 |0.04 2005-05-25 |573.63 |0.90 2005-05-26 |754.26 |2.01 2005-05-27 |685.33 |3.03 2005-05-28 |804.04 |4.22 2005-05-29 |648.46 |5.19 2005-05-30 |628.42 |6.12 2005-05-31 |700.37 |7.16 ... 2005-08-18 |2710.79 |79.59 2005-08-19 |2615.72 |83.47 2005-08-20 |2723.76 |87.51 2005-08-21 |2809.41 |91.67 2005-08-22 |2576.74 |95.49 2005-08-23 |2523.01 |99.24 2005-08-24 |514.18 |100.00In other words, at the beginning of our timeline, we’ve made 0% revenue, and then that percentage increases over time, until we reach 100% of our revenue at the end of our timeline.

### How to do it?

We’re going to do it in two steps. Our`PAYMENT`

table has a `PAYMENT_DATE`

column, which is really a timestamp, i.e. the exact amount in time when we received a payment. We can query the table to see its data (I will be using PostgreSQL syntax in this post):
```
SELECT
payment_date,
amount
FROM payment
ORDER BY payment_date;
```

payment_date |amount --------------------|------ 2005-05-24 22:53:30 |2.99 2005-05-24 22:54:33 |2.99 2005-05-24 23:03:39 |3.99 2005-05-24 23:04:41 |4.99 2005-05-24 23:05:21 |6.99 2005-05-24 23:08:07 |0.99 2005-05-24 23:11:53 |1.99 2005-05-24 23:31:46 |4.99 2005-05-25 00:00:40 |4.99 2005-05-25 00:02:21 |5.99 2005-05-25 00:09:02 |8.99 2005-05-25 00:19:27 |4.99 2005-05-25 00:22:55 |6.99 ...Now we could calculate that percentage on this timeline, but that wouldn’t be terribly interesting. We’re interested in the cumulative revenue

*per date*, so let’s run a classic

`GROUP BY`

:
```
SELECT
CAST(payment_date AS DATE),
sum(amount) AS amount
FROM payment
GROUP BY CAST(payment_date AS DATE)
ORDER BY CAST(payment_date AS DATE);
```

payment_date |amount -------------|------- 2005-05-24 |29.92 2005-05-25 |573.63 2005-05-26 |754.26 2005-05-27 |685.33 2005-05-28 |804.04 2005-05-29 |648.46 2005-05-30 |628.42 2005-05-31 |700.37 ... 2005-08-18 |2710.79 2005-08-19 |2615.72 2005-08-20 |2723.76 2005-08-21 |2809.41 2005-08-22 |2576.74 2005-08-23 |2523.01 2005-08-24 |514.18Now about that percentage. The formula in pseudo SQL is this:

cumulative_percentage[N] = SUM(amount[M <= N]) / SUM(amount[any])In other words, the percentage of the revenue we’ve made up until a given day is equal to the SUM of all amounts until that day divided by the SUM of all amounts. We could do that relatively easily in Microsoft Excel. But we can also do it with SQL, using window functions. The syntax is:

```
-- Sum of all amounts until that day:
SUM(amount) OVER (ORDER BY payment_date)
-- Sum of all amounts
SUM(amount) OVER ()
```

`GROUP BY`

statement in a derived table:
```
SELECT
payment_date,
amount,
CAST(100 * sum(amount) OVER (ORDER BY payment_date)
/ sum(amount) OVER () AS numeric(10, 2)) percentage
FROM (
SELECT
CAST(payment_date AS DATE),
sum(amount) AS amount
FROM payment
GROUP BY CAST(payment_date AS DATE)
) p
ORDER BY payment_date;
```

payment_date |amount |percentage -------------|--------|---------- 2005-05-24 |29.92 |0.04 2005-05-25 |573.63 |0.90 2005-05-26 |754.26 |2.01 2005-05-27 |685.33 |3.03 2005-05-28 |804.04 |4.22 2005-05-29 |648.46 |5.19 2005-05-30 |628.42 |6.12 2005-05-31 |700.37 |7.16 ... 2005-08-18 |2710.79 |79.59 2005-08-19 |2615.72 |83.47 2005-08-20 |2723.76 |87.51 2005-08-21 |2809.41 |91.67 2005-08-22 |2576.74 |95.49 2005-08-23 |2523.01 |99.24 2005-08-24 |514.18 |100.00

### Bonus: Nest aggregate functions in window functions

Because of the nature of SQL syntax, and the fact that both`GROUP BY`

and aggregate functions “happen before” window functions, i.e. they are calculated logically before window functions, we can nest aggregate functions in window functions.
This definitely doesn’t drastically improve readability, especially if you are not used to writing window functions every day. But in some more complex cases, it might help to shorten your SQL syntax. The above query is equivalent to this one:
```
SELECT
CAST(payment_date AS DATE) AS payment_date,
sum(amount) AS amount,
CAST(100 * sum(sum(amount)) OVER (
ORDER BY CAST(payment_date AS DATE))
/ sum(sum(amount)) OVER () AS numeric(10, 2)) percentage
FROM payment
GROUP BY CAST(payment_date AS DATE)
ORDER BY CAST(payment_date AS DATE);
```

`sum(sum(amount)) OVER ()`

syntax. If you cannot decipher this, don’t worry. You’re not alone. I invite you to review the following post on the order of SQL operations, first.