case when solution
Can someone provide the case when solution that tina suggested? I tried to implement that but it always have an error asking me to add action to group by if I want to take the min or max from the timestamp based on action. Trying to figure out the solution here. Thanks!
Here's my working solution, not sure it's optimal but would love to get feedbacks or others' solutions.
WITH sessions AS (
DATE_TRUNC('day', timestamp) AS date,
CASE WHEN action = 'page_exit' THEN timestamp END AS exit_timestamp,
CASE WHEN action = 'page_load' THEN timestamp END AS load_timestamp
WHERE action = 'page_exit' OR action = 'page_load'),
durations AS (
MIN(exit_timestamp) - MAX(load_timestamp) AS duration
GROUP BY 1,2)
SELECT user_id, AVG(duration) AS avg_duration
WHERE duration IS NOT NULL
GROUP BY 1
ORDER BY 2;