monicaluodialpad commented on issue #9517: URL: https://github.com/apache/pinot/issues/9517#issuecomment-1265742997
We also seen an error was this <img width="1191" alt="Screen Shot 2022-10-03 at 12 46 55 PM" src="https://user-images.githubusercontent.com/74613115/193633238-4665fd1e-e286-460b-936a-21f6978a057e.png";> The query was ``` SELECT HOUR("date_started",'US/Pacific') "hour",COUNT("id") "all_calls",SUM("analytics_tags" IN ('inbound')) "inbound_calls",SUM("analytics_tags" IN ('outbound')) "outbound_calls",SUM("recording_name"<>'null') "all_voicemails",SUM(CASE WHEN "analytics_tags" IN ('missed') AND "analytics_tags" IN ('spam')=false THEN 1 ELSE 0 END) "missed_calls",SUM(CASE WHEN "duration_connected" IS NOT NULL THEN "duration_connected" ELSE 0 END/60) "total_minutes",SUM(CASE WHEN "duration_connected" IS NOT NULL THEN "duration_connected" ELSE 0 END)/60/GREATEST(SUM("date_connected" IS NOT NULL),1) "average_connected_duration",SUM(CASE WHEN "analytics_tags" IN ('inbound') THEN "duration_inbound" ELSE 0 END/60) "total_inbound_minutes",SUM(CASE WHEN "analytics_tags" IN ('outbound') THEN "duration_outbound" ELSE 0 END/60) "total_outbound_minutes",SUM("is_internal") "switch_to_switch",SUM(CASE WHEN "rating">0 THEN "rating" ELSE 0 END) "rating_sum",SUM("rating">0) "rating_count",SUM("device_type" IN ('pstn') ) "pstn_calls",SUM("device_type" IN ('native','packaged_app','webrtc')) "desktop_app_calls",SUM("device_type" IN ('android','iphone','ipad')) "mobile_voip_calls",SUM("device_type" IN ('obi','polycom','sip','mini')) "desk_phone_calls",SUM(CASE WHEN "analytics_tags" IN ('answered') THEN "duration_waited" ELSE 0 END) "total_wait_seconds",SUM(CASE WHEN "analytics_tags" IN ('answered') THEN "duration_waited"/60 ELSE 0 END)/GREATEST(SUM("analytics_tags" IN ('answered')),1) "time_in_system",SUM("analytics_tags" IN ('abandoned') AND "analytics_tags" IN ('spam')=false) "abandoned_calls",SUM("analytics_tags" IN ('cancelled')) "cancelled_calls",SUM(CASE WHEN "analytics_tags" IN ('inbound') AND "target_availability_status" IN ('open','holiday') AND ("date_callback_connected" IS NOT NULL OR "date_connected" IS NOT NULL) AND "duration_to_answer"<60 THEN 1 ELSE 0 END) "above_service_level",MAX(CASE WHEN "analytics_tags" IN ('answered') THEN "duration_inbound" ELSE 0 END) "max_inbound_seconds",MIN( CASE WHEN "analytics_tags" IN ('answered') THEN "duration_inbound" ELSE 2147483647 END) "min_inbound_seconds",MAX(CASE WHEN "analytics_tags" IN ('outbound_connected','callback_attempted_connected') THEN "duration_outbound" ELSE 0 END) "max_outbound_seconds",MIN(CASE WHEN "analytics_tags" IN ('outbound_connected','callback_attempted_connected') THEN "duration_outbound" ELSE 2147483647 END) "min_outbound_seconds",SUM(CASE WHEN "analytics_tags" IN ('abandoned') AND "target_availability_status" IN ('open','holiday') AND "analytics_tags" IN ('spam')=false AND "duration_abandoned"<10 THEN 1 ELSE 0 END) "short_abandoned",MAX(CASE WHEN "analytics_tags" IN ('answered','outbound_connected') AND "date_started" IS NOT NULL THEN ("date_connected"-"date_started")/1000 ELSE 0 END) "max_wait_seconds",MIN(CASE WHEN "analytics_tags" IN ('answered','outbound_connected') AND "date_started" IS NOT NULL THEN ("date_connected"-"date_started")/1000 ELSE 2147483647 END) "min_wait_seconds",MAX(CASE WHEN "ana lytics_tags" IN ('abandoned') AND "analytics_tags" IN ('spam')=false AND "date_started" IS NOT NULL THEN "duration_abandoned" ELSE 0 END) "max_abandoned_seconds",MIN(CASE WHEN "analytics_tags" IN ('abandoned') AND "analytics_tags" IN ('spam')=false AND "date_started" IS NOT NULL THEN "duration_abandoned" ELSE 2147483647 END) "min_abandoned_seconds",SUM(CASE WHEN "analytics_tags" IN ('abandoned') AND "analytics_tags" IN ('spam')=false THEN "duration_abandoned" ELSE 0 END) "total_abandoned_seconds",SUM("device_type" IN ('harness','salesforce','iframe_zendesk','iframe_service_titan','iframe_front','iframe_salesforce')) "web_app_calls",SUM("analytics_tags" IN ('callbacks_requested')) "callbacks_requested",SUM("analytics_tags" IN ('callbacks_attempted')) "callbacks_completed",SUM("analytics_tags" IN ('callback_attempted_cancelled')) "callbacks_cancelled",SUM("target_availability_status" IN ('open','holiday_open') AND ("date_callback_connected" IS NOT NULL OR ("direction"='inbound' AND "d ate_connected" IS NOT NULL))) "open_inbound_calls",SUM("analytics_tags" IN ('missed') AND "analytics_tags" IN ('spam')=false AND "target_availability_status" IN ('open','holiday_open') AND "direction"='inbound') "open_missed_calls",SUM("analytics_tags" IN ('abandoned') AND "analytics_tags" IN ('spam')=false AND "target_availability_status" IN ('open','holiday_open') AND "direction"='inbound') "open_abandoned_calls",SUM("analytics_tags" IN ('forward_transfer') AND "analytics_tags" IN ('missed','spam')=false) "forwarded_calls",SUM("duration_to_answer") "total_answer_seconds",SUM("duration_to_answer")/60/GREATEST(SUM("analytics_tags" IN ('answered')),1) "average_speed_to_answer",MAX("duration_to_answer") "max_answer_seconds",MIN(CASE WHEN "analytics_tags" IN ('answered') THEN "duration_to_answer" ELSE 2147483647 END) "min_answer_seconds",SUM("analytics_tags" IN ('auto_transfer','dtmf_transfer','router_transfer','forward_transfer') AND "target_availability_status" IN ('open','open_holid ay') AND "analytics_tags" IN ('missed','spam')=false AND "direction"='inbound') "open_transferred_calls",SUM(("date_connected" IS NOT NULL OR "date_callback_connected" IS NOT NULL) AND "direction"='inbound') "inbound_calls_plus_callbacks",SUM("analytics_tags" IN ('callback_attempted_connected')) "callbacks_connected",SUM("analytics_tags" IN ('callback_attempted_unconnected')) "callbacks_unconnected",SUM(CASE WHEN "analytics_tags" IN ('missed') AND "analytics_tags" IN ('voicemail') AND "analytics_tags" IN ('spam')=false THEN 1 ELSE 0 END) "missed_voicemails",SUM("analytics_tags" IN ('voicemail') AND "date_rang" IS NULL) "direct_voicemails",SUM("analytics_tags" IN ('missed') AND "analytics_tags" IN ('auto_transfer','dtmf_transfer','router_transfer','forward_transfer') AND "analytics_tags" IN ('spam')=false) "missed_transferred_calls",SUM("analytics_tags" IN ('missed') AND "analytics_tags" IN ('auto_transfer','dtmf_transfer','router_transfer','forward_transfer') AND "target_availabilit y_status" IN ('open','open_holiday') AND "analytics_tags" IN ('spam')=false) "open_missed_transferred_calls",SUM("analytics_tags" IN ('missed') AND "analytics_tags" IN ('voicemail') AND "target_availability_status" IN ('open','open_holiday') AND "analytics_tags" IN ('spam')=false) "open_missed_voicemails",SUM("analytics_tags" IN ('voicemail') AND "target_availability_status" IN ('open','open_holiday') AND "date_rang" IS NULL) "open_direct_voicemails",SUM("analytics_tags" IN ('outbound') AND "target_availability_status" IN ('open','open_holiday')) "open_outbound_calls",SUM("analytics_tags" IN ('cancelled') AND "target_availability_status" IN ('open','open_holiday')) "open_cancelled_calls",SUM("analytics_tags" IN ('spam')) "spam_calls",SUM("analytics_tags" IN ('transferred')) "transferred_calls",SUM("analytics_tags" IN ('deleted')) "deleted_calls",SUM("analytics_tags" IN ('parked')) "parked_calls",SUM("analytics_tags" IN ('unparked')) "unparked_calls",SUM("analytics_tags" IN ('message ') AND "analytics_tags" IN ('spam','missed')=false) "calls_sent_to_recorded_message",SUM("analytics_tags" IN ('meeting')) "meetings",SUM("analytics_tags" IN ('direct_to_voicemail') AND "analytics_tags" IN ('voicemail') AND "analytics_tags" IN ('spam','missed')=false) "direct_to_voicemail",SUM("analytics_tags" IN ('answered','outbound_connected','callback_attempted_connected')) "handled_calls",SUM("analytics_tags" IN ('answered')) "answered_calls",SUM("analytics_tags" IN ('unanswered')) "unanswered_calls",SUM("analytics_tags" IN ('outbound_connected')) "outbound_connected_calls",SUM("analytics_tags" IN ('self')) "self_calls",SUM("analytics_tags" IN ('self_group')) "self_group_calls",SUM("analytics_tags" IN ('manual_transfer')) "manual_transfer_calls",SUM("analytics_tags" IN ('dtmf_transfer') AND "analytics_tags" IN ('spam','missed')=false) "dtmf_transfer",SUM("analytics_tags" IN ('auto_transfer') AND "analytics_tags" IN ('spam','missed')=false) "auto_transfer",SUM("analytics_tags" IN ('transfer_voicemail') AND "analytics_tags" IN ('voicemail') AND "analytics_tags" IN ('spam','missed')=false) "transfer_voicemail",SUM("analytics_tags" IN ('router_transfer') AND "analytics_tags" IN ('spam','missed')=false) "router_transfer",SUM("analytics_tags" IN ('transferred_from')) "calls_transferred_in",SUM("analytics_tags" IN ('transferred_to')) "calls_transferred_out",SUM("analytics_tags" IN ('dtmf_voicemail') AND "analytics_tags" IN ('voicemail') AND "analytics_tags" IN ('spam','missed')=false) "dtmf_voicemail",SUM("analytics_tags" IN ('in_queue_voicemail') AND "analytics_tags" IN ('voicemail') AND "analytics_tags" IN ('spam','missed')=false) "in_queue_voicemail",SUM("analytics_tags" IN ('manual_transfer') AND "analytics_tags" IN ('answered')) "answered_transferred_calls",SUM("analytics_tags" IN ('manual_transfer') AND "analytics_tags" IN ('outbound_connected')) "connected_transferred_calls",SUM("analytics_tags" IN ('user_initiated')) "user_initiated_calls" FROM "calls" WH ERE "company_id"=377007 AND (("target_id"=6652811376590848 AND "target_kind"='CallCenter') OR ("proxy_target_id"=6652811376590848 AND "proxy_target_kind"='CallCenter' AND "entry_point_target_id" IS NULL)) AND (("target_id" IS NULL AND "transferred_from_target_id" IS NULL) OR "target_id"<>"transferred_from_target_id" OR "transferred_to_state"<>'voicemail') AND BETWEEN("date_started",FROMDATETIME('2022-09-26 US/Pacific','YYYY-MM-dd ZZZ'),FROMDATETIME('2022-09-27 US/Pacific','YYYY-MM-dd ZZZ')) GROUP BY "hour" ORDER BY "hour" LIMIT 500 ``` -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@pinot.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@pinot.apache.org For additional commands, e-mail: commits-h...@pinot.apache.org
![https://user-images.githubusercontent.com/74613115/193633238-4665fd1e-e286-460b-936a-21f6978a057e.png"](https://user-images.githubusercontent.com/74613115/193633238-4665fd1e-e286-460b-936a-21f6978a057e.png"){kind=link}