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);