mberman84
mberman84
/ gist:47c48d5b92a337aa08f9
Created
January 21, 2015 19:29
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| def calc | |
| res = Message.connection.execute(" | |
| SELECT q4.* 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 |
mberman84
/ gist:060066cef967bf541399
Created
January 21, 2015 19:31
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| def calc | |
| res = Message.connection.execute(" | |
| SELECT q4.* 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 |
mberman84
/ gist:c68c5c04beecfaad17f0
Created
January 21, 2015 19:40
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| [{"response_time"=>"98 days 23:04:53.941484", "id"=>"50208", "customer_id"=>"2049"}, | |
| {"response_time"=>"08:22:51.910561", "id"=>"302830", "customer_id"=>"2049"}, | |
| {"response_time"=>"110 days 16:35:38.133115", "id"=>"11519", "customer_id"=>"2050"}, | |
| {"response_time"=>"60 days 02:39:32.790405", "id"=>"168866", "customer_id"=>"2050"}, | |
| {"response_time"=>"2 days 15:04:51.745552", "id"=>"756949", "customer_id"=>"2050"}, | |
| {"response_time"=>"02:52:21.61947", "id"=>"757891", "customer_id"=>"2050"}, | |
| {"response_time"=>"59 days 01:21:55.921799", "id"=>"28354", "customer_id"=>"2053"}, | |
| {"response_time"=>"01:18:55.147105", "id"=>"25616", "customer_id"=>"2056"}, | |
| {"response_time"=>"00:05:55.22663", "id"=>"25678", "customer_id"=>"2056"}, | |
| {"response_time"=>"01:10:41.813236", "id"=>"25685", "customer_id"=>"2056"}, |
mberman84
/ gist:08cce1b0979b0d817e4d
Created
January 22, 2015 07:10
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| def calculate_response_time_per_message | |
| 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 = 2 AND via_api = false AND mass_message_id IS NULL) AS q1 |
mberman84
/ gist:988777fc5cf3a6d534ac
Created
January 24, 2015 03:43
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| def calculate_response_time_average_per_day | |
| Message.connection.execute(" | |
| SELECT q4.date, AVG(q4.response_time / 60) FROM | |
| (SELECT EXTRACT(HOUR from q3.created_at AT TIME ZONE '-08:00') 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 |
mberman84
/ gist:bd245be9d8eeaab8d52c
Created
January 24, 2015 04:05
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| def calculate_response_time_average_per_day | |
| Message.connection.execute(" | |
| SELECT q4.date, AVG(q4.response_time / 60) FROM | |
| (SELECT EXTRACT(HOUR FROM now() 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 |
mberman84
/ gist:80f065b9b4db0d250c81
Created
January 24, 2015 04:56
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
mberman84
/ gist:89a1b9c9088f38e5d63b
Created
January 26, 2015 22:11
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
mberman84
/ gist:86c6a4fd91d701b146b2
Created
January 28, 2015 01:00
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| message.rb: | |
| user_id | |
| customer_id | |
| text | |
| viewed | |
| company_id | |
| via_api | |
| sent_at | |
| sms_message.rb: |
mberman84
/ gist:95d4381f24004f5ed9f0
Created
January 30, 2015 20:22
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| %form{method:"get", style: "display: inline-block;"} | |
| - if @graph_type == 'volume' | |
| %b volume | |
| [ | |
| - if @sub_graph_type == 'daily' | |
| daily | |
| = link_to 'hourly', analytics_path(graph_type: 'volume', start_date: @since_start, end_date: @since_end, sub_graph_type: 'hourly') | |
| - else | |
| = link_to 'daily', analytics_path(graph_type: 'volume', start_date: @since_start, end_date: @since_end, sub_graph_type: 'daily') | |
| hourly |