## 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?

Intercom

### Output

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'
parts AS
( SELECT conversation_id,
created_at
FROM conversations_parts
WHERE part_type = 'comment'
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