gistfile1.txt

  def calculate_response_time
    res = Message.connection.execute("
      SELECT stddev(cast(q3.response_time as float)) AS average, DATE(created_at AT TIME ZONE '-08:00') AS day FROM
        (SELECT q2.* FROM
          (SELECT q1.*, (LEAD(created_at) OVER (PARTITION BY customer_id ORDER BY created_at) - q1.created_at) AS response_time FROM
            (SELECT m.*, LEAD(direction) OVER (PARTITION BY customer_id ORDER BY created_at) AS next_direction FROM messages m
              WHERE company_id = #{self.id} AND via_api = false AND mass_message_id IS NULL) AS q1
          WHERE q1.direction <> q1.next_direction AND q1.next_direction IS NOT NULL) AS q2
        WHERE q2.direction = 'received') AS q3
      GROUP BY DATE(created_at AT TIME ZONE '-08:00')
      ")
  end