Temp table-based approach to calculate the number of clicks, logins, and purchases per user session for active users is shown below.Ĭreate temporary table purchasingUsers DISTKEY(userId) as (Ĭreate temporary table movingUsers DISTKEY(userId) as (Ĭreate temporary table userSessionMetrics DISTKEY(userId) as ( You can see that the query plan is very similar to the CTE approach. While simultaneously filtering clickstream data to generate purchasingUsers.Calculate movingUsers (the IN subquery) and userSessionMetrics in parallel and join them.Finally, join the datasets from the above 2 points.Ĭalculating the number of clicks, logins, and purchases per user session for active users using subquery and derived tables is shown below.While simultaneously aggregating clickstream data to generate userSessionMetrics.Calculate movingUsers and purchasingUsers in parallel and join them. We can see the query plan by running explain + the above query in your sql terminal.įrom the query plan, we can see that the query planner decided to Join purchasingUsers as pu on usm.userId = pu.userId Join movingUsers as mu on usm.userId = mu.userId You can write a query that uses a subquery ( where userId in (.)) as shown below.Ĭase when actionType = 'buy' then 1 else 0 endĬase when actionType = 'click' then 1 else 0 endĬase when actionType = 'login' then 1 else 0 end Active users are defined as users who have been in multiple locations (identified by zip code) and have purchased at least one product. Let’s assume we want to calculate the number of purchases made by active users. Insert into geolocation(userId, zipCode, datetimeCreated ) Insert into clickstream(eventId, userId, sessionId, actionType, datetimeCreated )
0 Comments
Leave a Reply. |