How to get average time on page in PostHog - https://gradient.page example

posthog-avg-time.sql

/*
https://twitter.com/illyism/status/1689679677563006976
Shared for gradient.page: https://eu.posthog.com/shared/xTSSqCWgGVUnhselqI_YOoxGVPtlWQ

Modify it by settings your domain below
*/

SELECT 
  avg(time_on_page) AS avg_time_on_page,
  count(time_on_page) AS counts,
  current_url
FROM (
  SELECT
    dateDiff('minute', first_timestamp, next_timestamp) AS time_on_page,
    current_url
  FROM (
    SELECT 
      distinct_id,
      event AS first_event,
      timestamp AS first_timestamp,
      first_value(event) OVER w AS next_event,
      first_value(timestamp) OVER w AS next_timestamp,
      properties.$current_url as current_url
    FROM events
    WHERE 
      timestamp > toDateTime('2023-01-01 00:00:00') 
      AND (event = '$pageview' OR event = '$pageleave')
      AND properties.$current_url LIKE '%gradient.page%' /* change this to your domain */
       AND properties.$current_url NOT LIKE '%?%' /* removes query strings */
        AND properties.$current_url NOT LIKE '%#%' /* removes # hashes */
    WINDOW w AS (PARTITION BY distinct_id ORDER BY timestamp ASC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
    ORDER BY distinct_id, timestamp
  ) AS subquery
  WHERE first_event = '$pageview'
    AND (next_event = '$pageleave' OR next_event = '$pageview')
    AND time_on_page <= 30
)
GROUP BY current_url
ORDER BY counts DESC

/* Learn more: https://posthog.com/tutorials/time-on-page */

Use SQL to calculate time on page in PostHog
Showing users browsing for gradient wallpapers on https://gradient.page/

Learn more: https://posthog.com/tutorials/time-on-page