Below is an example showing two customer accounts and their transaction information. I'm trying to calculate the number of days retained from when they started their subscription (startdate), to when they stopped their subscription (StopDate).
The trouble I have is that many times the customer will switch from a trial subscription (.99) to a full price subscription. When that happens, a new record is created for the new product but the original start date is not part of that new record.
For example, account "A2E7JBVBLTUSTHF" started a trial subscription on 6/14/13 (TransID = 100931889). Their subscription rolled into a full price subscription then subsequently stopped on 11/11/2013. For this customer, they remained active for 150 days (6/14/2013 to 11/11/2013).
Account "A2GC4RIYWEHKUHK" had a subscription start on 9/27/2014. Their subscription rolled into 2 more subscriptions and eventually ended on 7/19/2016. 661 days retained.
My question is, what is the best way to get days retained when the start date is not always associated with the record that holds the stop date. I've tried self joins, lead() and lag() functions all to no avail.
Any suggestions or help would be very much appreciated!