Why am I getting an invalid column reference 's' error with this query?

Question

Something is likely wrong with the inner join here, since the two queries I'm joining are fine if run separately, but I can't figure out what... :( I'm sorry for what's probably an easy question for most of you here!

I tried not referencing the s as users, but I still get the invalid column reference error...

SELECT time_spent_bucket, totalrev
FROM
(
SELECT session_aggregate.app_timespent AS time_spent_bucket, COUNT(*) AS users
FROM
    (
    SELECT session_info.s, 
    case when SUM(session_info.session_length)/60 > 200 then "200+" 
    when SUM(session_info.session_length)/60 >= 100 then "100 <-> 200" 
    when SUM(session_info.session_length)/60 >= 50 then "50 <-> 99"
    when SUM(session_info.session_length)/60 >= 20 then "20 <-> 49"
    when SUM(session_info.session_length)/60 >= 10 then "10 <-> 19"
    when SUM(session_info.session_length)/60 >= 5 then "5 <-> 9"
    else "<5" end AS app_timespent
    FROM
        (
        SELECT kt_session(calc_session.s, calc_session.evt_lst, 5) AS (s, session_number, session_length)
        FROM 
            (
            SELECT session_set.s, collect_set(session_set.timestamps) evt_lst
            FROM 
                (
                SELECT total_list.s, total_list.timestamps
                FROM 
                    (
                    SELECT s, utc_timestamp AS timestamps
                    FROM appl9_evt
                    WHERE month = 201512
                    and s is not null
                    UNION ALL
                    SELECT s, utc_timestamp AS timestamps
                    FROM appl9_evt
                    WHERE month = 201512
                    and s is not null
                    ) total_list 
                )session_set
            GROUP BY session_set.s
            ) calc_session 
        ORDER BY s,session_number DESC
        )session_info
    GROUP BY session_info.s
    )session_aggregate
GROUP BY session_aggregate.app_timespent
ORDER BY time_spent_bucket) ts
INNER JOIN
(
SELECT s, v
FROM appl9_mtu
WHERE month = "201507"
GROUP BY s, v
        ) totalrev
ON totalrev.s = ts.s

Answer 1

Your join references totalrev.s, but you aliased that column to 'users' in the totalrev subquery. Just change your join to reference the users column like:

ON totalrev.users = ts.s

You could also not alias the s column in the totalrev subquery.

In addition to the above, your ts subquery also does not have an 's' column, so there is no ts.s to join on. You need to include this in the ts subquery selection (and also the group by), something like:

SELECT session_aggregate.s, session_aggregate.app_timespent AS time_spent_bucket, COUNT(*) AS users
...
GROUP BY session_aggregate.s, session_aggregate.app_timespent