case when solution

Question

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!

Answer 1

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;

case when solution

Submit an answer