mberman84

{"average"=>"30 days 24:07:39.67514", "day"=>"2014-04-09"}
{"average"=>nil, "day"=>"2014-04-10"}
{"average"=>"18 days 11:59:25.252155", "day"=>"2014-04-11"}
{"average"=>"52 days 20:15:16.493404", "day"=>"2014-04-12"}
{"average"=>"1 day 13:53:25.881951", "day"=>"2014-04-13"}
{"average"=>"21 days 26:31:47.568003", "day"=>"2014-04-14"}
{"average"=>"20 days 09:51:19.84725", "day"=>"2014-04-15"}
{"average"=>"25 days 25:40:46.853917", "day"=>"2014-04-16"}
{"average"=>"23 days 23:41:32.584254", "day"=>"2014-04-17"}
{"average"=>"23 days 13:32:31.002631", "day"=>"2014-04-18"}

mberman84

{"average"=>"03:29:36.622436", "day"=>"2014-10-25"}
{"average"=>"19:18:16.957437", "day"=>"2015-01-11"}
{"average"=>"7 days 07:39:57.416137", "day"=>"2014-09-20"}
{"average"=>"12:27:04.902307", "day"=>"2014-11-13"}
{"average"=>"2 days 26:33:49.611325", "day"=>"2014-09-22"}
{"average"=>"4 days 26:24:29.228502", "day"=>"2014-09-21"}
{"average"=>"12:29:43.323986", "day"=>"2014-10-17"}
{"average"=>"21:08:21.888295", "day"=>"2014-10-04"}
{"average"=>"7 days 23:22:35.682252", "day"=>"2014-09-13"}
{"average"=>"6 days 10:23:57.00454", "day"=>"2014-09-01"}

mberman84

  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')

mberman84

  def calculate_response_time
    res = Message.connection.execute("
      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 = 2 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
      ")
  end

mberman84

  def calculate_response_time
    res = Message.connection.execute("
      SELECT DATE(q2.created_at AT TIME ZONE '-08:00'), q2.response_time AS q3 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'
    ")
  end

mberman84

 [{"date"=>"2014-07-28", "response_time"=>nil, "id"=>"108047"},
 {"date"=>"2014-08-14", "response_time"=>nil, "id"=>"140694"},
 {"date"=>"2014-08-25", "response_time"=>"00:01:46.624461", "id"=>"174302"},
 {"date"=>"2014-08-25", "response_time"=>"2 days 00:10:45.687226", "id"=>"174317"},
 {"date"=>"2014-08-25", "response_time"=>"2 days 00:08:56.161418", "id"=>"174363"},
 {"date"=>"2014-08-25", "response_time"=>nil, "id"=>"174369"},
 {"date"=>"2014-08-25", "response_time"=>"00:00:33.883226", "id"=>"174390"},
 {"date"=>"2014-08-25", "response_time"=>"00:02:14.670211", "id"=>"174415"},
 {"date"=>"2014-08-25", "response_time"=>"2 days 00:07:44.033353", "id"=>"174418"},
 {"date"=>"2014-08-25", "response_time"=>"00:00:50.339513", "id"=>"174428"},

mberman84

 [#<Message id: 173694, user_id: 35, customer_id: 25603, text: "Hey Amie - this is Sieva (StudySoup). I’ll be text...", direction: "sent", created_at: "2014-08-25 17:24:41", updated_at: "2014-08-25 17:24:41", viewed: true, company_id: 13, via_api: false, error_description: nil, to_phone_number: nil, from_phone_number: nil, media_url: nil, media_content_type: nil, sent_at: nil, source_uuid: nil, mass_message_id: nil, sequence_id: 0, via_auto_reply: false>,
 #<Message id: 176002, user_id: nil, customer_id: 25603, text: "Hey Sieva! Im sorry I missed the phone call meetin...", direction: "received", created_at: "2014-08-25 17:56:13", updated_at: "2014-08-25 17:56:13", viewed: true, company_id: 13, via_api: false, error_description: nil, to_phone_number: nil, from_phone_number: nil, media_url: nil, media_content_type: nil, sent_at: nil, source_uuid: nil, mass_message_id: nil, sequence_id: 0, via_auto_reply: false>,
 #<Message id: 176128, user_id: 35, customer_id: 25603, text: "Hitup Fernando on Slack right now, an

mberman84

  def calculate_response_time
    res = Message.connection.execute("
      SELECT DATE(q2.created_at AT TIME ZONE '-08:00'), q2.response_time, q2.id, q2.customer_id 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' ORDER BY created_at
    ")
  end

mberman84

 {"date"=>"2014-07-29", "response_time"=>"64 days 23:44:04.855846", "id"=>"108692", "customer_id"=>"19078"},
 {"date"=>"2014-07-29", "response_time"=>"01:52:52.975275", "id"=>"108694", "customer_id"=>"19080"},
 {"date"=>"2014-07-29", "response_time"=>"12 days 20:59:04.626793", "id"=>"108702", "customer_id"=>"19010"},
 {"date"=>"2014-07-29", "response_time"=>"55 days 20:49:27.975908", "id"=>"108706", "customer_id"=>"5114"},
 {"date"=>"2014-07-29", "response_time"=>"02:08:34.323635", "id"=>"108712", "customer_id"=>"2400"},
 {"date"=>"2014-07-29", "response_time"=>"1 day 02:39:03.611563", "id"=>"108732", "customer_id"=>"14886"},
 {"date"=>"2014-07-29", "response_time"=>"00:47:16.785811", "id"=>"108740", "customer_id"=>"18607"},
 {"date"=>"2014-07-29", "response_time"=>"00:48:26.918115", "id"=>"108759", "customer_id"=>"16310"},
 {"date"=>"2014-07-29", "response_time"=>"00:34:33.677096", "id"=>"108760", "customer_id"=>"13336"},
 {"date"=>"2014-07-29", "response_time"=>"00:00:52.137527", "id"=>"108796", "customer_i

mberman84

  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