Intercom – Conversations per Hour of Day

Question

How are new conversations distributed on an hourly basis?

Schema

Intercom

Output

 

Intercom – conversations per hour of day

How new conversations are distributed on an hourly basis?

SQL Query

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

SELECT extract('hour'
               FROM created_at) AS hour,
       count(id) AS conversations
FROM conversations
WHERE created_at > CURRENT_TIMESTAMP - interval '28 days'
  AND conversation_message_author_type != 'admin'
GROUP BY 1

 

in Customer Support