CASE 4 AGENT LEVEL ONLY NEW

PHOTO EMBED

Wed Apr 26 2023 17:17:29 GMT+0000 (Coordinated Universal Time)

Saved by @shubhangi_burle 

%jdbc(hive)
set tez.queue.name=phonepe_verified;
set hive.execution.engine=tez;

-- -- INCOMING TICKET COUNT
-- SELECT '2023-04-24' as run_date_monday, A.agent_name
-- , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 36) AND DATE_SUB('2023-04-24', 30) THEN A.incoming_cnt ELSE 0 END) AS Week1_ticket_cnt
-- , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 29) AND DATE_SUB('2023-04-24', 23) THEN A.incoming_cnt ELSE 0 END) AS Week2_ticket_cnt
-- , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 22) AND DATE_SUB('2023-04-24', 16) THEN A.incoming_cnt ELSE 0 END) AS Week3_ticket_cnt
-- , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 15) AND DATE_SUB('2023-04-24', 9) THEN A.incoming_cnt ELSE 0 END) AS Week4_ticket_cnt
-- , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 8) AND DATE_SUB('2023-04-24', 2) THEN A.incoming_cnt ELSE 0 END) AS Week5_ticket_cnt
-- FROM
--     (SELECT Date(created_time) as dt, `agent` AS agent_name, COUNT(*) AS incoming_cnt
--     FROM freshdesk.properties
--     WHERE year IN (year(DATE_SUB('2023-04-24', 36)) , year(DATE_SUB('2023-04-24', 2)))
--     AND month >= month(DATE_SUB('2023-04-24', 36)) AND month <= month(DATE_SUB('2023-04-24', 2))
--     AND Date(created_time) BETWEEN DATE_SUB('2023-04-24', 36) AND DATE_SUB('2023-04-24', 2)
--     -- AND status IN ('Closed', 'Resolved')
--     AND `group` IN ('Risk Team', 'security', 'Cybercell', 'FRA-Escalations', 'Blocked Fraud', 'Lien Email', 'NPCI Escalations')
--     AND `agent` IN ('Akash Shinde', 'arifa.fathima', 'ashmita.maheshwari', 'imran.khan', 'pooja.rajangam', 'sharath.ls', 'vivek.gowra', 'BhanuPrasad N'
--     , 'karthik.hiremath', 'anushka.prabha', 'Prasad Patil', 'fra-automation', 'alice')
--     GROUP BY Date(created_time), `agent`)A
-- GROUP BY agent_name

-- REPLY & PRIVATE NOTE COUNT
 SELECT '2023-04-24' AS run_date_monday, C.agent_name
 , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 36) AND DATE_SUB('2023-04-24', 30) THEN reply_cnt ELSE 0 END) AS Week1_reply_cnt
 , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 36) AND DATE_SUB('2023-04-24', 30) THEN private_note_cnt ELSE 0 END) AS Week1_private_note_cnt 
 
 , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 29) AND DATE_SUB('2023-04-24', 23) THEN reply_cnt ELSE 0 END) AS Week2_reply_cnt
 , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 29) AND DATE_SUB('2023-04-24', 23) THEN private_note_cnt ELSE 0 END) AS Week2_private_note_cnt  
 
 , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 22) AND DATE_SUB('2023-04-24', 16) THEN reply_cnt ELSE 0 END) AS Week3_reply_cnt
 , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 22) AND DATE_SUB('2023-04-24', 16) THEN private_note_cnt ELSE 0 END) AS Week3_private_note_cnt 
 
 , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 15) AND DATE_SUB('2023-04-24', 9) THEN reply_cnt ELSE 0 END) AS Week4_reply_cnt
 , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 15) AND DATE_SUB('2023-04-24', 9) THEN private_note_cnt ELSE 0 END) AS Week4_private_note_cnt 
 
 , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 8) AND DATE_SUB('2023-04-24', 2) THEN reply_cnt ELSE 0 END) AS Week5_reply_cnt
 , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 8) AND DATE_SUB('2023-04-24', 2) THEN private_note_cnt ELSE 0 END) AS Week5_private_note_cnt  
