ohyeah ohyeah - 9 months ago 39
SQL Question

How do I calculate the accumulative percentage?

I have a table that consists of a customer ID, and the number of hours it took to place an order since they first registered.

An example would be:

UserId | TimeToPay
2DD6ABBB-C9A4-4373-B188-312DB8222859 | 0
C7438620-6431-4C13-B335-AA1A3E314C58 | 55
6AG22103-62B0-47A0-BE3F-7AE1A7A4C3B7 | 30
300A2E02-0799-47BB-BF36-070706F98149 | 8
43382839-E897-4E5F-A955-C9DDAF9B424B | 0

In the above example, 2 customers have placed an order within an hour of ordering something, and after 55 hours, all customers have placed an order. This table does not contain customers that have not placed an order yet. I am trying to create a query that shows cumulative percentages of how many customers have placed an order in what timespan. So my prefered output would be:

Hours | PercentageOfCustomers
0 | 40
8 | 60
30 | 80
55 | 100

However, when I use answers like this or this one, I don't get cumulative percentages. How do I get my desired output?


You can use a windowed COUNT(*) to get a rolling total, and divide that by the number of total customers:

Select  Distinct TimeToPay As Hours, 
        ((Count(*) Over (Order By TimeToPay Asc) * 1.0) / 
            (Count(*) Over (Order By (Select Null)) * 1.0)) 
            * 100 As PercentageOfCustomers
From    Test
Order by Hours