CASE 2 GROUP LEVEL NEW

PHOTO EMBED

Fri May 05 2023 09:02:43 GMT+0000 (Coordinated Universal Time)

Saved by @shubhangi_burle 

%jdbc(hive)
set tez.queue.name=phonepe_verified;
set hive.execution.engine=tez;
 
-- CASE 2 GROUP LEVEL COMBINED
 
SELECT P.run_date_monday, P.group_name 
, week1_ticket_cnt
, week1_reply_cnt
, week1_private_note_cnt
, week1_reopen_cnt
, week2_ticket_cnt
, week2_reply_cnt
, week2_private_note_cnt
, week2_reopen_cnt
, week3_ticket_cnt
, week3_reply_cnt
, week3_private_note_cnt
, week3_reopen_cnt
, week4_ticket_cnt
, week4_reply_cnt
, week4_private_note_cnt
, week4_reopen_cnt
, week5_ticket_cnt
, week5_reply_cnt
, week5_private_note_cnt
, week5_reopen_cnt
FROM
    (-- INCOMING TICKET COUNT
    SELECT '2023-05-22' as run_date_monday, A.group_name
    , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 36) AND DATE_SUB('2023-05-22', 30) THEN A.incoming_cnt ELSE 0 END) AS Week1_ticket_cnt
    , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 29) AND DATE_SUB('2023-05-22', 23) THEN A.incoming_cnt ELSE 0 END) AS Week2_ticket_cnt
    , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 22) AND DATE_SUB('2023-05-22', 16) THEN A.incoming_cnt ELSE 0 END) AS Week3_ticket_cnt
    , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 15) AND DATE_SUB('2023-05-22', 9) THEN A.incoming_cnt ELSE 0 END) AS Week4_ticket_cnt
    , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 8) AND DATE_SUB('2023-05-22', 2) THEN A.incoming_cnt ELSE 0 END) AS Week5_ticket_cnt
    FROM
        (SELECT `group` AS group_name, Date(created_time) as dt, ticket_id, COUNT(*) AS incoming_cnt
        FROM freshdesk.properties
        WHERE year IN (year(DATE_SUB('2023-05-22', 36)) , year(DATE_SUB('2023-05-22', 2)))
        AND month >= month(DATE_SUB('2023-05-22', 36)) AND month <= month(DATE_SUB('2023-05-22', 2))
        AND Date(created_time) BETWEEN DATE_SUB('2023-05-22', 36) AND DATE_SUB('2023-05-22', 2)
        AND `group` IN ('Risk Team', 'security', 'Cybercell', 'FRA-Escalations', 'Blocked Fraud', 'Lien Email', 'NPCI Escalations')
        GROUP BY `group`, Date(created_time), ticket_id)A
    GROUP BY group_name)P
LEFT JOIN
    (-- REOPEN COUNT
    SELECT '2023-05-22' as run_date_monday, A.group_name
    , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 36) AND DATE_SUB('2023-05-22', 30) THEN B.reopen_cnt ELSE 0 END) AS Week1_reopen_cnt
    , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 29) AND DATE_SUB('2023-05-22', 23) THEN B.reopen_cnt ELSE 0 END) AS Week2_reopen_cnt
    , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 22) AND DATE_SUB('2023-05-22', 16) THEN B.reopen_cnt ELSE 0 END) AS Week3_reopen_cnt
    , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 15) AND DATE_SUB('2023-05-22', 9) THEN B.reopen_cnt ELSE 0 END) AS Week4_reopen_cnt
    , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 8) AND DATE_SUB('2023-05-22', 2) THEN B.reopen_cnt ELSE 0 END) AS Week5_reopen_cnt
    FROM
        (SELECT `group` AS group_name, Date(created_time) as dt, ticket_id
        FROM freshdesk.properties
        WHERE year IN (year(DATE_SUB('2023-05-22', 36)) , year(DATE_SUB('2023-05-22', 2)))
        AND month >= month(DATE_SUB('2023-05-22', 36)) AND month <= month(DATE_SUB('2023-05-22', 2))
        AND Date(created_time) BETWEEN DATE_SUB('2023-05-22', 36) AND DATE_SUB('2023-05-22', 2)
        AND `group` IN ('Risk Team', 'security', 'Cybercell', 'FRA-Escalations', 'Blocked Fraud', 'Lien Email', 'NPCI Escalations'))A
    LEFT JOIN
        (SELECT from_unixtime(unix_timestamp(`date` ,'yyyyMMdd'), 'yyyy-MM-dd') as dt, ticket_id, COUNT(*) AS reopen_cnt
        FROM freshdesk.activities 
        WHERE from_unixtime(unix_timestamp(`date` ,'yyyyMMdd'), 'yyyy-MM-dd') >= DATE_SUB('2023-05-22', 36)
        AND activity_status = 'Reopen'
        GROUP BY from_unixtime(unix_timestamp(`date` ,'yyyyMMdd'), 'yyyy-MM-dd'), ticket_id)B
    ON A.ticket_id = B.ticket_id AND A.dt <= B.dt
    GROUP BY group_name)Q