FROM
    (SELECT Date(created_time) as dt, ticket_id
    FROM freshdesk.properties
    WHERE year IN (year(DATE_SUB('2023-04-24', 36)) , year(DATE_SUB('2023-04-24', 2)))
    AND month >= month(DATE_SUB('2023-04-24', 36)) AND month <= month(DATE_SUB('2023-04-24', 2))
    AND Date(created_time) BETWEEN DATE_SUB('2023-04-24', 36) AND DATE_SUB('2023-04-24', 2)
    -- AND status IN ('Closed', 'Resolved')
    AND `group` IN ('Risk Team', 'security', 'Cybercell', 'FRA-Escalations', 'Blocked Fraud', 'Lien Email', 'NPCI Escalations')
    -- AND `agent` IN ('Akash Shinde', 'arifa.fathima', 'ashmita.maheshwari', 'imran.khan', 'pooja.rajangam', 'sharath.ls', 'vivek.gowra', 'BhanuPrasad N'
    -- , 'karthik.hiremath', 'anushka.prabha', 'Prasad Patil')
    GROUP BY Date(created_time), ticket_id)A
INNER JOIN
    (SELECT from_unixtime(unix_timestamp(`date` ,'yyyyMMdd'), 'yyyy-MM-dd') as dt, ticket_id
    , CASE WHEN performer_id = 40094141204 THEN 'Akash Shinde'
        WHEN performer_id = 40096302442 THEN 'arifa.fathima'
        WHEN performer_id = 40098326974 THEN 'ashmita.maheshwari'
        WHEN performer_id = 40068659687 THEN 'imran.khan'
        WHEN performer_id = 40079215228 THEN 'pooja.rajangam'
        WHEN performer_id = 40097988320 THEN 'sharath.ls'
        WHEN performer_id = 40080532390 THEN 'vivek.gowra'
        WHEN performer_id = 40139679443 THEN 'BhanuPrasad N'
        WHEN performer_id = 40134990405 THEN 'karthik.hiremath'
        WHEN performer_id = 40071150299 THEN 'anushka.prabha'
        WHEN performer_id = 40070159259 THEN 'Prasad Patil'
        WHEN performer_id = 40031538049 THEN 'alice'
        WHEN performer_id = 40062605741 THEN 'fra-automation' END AS agent_name 
    , 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-04-24', 36)
    AND performer_id IN (40094141204, 40096302442, 40098326974, 40068659687, 40079215228, 40097988320, 40080532390, 40139679443, 40134990405, 40071150299, 40070159259, 40031538049, 40062605741)
    GROUP BY from_unixtime(unix_timestamp(`date` ,'yyyyMMdd'), 'yyyy-MM-dd'), ticket_id
    , CASE WHEN performer_id = 40094141204 THEN 'Akash Shinde'
        WHEN performer_id = 40096302442 THEN 'arifa.fathima'
        WHEN performer_id = 40098326974 THEN 'ashmita.maheshwari'
        WHEN performer_id = 40068659687 THEN 'imran.khan'
        WHEN performer_id = 40079215228 THEN 'pooja.rajangam'
        WHEN performer_id = 40097988320 THEN 'sharath.ls'
        WHEN performer_id = 40080532390 THEN 'vivek.gowra'
        WHEN performer_id = 40139679443 THEN 'BhanuPrasad N'
        WHEN performer_id = 40134990405 THEN 'karthik.hiremath'
        WHEN performer_id = 40071150299 THEN 'anushka.prabha'
        WHEN performer_id = 40070159259 THEN 'Prasad Patil' 
        WHEN performer_id = 40031538049 THEN 'alice'
        WHEN performer_id = 40062605741 THEN 'fra-automation' END)C
ON A.ticket_id = C.ticket_id AND A.dt <= C.dt
GROUP BY C.agent_name

