gistfile1.txt

  def calculate_response_time_average_per_day
    Message.connection.execute("
      SELECT q4.date, AVG(q4.response_time / 60 / 60) FROM
        (SELECT EXTRACT(HOUR FROM q3.created_at AT TIME ZONE 'PST') AS date, 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 > '#{7.days.ago}') AS q1
            WHERE (q1.direction = 'received' AND q1.prev_direction IS DISTINCT FROM 'received' AND q1.created_at < '#{Time.zone.now}') OR (q1.direction = 'sent' AND q1.prev_direction = 'received')) AS q2
          ) AS q3
        WHERE q3.direction = 'received' AND q3.response_time IS NOT NULL) AS q4
      GROUP BY q4.date ORDER BY q4.date DESC
    ")
  end