psql.edit.11532.sql

WITH my_surveys AS (
  SELECT
    surveys.*,
    rank() OVER (ORDER BY surveys.created_at desc)
  FROM surveys
  JOIN comments
  ON comments.ticket_id = surveys.ticket_id
  WHERE user_cache IS NOT NULL
    AND user_cache->>'email' = '[email protected]'
)
SELECT
  avg(s2.support_score * (s2.rank - s.rank) / 210.0) * 100 AS support_score,
  s.comments,
  'https://support.heroku.com/tickets/' || s.ticket_id::text AS url,
  s.created_at
FROM my_surveys s
JOIN my_surveys s2
  ON s2.rank >= s.rank
 AND s2.rank < s.rank + 20
GROUP BY
  s.comments,
  s.ticket_id,
  s.created_at
ORDER BY s.created_at desc
;