on P.run_date_monday = Q.run_date_monday AND P.group_name = Q.group_name
LEFT JOIN 
    (-- REPLY & PRIVATE NOTE COUNT
    SELECT '2023-05-22' as run_date_monday, A.group_name
    , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 36) AND DATE_SUB('2023-05-22', 30) THEN reply_cnt ELSE 0 END) AS Week1_reply_cnt
    , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 36) AND DATE_SUB('2023-05-22', 30) THEN private_note_cnt ELSE 0 END) AS Week1_private_note_cnt 
    , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 29) AND DATE_SUB('2023-05-22', 23) THEN reply_cnt ELSE 0 END) AS Week2_reply_cnt
    , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 29) AND DATE_SUB('2023-05-22', 23) THEN private_note_cnt ELSE 0 END) AS Week2_private_note_cnt
    , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 22) AND DATE_SUB('2023-05-22', 16) THEN reply_cnt ELSE 0 END) AS Week3_reply_cnt
    , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 22) AND DATE_SUB('2023-05-22', 16) THEN private_note_cnt ELSE 0 END) AS Week3_private_note_cnt 
    , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 15) AND DATE_SUB('2023-05-22', 9) THEN reply_cnt ELSE 0 END) AS Week4_reply_cnt
    , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 15) AND DATE_SUB('2023-05-22', 9) THEN private_note_cnt ELSE 0 END) AS Week4_private_note_cnt  
    , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 8) AND DATE_SUB('2023-05-22', 2) THEN reply_cnt ELSE 0 END) AS Week5_reply_cnt
    , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 8) AND DATE_SUB('2023-05-22', 2) THEN private_note_cnt ELSE 0 END) AS Week5_private_note_cnt  
    FROM
        (SELECT `group` AS group_name, Date(created_time) as dt, ticket_id, COUNT(*) AS incoming_cnt
        FROM freshdesk.properties
        WHERE year IN (year(DATE_SUB('2023-05-22', 36)) , year(DATE_SUB('2023-05-22', 2)))
        AND month >= month(DATE_SUB('2023-05-22', 36)) AND month <= month(DATE_SUB('2023-05-22', 2))
        AND Date(created_time) BETWEEN DATE_SUB('2023-05-22', 36) AND DATE_SUB('2023-05-22', 2)
        AND `group` IN ('Risk Team', 'security', 'Cybercell', 'FRA-Escalations', 'Blocked Fraud', 'Lien Email', 'NPCI Escalations')
        GROUP BY `group`, Date(created_time), ticket_id)A
    LEFT JOIN
        (SELECT from_unixtime(unix_timestamp(`date` ,'yyyyMMdd'), 'yyyy-MM-dd') as dt, ticket_id
        , COUNT(CASE WHEN activity_note_type = 0 THEN ticket_id ELSE NULL END) AS reply_cnt
        , COUNT(CASE WHEN activity_note_type = 3 THEN ticket_id ELSE NULL END) AS private_note_cnt
        FROM freshdesk.activities 
        WHERE from_unixtime(unix_timestamp(`date` ,'yyyyMMdd'), 'yyyy-MM-dd') >= DATE_SUB('2023-05-22', 36)
        GROUP BY from_unixtime(unix_timestamp(`date` ,'yyyyMMdd'), 'yyyy-MM-dd'), ticket_id)C
    ON A.ticket_id = C.ticket_id AND A.dt <= C.dt
    GROUP BY group_name)R
on P.run_date_monday = R.run_date_monday AND P.group_name = R.group_name
ORDER BY P.group_name
content_copyCOPY