SELECT td_title, AVG(browsing_time) AS avg_browsing_time, COUNT(1) AS pv
FROM
(
  SELECT td_title, 1.0*diff/60 AS browsing_time
  FROM
  (

    SELECT
      td_title,
      LEAD(time) OVER (PARTITION BY td_client_id ORDER BY time) - time AS diff
    FROM access_log
    WHERE TD_TIME_RANGE(time,'2017-03-01','2017-04-01','JST')
    ORDER BY time
  ) t1
  WHERE 1.0*diff/60 <= 30 -- 30分以上の閲覧時間はセッション切れとして除外 --
) t
GROUP BY td_title
HAVING 100 <= COUNT(1) -- 100レコード以上アクセスのあったページに限定 --
ORDER BY avg_browsing_time DESC
LIMIT 10