CASE 17 NEW

PHOTO EMBED

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

Saved by @shubhangi_burle 

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

-- CASE 17
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) AND C.agent_name = 'fra-automation' THEN C.ticket_cnt ELSE 0 END) AS Week1_fra_automation_closed
, SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 36) AND DATE_SUB('2023-05-22', 30) AND C.agent_name = 'alice' THEN C.ticket_cnt ELSE 0 END) AS Week1_cs_automation_closed
, SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 36) AND DATE_SUB('2023-05-22', 30) AND C.agent_name NOT IN ('fra-automation', 'alice') THEN C.ticket_cnt ELSE 0 END) AS Week1_manual_closed_cnt

, SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 29) AND DATE_SUB('2023-05-22', 23) AND C.agent_name = 'fra-automation' THEN C.ticket_cnt ELSE 0 END) AS Week2_fra_automation_closed 
, SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 29) AND DATE_SUB('2023-05-22', 23) AND C.agent_name = 'alice' THEN C.ticket_cnt ELSE 0 END) AS Week2_cs_automation_closed 
, SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 29) AND DATE_SUB('2023-05-22', 23) AND C.agent_name NOT IN ('fra-automation', 'alice') THEN C.ticket_cnt ELSE 0 END) AS Week2_manual_closed_cnt 

, SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 22) AND DATE_SUB('2023-05-22', 16) AND C.agent_name = 'fra-automation' THEN C.ticket_cnt ELSE 0 END) AS Week3_fra_automation_closed
, SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 22) AND DATE_SUB('2023-05-22', 16) AND C.agent_name = 'alice' THEN C.ticket_cnt ELSE 0 END) AS Week3_cs_automation_closed
, SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 22) AND DATE_SUB('2023-05-22', 16) AND C.agent_name NOT IN ('fra-automation', 'alice') THEN C.ticket_cnt ELSE 0 END) AS Week3_manual_closed_cnt
 
, SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 15) AND DATE_SUB('2023-05-22', 9) AND C.agent_name = 'fra-automation' THEN C.ticket_cnt ELSE 0 END) AS Week4_fra_automation_closed  
, SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 15) AND DATE_SUB('2023-05-22', 9) AND C.agent_name = 'alice' THEN C.ticket_cnt ELSE 0 END) AS Week4_cs_automation_closed  
, SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 15) AND DATE_SUB('2023-05-22', 9) AND C.agent_name NOT IN ('fra-automation', 'alice') THEN C.ticket_cnt ELSE 0 END) AS Week4_manual_closed_cnt  

, SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 8) AND DATE_SUB('2023-05-22', 2) AND C.agent_name = 'fra-automation' THEN C.ticket_cnt ELSE 0 END) AS Week5_fra_automation_closed  
, SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 8) AND DATE_SUB('2023-05-22', 2) AND C.agent_name = 'alice' THEN C.ticket_cnt ELSE 0 END) AS Week5_cs_automation_closed  
, SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 8) AND DATE_SUB('2023-05-22', 2) AND C.agent_name NOT IN ('fra-automation', 'alice') THEN C.ticket_cnt ELSE 0 END) AS Week5_manual_closed_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'))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 = 40062605741 THEN 'fra-automation'
    WHEN performer_id = 40031538049 THEN 'alice'
    END AS agent_name 
    , COUNT(*) AS ticket_cnt
    FROM freshdesk.activities 
    WHERE from_unixtime(unix_timestamp(`date` ,'yyyyMMdd'), 'yyyy-MM-dd') >= DATE_SUB('2023-05-22', 36)
    AND performer_id IN (40094141204, 40096302442, 40098326974, 40068659687, 40079215228, 40097988320, 40080532390, 40139679443, 40134990405, 40071150299, 40070159259, 40062605741, 40031538049)
    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 = 40062605741 THEN 'fra-automation'
    WHEN performer_id = 40031538049 THEN 'alice' END)C
ON A.ticket_id = C.ticket_id AND A.dt <= C.dt
GROUP BY group_name
content_copyCOPY