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]'
), csat AS (
  SELECT
    avg(support_score) * 100 AS support_score,
    'Overall' AS comments,
    '' AS url,
    now() as created_at
  FROM my_surveys
  UNION
  SELECT
    avg(s2.support_score) 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
  GROUP BY s.comments, s.ticket_id, s.created_at
)
SELECT
  support_score,
  comments,
  url
FROM csat
ORDER BY created_at desc;