Last active
January 16, 2023 07:55
-
-
Save omi-akif/def328e923636b2f0a2a0dc813b3adb3 to your computer and use it in GitHub Desktop.
This code provides a way to get the hourly session chat data
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
--odps sql | |
--********************************************************************-- | |
--author:Razzak, Khandaker | |
--create time:2023-01-16 15:23:17 | |
--Created SQL table for the Chat Data. | |
--********************************************************************-- | |
SELECT chat_hour, | |
COUNT(DISTINCT M1) AS month_1, | |
COUNT(DISTINCT M2) AS month_2, | |
COUNT(DISTINCT M3) AS month_3, | |
COUNT(DISTINCT M4) AS month_4, | |
COUNT(DISTINCT M5) AS month_5, | |
COUNT(DISTINCT M6) AS month_6, | |
COUNT(DISTINCT M7) AS month_7, | |
COUNT(DISTINCT M8) AS month_8, | |
COUNT(DISTINCT M9) AS month_9, | |
COUNT(DISTINCT M10) AS month_10, | |
COUNT(DISTINCT M11) AS month_11 | |
FROM( | |
SELECT SUBSTR(chat_time, 1, 2) chat_hour, | |
CASE WHEN month_number = '2022-02' THEN session_id END M1, | |
CASE WHEN month_number = '2022-03' THEN session_id END M2, | |
CASE WHEN month_number = '2022-04' THEN session_id END M3, | |
CASE WHEN month_number = '2022-05' THEN session_id END M4, | |
CASE WHEN month_number = '2022-06' THEN session_id END M5, | |
CASE WHEN month_number = '2022-07' THEN session_id END M6, | |
CASE WHEN month_number = '2022-08' THEN session_id END M7, | |
CASE WHEN month_number = '2022-09' THEN session_id END M8, | |
CASE WHEN month_number = '2022-10' THEN session_id END M9, | |
CASE WHEN month_number = '2022-11' THEN session_id END M10, | |
CASE WHEN month_number = '2022-12' THEN session_id END M11 | |
FROM( | |
SELECT month_number, | |
session_id, | |
SUBSTR(local_gmt_create, 12, 19) AS chat_time, | |
is_session_direct_agent, | |
is_session_unsatisfied, | |
is_session_intention_to_agent, | |
is_session_last_chat_no_answer, | |
is_session_click_to_agent, | |
is_session_switch_to_agent, | |
is_session_last_chat_recommend_not_clk | |
FROM data_table | |
WHERE tenant_name_new = 'bd_data' | |
) | |
-- -- Resolved Sessions | |
is_session_unsatisfied = 'N' | |
AND is_session_direct_agent = 'N' | |
AND is_session_intention_to_agent = 'N' | |
AND is_session_last_chat_no_answer = 'N' | |
AND is_session_click_to_agent = 'N' | |
AND is_session_switch_to_agent = 'N' | |
AND is_session_last_chat_recommend_not_clk ='N' | |
) GROUP BY chat_hour ORDER BY chat_hour |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment