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 (
SELECT user_id,
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
FROM web_log
WHERE action = 'page_exit' OR action = 'page_load'),
durations AS (
SELECT user_id,
date,
MIN(exit_timestamp) - MAX(load_timestamp) AS duration
FROM sessions
GROUP BY 1,2)
SELECT user_id, AVG(duration) AS avg_duration
FROM durations
WHERE duration IS NOT NULL
GROUP BY 1
ORDER BY 2;