gistfile1.txt

  def calculate_response_time_per_message(start_date = 30.days.ago, end_date = Time.now)
    Message.connection.execute("
      SELECT q3.id, q3.customer_id, DATE(q3.created_at AT TIME ZONE '-08:00') AS date, EXTRACT(EPOCH FROM q3.created_at) AS created_at, EXTRACT(EPOCH FROM q3.response_time) AS response_time FROM
        (SELECT q2.*,
          CASE WHEN direction = 'received' THEN LEAD(created_at) OVER (PARTITION BY customer_id ORDER BY created_at) - created_at
               ELSE NULL
               END AS response_time FROM
          (SELECT q1.id, q1.customer_id, q1.created_at, q1.direction FROM
            (SELECT m.id, m.customer_id, m.created_at, m.direction, LAG(direction) OVER (PARTITION BY customer_id ORDER BY created_at) AS prev_direction FROM messages m
              WHERE company_id = #{self.id} AND via_api = false AND mass_message_id IS NULL AND created_at >= '#{start_date}' AND created_at <= '#{end_date}') AS q1
          WHERE (q1.direction = 'received' AND q1.prev_direction IS DISTINCT FROM 'received') OR (q1.direction = 'sent' AND q1.prev_direction = 'received')) AS q2
        ) AS q3
      WHERE q3.direction = 'received' AND q3.response_time IS NOT NULL ORDER BY created_at DESC
    ")
  end