I have a table structured like this.
User_id | Subscription_type | timestamp |
---|---|---|
100 | PAYING | 2/10/2021 |
99 | TRIAL | 2/10/2021 |
100 | TRIAL | 15/9/2021 |
I want my output to be the same, with an additional column pulling the trial start date when the subscriber converts to a paying subscription.
User_id | Subscription_type | timestamp | Trial_Start_date |
---|---|---|---|
100 | PAYING | 2/10/2021 | 15/9/2021 |
99 | TRIAL | 2/10/2021 | |
100 | TRIAL | 2/10/2021 |
At the moment, I have this query:
SELECT *,
CASE WHEN
(SELECT `subscription_type` FROM subscription_event se1
WHERE se1.`timestamp` < se.`timestamp` AND se1.user_id = se.user_id
ORDER BY user_id DESC LIMIT 1) = 'TRIAL'
then se1.`timestamp` else 0 end as "Converted_from_TRIAL"
FROM subscription_event se
I have an error message with se1.timestamp
not been defined. I understand why, but I cannot see a workaround.
Any pointer?
CodePudding user response:
If you need to get two values out of the subquery, you have to join with it, not use it as an expression.
SELECT se.*,
MAX(se1.timestamp) AS Converted_from_TRIAL
FROM subscription_event AS se
LEFT JOIN subscription_event AS se1 ON se.user_id = se1.user_id AND se1.timestamp < se.timestamp AND se1.subscription_type = 'TRIAL'
GROUP BY se.user_id, se.subscription_type, se.timestamp
CodePudding user response:
Thanks a lot! For some reasons I needed to declare explicitely in SELECT the variables used in the GROUP BY . Not sure why ( I am using MySQL5.7 so maybe it is linked with that). In any case, this is the working query.
SELECT se.user_id, se.subscription_type, se.timestamp,
MAX(se1.timestamp) AS Converted_from_TRIAL
FROM subscription_event AS se
LEFT JOIN subscription_event AS se1 ON se.user_id = se1.user_id AND se1.timestamp < se.timestamp AND se1.subscription_type = 'TRIAL'
GROUP BY se.user_id, se.subscription_type, se.timestamp