Untitled
--решение 2, от AY ;WITH activity_aggr AS ( SELECT client_id, "action", hitdatetime, CAST(DATE_TRUNC('Month', hitdatetime) AS DATE) AS "month", LAG("action") OVER (PARTITION BY client_id ORDER BY hitdatetime) AS prev_action FROM user_activity_log ) SELECT client_id, month, SUM( CASE WHEN ("action" = 'login') AND (prev_action = 'visit') THEN 1 ELSE 0 END ) AS visit_to_login_events FROM activity_aggr GROUP BY client_id, month; --решение 1 SELECT client_id, CAST(DATE_TRUNC('Month',hitdatetime) as date) "month", COUNT(CASE WHEN ("action" = 'login') AND (prev_action = 'visit') THEN 1 END) visit_to_login_events FROM ( SELECT client_id, "action", hitdatetime, LAG("action") OVER (PARTITION BY client_id ORDER BY hitdatetime) prev_action FROM user_activity_log )t GROUP BY client_id, CAST(DATE_TRUNC('Month',hitdatetime) as date);
Leave a Comment