-----------------------------------------------------------------
  -- final combined
  %jdbc(hive)
set tez.queue.name=phonepe_verified;
set hive.execution.engine=tez;
 
SELECT P.run_date_monday, P.agent_name
, week1_ticket_cnt
, week1_reply_cnt
, week1_private_note_cnt
, week2_ticket_cnt
, week2_reply_cnt
, week2_private_note_cnt
, week3_ticket_cnt
, week3_reply_cnt
, week3_private_note_cnt
, week4_ticket_cnt
, week4_reply_cnt
, week4_private_note_cnt
, week5_ticket_cnt
, week5_reply_cnt
, week5_private_note_cnt
FROM 
    (-- INCOMING TICKET COUNT
    SELECT '2023-04-24' as run_date_monday, A.agent_name
    , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 36) AND DATE_SUB('2023-04-24', 30) THEN A.incoming_cnt ELSE 0 END) AS Week1_ticket_cnt
    , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 29) AND DATE_SUB('2023-04-24', 23) THEN A.incoming_cnt ELSE 0 END) AS Week2_ticket_cnt
    , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 22) AND DATE_SUB('2023-04-24', 16) THEN A.incoming_cnt ELSE 0 END) AS Week3_ticket_cnt
    , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 15) AND DATE_SUB('2023-04-24', 9) THEN A.incoming_cnt ELSE 0 END) AS Week4_ticket_cnt
    , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 8) AND DATE_SUB('2023-04-24', 2) THEN A.incoming_cnt ELSE 0 END) AS Week5_ticket_cnt
    FROM
        (SELECT Date(created_time) as dt, `agent` AS agent_name, COUNT(*) AS incoming_cnt
        FROM freshdesk.properties
        WHERE year IN (year(DATE_SUB('2023-04-24', 36)) , year(DATE_SUB('2023-04-24', 2)))
        AND month >= month(DATE_SUB('2023-04-24', 36)) AND month <= month(DATE_SUB('2023-04-24', 2))
        AND Date(created_time) BETWEEN DATE_SUB('2023-04-24', 36) AND DATE_SUB('2023-04-24', 2)
        AND `group` IN ('Risk Team', 'security', 'Cybercell', 'FRA-Escalations', 'Blocked Fraud', 'Lien Email', 'NPCI Escalations')
        AND `agent` IN ('Akash Shinde', 'arifa.fathima', 'ashmita.maheshwari', 'imran.khan', 'pooja.rajangam', 'sharath.ls', 'vivek.gowra', 'BhanuPrasad N'
        , 'karthik.hiremath', 'anushka.prabha', 'Prasad Patil', 'fra-automation', 'alice')
        GROUP BY Date(created_time), `agent`)A
    GROUP BY agent_name)P
