Common Table Expression (CTE)

While working on a practice interview question, we came upon a problem that requires CTE. But what is CTE? CTE, also known as Common Table Expression is used for SQL(Structured Query Language). CTE is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, DELETE, or MERGE statement. CTEs are often used to perform multi-level aggregations.

For further information on the syntax for CTE please refer to this link. Below shows the example that we worked on.

Example From interviewquery.com

As you work the problem out from above you noticed that you would need a multi-level aggregation. We would need an aggregation to pull the users who converted and another aggregation to get the first sessions for the converted users. Below is the answer that we worked out.

WITH conversions AS (
    SELECT us.user_id
    FROM attribution AS a
    INNER JOIN user_sessions as us
         ON a.session_id = us.session_id
    WHERE conversion = 1
    GROUP BY 1),
first_session AS (
    SELECT conversions.user_id, min(us.created_at) AS min_created_at
    FROM user_sessions AS us
    INNER JOIN conversions
         ON us.user_id = conversions.user_id
    INNER JOIN attribution AS a
         ON a.session_id = us.session_id
    GROUP BY conversions.user_id
)
SELECT us.user_id, channel
FROM attribution AS a
JOIN user_sessions AS us
    ON a.session_id = us.session_id
JOIN first_session
    ON first_session.min_created_at = us.created_at
       AND first_session.user_id = us.user_id

Let’s break the solution up into parts so that we can understand what happened. The solution above uses two CTE conversions and first_session. The CTE conversions returns the column of user ids where conversion = 1 in other words the users that converted. On the other hand, the second CTE first_session returns one column of user ids and another column of the first session of the converted users.

And finally in the outer query, we return the user id and channels of the converted users’ first session and the corresponding channel of the first session. I hope this example helps you understand how to use CTE in your future SQL query. If you have any questions feel free to email me at andypeng93@gmail.com.