I have a dataset that looks like this
listener_id
product_type
feature_code
day
123
subscription
pandora_plus
1/1/2016
345
trial_subscription
pandora_plus
1/1/2016
678
registered
ad_supported
1/1/2016
1230
subscription
pandora_premium
1/1/2016
123
subscription
pandora_plus
1/2/2016
345
trial_subscription
pandora_plus
1/2/2016
678
trial_subscription
pandora_plus
1/2/2016
1230
subscription
pandora_premium
1/2/2016
123
registered
ad_supported
1/3/2016
345
trial_subscription
pandora_plus
1/3/2016
678
trial_subscription
pandora_plus
1/3/2016
1230
subscription
pandora_premium
1/3/2016
Few clarification on the data
1.A listener can only appear once per day in this table.
2. Having a product_type of subscription indicates the listener is a subscriber.
3. A new subscriber would be one who did not have product_type of subscription the day before.
I'm trying to answer the following questions
1. Count of new subscribers that start paying each day.
2. Count of times a listener shows up as a new subscriber in the year
I was thinking of joining product_type of each user from previous day for all 365 days of the year and confirm whether they are a new subscriber or not. Wondering if the self join would be the most efficient way to go about. Any help appreciated.
