SELECT d, d_lag, COUNT(1) AS cnt
FROM
(
  SELECT
    td_client_id, d,
    LAG(d, 1) OVER (PARTITION BY td_client_id ORDER BY d) AS d_lag
  FROM
  (
    SELECT td_client_id, TD_TIME_FORMAT(time,'yyyy-MM-dd', 'JST') AS d
    FROM access_log
    WHERE TD_TIME_RANGE(time,'2017-03-01','2017-04-02','JST')
    GROUP BY td_client_id, TD_TIME_FORMAT(time,'yyyy-MM-dd', 'JST')
  ) t1
) t
GROUP BY d, d_lag
HAVING d_lag IS NOT NULL AND d = '2017-04-01'
ORDER BY d DESC, d_lag DESC