Use the result of a sub-query outside of the sub-query

I have a table structured like this.

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.

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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

Method 2

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0