posthog_bounce_rate.sql

SELECT 
    (COUNT(DISTINCT CASE 
        WHEN (raw_session_replay_events.click_count = 0 AND raw_session_replay_events.active_milliseconds < 60000) 
        THEN raw_session_replay_events.session_id 
        ELSE NULL 
    END) * 100.0) / COUNT(DISTINCT properties.$session_id) AS bounce_rate
FROM 
    events
INNER JOIN 
    raw_session_replay_events ON events.properties.$session_id = raw_session_replay_events.session_id
WHERE 
    created_at >= now() - INTERVAL 7 DAY 
    AND properties.$current_url = 'https://mediar.ai/'

grouped by day:

SELECT 
    toDate(created_at) AS day,
    (uniqIf(raw_session_replay_events.session_id, raw_session_replay_events.click_count = 0 AND raw_session_replay_events.active_milliseconds < 60000) * 100.0) / uniq(properties.$session_id) AS bounce_rate
FROM 
    events
INNER JOIN 
    raw_session_replay_events ON events.properties.$session_id = raw_session_replay_events.session_id
WHERE 
    created_at >= now() - toIntervalDay(7)
    AND properties.$current_url = 'https://mediar.ai/'
GROUP BY 
    toDate(created_at)
ORDER BY 
    day