Intercom – number of new conversations per day

Question

How many new conversations were created per day in the last: 7, 28 & 90 days?

Schema

Intercom

Output

Intercom – number of new conversations per day

How many new conversations were created per day in the last: 7, 28 or 90 days?

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 date_trunc('day', created_at) AS date,
       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