gistfile1.txt

  def calc
    res = Message.connection.execute("
      SELECT q3.* FROM
        (SELECT q2.*, (LEAD(created_at) OVER (PARTITION BY customer_id ORDER BY created_at) - q2.created_at) AS response_time FROM
          (SELECT q1.* 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
          ) AS q3
        WHERE q3.direction = 'received' AND q3.response_time IS NOT NULL) AS q4
      GROUP BY DATE(created_at AT TIME ZONE '-08:00'
    ")
  end