ohyeah ohyeah - 1 year ago 78
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?

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download