This code provides a way to get the hourly session chat data

Chat_Details.sql

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