LEFT JOIN 
    (-- REPLY & PRIVATE NOTE COUNT
     SELECT '2023-04-24' AS run_date_monday, C.agent_name
     , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 36) AND DATE_SUB('2023-04-24', 30) THEN reply_cnt ELSE 0 END) AS Week1_reply_cnt
     , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 36) AND DATE_SUB('2023-04-24', 30) THEN private_note_cnt ELSE 0 END) AS Week1_private_note_cnt 
     
     , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 29) AND DATE_SUB('2023-04-24', 23) THEN reply_cnt ELSE 0 END) AS Week2_reply_cnt
     , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 29) AND DATE_SUB('2023-04-24', 23) THEN private_note_cnt ELSE 0 END) AS Week2_private_note_cnt  
     
     , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 22) AND DATE_SUB('2023-04-24', 16) THEN reply_cnt ELSE 0 END) AS Week3_reply_cnt
     , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 22) AND DATE_SUB('2023-04-24', 16) THEN private_note_cnt ELSE 0 END) AS Week3_private_note_cnt 
     
     , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 15) AND DATE_SUB('2023-04-24', 9) THEN reply_cnt ELSE 0 END) AS Week4_reply_cnt
     , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 15) AND DATE_SUB('2023-04-24', 9) THEN private_note_cnt ELSE 0 END) AS Week4_private_note_cnt 
     
     , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 8) AND DATE_SUB('2023-04-24', 2) THEN reply_cnt ELSE 0 END) AS Week5_reply_cnt
     , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-04-24', 8) AND DATE_SUB('2023-04-24', 2) THEN private_note_cnt ELSE 0 END) AS Week5_private_note_cnt  
    FROM
        (SELECT Date(created_time) as dt, ticket_id
        FROM freshdesk.properties
        WHERE year IN (year(DATE_SUB('2023-04-24', 36)) , year(DATE_SUB('2023-04-24', 2)))
        AND month >= month(DATE_SUB('2023-04-24', 36)) AND month <= month(DATE_SUB('2023-04-24', 2))
        AND Date(created_time) BETWEEN DATE_SUB('2023-04-24', 36) AND DATE_SUB('2023-04-24', 2)
        AND `group` IN ('Risk Team', 'security', 'Cybercell', 'FRA-Escalations', 'Blocked Fraud', 'Lien Email', 'NPCI Escalations')
        GROUP BY Date(created_time), ticket_id)A
    INNER JOIN
        (SELECT from_unixtime(unix_timestamp(`date` ,'yyyyMMdd'), 'yyyy-MM-dd') as dt, ticket_id
        , CASE WHEN performer_id = 40094141204 THEN 'Akash Shinde'
            WHEN performer_id = 40096302442 THEN 'arifa.fathima'
            WHEN performer_id = 40098326974 THEN 'ashmita.maheshwari'
            WHEN performer_id = 40068659687 THEN 'imran.khan'
            WHEN performer_id = 40079215228 THEN 'pooja.rajangam'
            WHEN performer_id = 40097988320 THEN 'sharath.ls'
            WHEN performer_id = 40080532390 THEN 'vivek.gowra'
            WHEN performer_id = 40139679443 THEN 'BhanuPrasad N'
            WHEN performer_id = 40134990405 THEN 'karthik.hiremath'
            WHEN performer_id = 40071150299 THEN 'anushka.prabha'
            WHEN performer_id = 40070159259 THEN 'Prasad Patil'
            WHEN performer_id = 40031538049 THEN 'alice'
            WHEN performer_id = 40062605741 THEN 'fra-automation' END AS agent_name 
        , 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-04-24', 36)
        AND performer_id IN (40094141204, 40096302442, 40098326974, 40068659687, 40079215228, 40097988320, 40080532390, 40139679443, 40134990405, 40071150299, 40070159259, 40031538049, 40062605741)
        GROUP BY from_unixtime(unix_timestamp(`date` ,'yyyyMMdd'), 'yyyy-MM-dd'), ticket_id
        , CASE WHEN performer_id = 40094141204 THEN 'Akash Shinde'
            WHEN performer_id = 40096302442 THEN 'arifa.fathima'
            WHEN performer_id = 40098326974 THEN 'ashmita.maheshwari'
            WHEN performer_id = 40068659687 THEN 'imran.khan'
            WHEN performer_id = 40079215228 THEN 'pooja.rajangam'
            WHEN performer_id = 40097988320 THEN 'sharath.ls'
            WHEN performer_id = 40080532390 THEN 'vivek.gowra'
            WHEN performer_id = 40139679443 THEN 'BhanuPrasad N'
            WHEN performer_id = 40134990405 THEN 'karthik.hiremath'
            WHEN performer_id = 40071150299 THEN 'anushka.prabha'
            WHEN performer_id = 40070159259 THEN 'Prasad Patil' 
            WHEN performer_id = 40031538049 THEN 'alice'
            WHEN performer_id = 40062605741 THEN 'fra-automation' END)C
    ON A.ticket_id = C.ticket_id AND A.dt <= C.dt
    GROUP BY C.agent_name)Q
ON P.agent_name = Q.agent_name
ORDER BY P.agent_name
content_copyCOPY