Intercom – The median first response time

Question

What is the median time it takes for our team to make the first response to a new conversation?

Schema

Intercom

Output

Intercom – The median first response time

What is the median time it takes for our team to make the first response to a new conversation?

SQL Query

Important note 1: change the interval in the query from 28 to 7 or 90 to calculate the query for a different period of time.

WITH tickets AS
  ( SELECT id,
           created_at
   FROM conversations
   WHERE created_at > CURRENT_TIMESTAMP - interval '28 days'
     AND conversation_message_author_type != 'admin'),
     parts AS
  ( SELECT conversation_id,
           created_at
   FROM conversations_parts
   WHERE part_type = 'comment'
     AND author_type = 'admin'),
     min_resp_times AS
  ( SELECT min(parts.created_at) AS first_response,
           conversation_id
   FROM tickets
   JOIN parts ON (tickets.id = parts.conversation_id)
   GROUP BY parts.conversation_id)
SELECT median(extract(epoch
                      FROM first_response - created_at)/60) AS median
FROM min_resp_times
JOIN tickets ON (tickets.id = min_resp_times.conversation_id)

Important note 2: if you are using Amazon Redshift, the median aggregation function is implemented and can be used out of the box. If you are using a PostgreSQL instance  – i.e. at Amazon RDS PostgreSQL or Heroku PostgreSQL instance – then you will have to define your own median function as it is not officially implemented. The recommended implementation for such a function is the following:

CREATE FUNCTION median(anyarray) RETURNS float8 AS $$ WITH q AS
  ( SELECT val
   FROM unnest($1) val
   WHERE VAL IS NOT NULL
   ORDER BY 1 ),cnt AS
  ( SELECT COUNT(*) AS c
   FROM q )
SELECT AVG(val)::float8
FROM
  ( SELECT val
   FROM q LIMIT 2 - MOD(
                          (SELECT c
                           FROM cnt), 2)
   OFFSET GREATEST(CEIL(
                          (SELECT c
                           FROM cnt) / 2.0) - 1,0)) q2; $$ LANGUAGE SQL IMMUTABLE;


CREATE AGGREGATE median(anyelement) ( SFUNC=array_append,
                                      STYPE=anyarray,
                                            FINALFUNC=_median,
                                            INITCOND='{}');

Just execute the above function once on your database and the function will be registered and ready to be used. For more details you can also check here.

in Customer Support