I have three columns, id, opening_date and termination_date.
I want to write a query that performs this:
total number of terminated up until a specific date ÷ total number of opened up until that same specific date
This would give me the fraction of the total opened that have been terminated at that point in time.
I want to calculate this over the span of every date from the first opening until present day.
I have realized that in order to do this, I will have to divide two running totals (the running total of every store ever terminated ÷ the running total of every store ever opened), which has proven to be quite hard.
Here is the code I have gotten so far
The resulting table looks as follows, where the running total of total is just 1,2,3,4,5 .... (which is not correct/what I want) and the resulting fraction is also not correct. The running sum of the terminated stores is correct however.
I have been struggling trying to solve this for a while, thank you for your help!
Don't count ids. As you don't want to count the nulls as terminated use the date column instead. You also need to specify that nulls are ordered first:
sum(count(churn_date))
over (order by churn_date nulls first
range between unbounded preceding and current row
) * 1.00 as terminated_stores
You'll want to avoid integer division so convert the numerator to a decimal value prior to computing the percentage. I changed to range between so it will count all store opens (closings) with the same timestamp together. And you do have to avoid divide by zero as well:
sum(count(close_dt)) over (
order by close_dt asc nulls first
range between unbounded preceding and current row
) * 1.00 /
nullif( sum(count(open_dt)) over (
order by open_dt asc
range between unbounded preceding and current row
), 0) as pct
See demo here: http://sqlfiddle.com/#!17/48fa62/23