Case 3: group & agent level NEW
Tue Apr 04 2023 06:07:17 GMT+0000 (Coordinated Universal Time)
Saved by @shubhangi_burle
%jdbc(hive) set tez.queue.name=phonepe_verified; set hive.execution.engine=tez; -- CASE 3 COMBINED SELECT P.run_date_monday, P.group_name, 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 (SELECT '2023-05-22' AS run_date_monday, A.group_name, A.agent_name , SUM(CASE WHEN A.dt BETWEEN DATE_SUB('2023-05-22', 36) AND DATE_SUB('2023-05-22', 30) THEN 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 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 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 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 incoming_cnt ELSE 0 END) AS Week5_ticket_cnt FROM (SELECT `group` AS group_name, Date(created_time) as dt, ticket_id, `agent` AS agent_name, 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') 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', 'alice', 'fra-automation') GROUP BY `group`, Date(created_time), ticket_id, `agent`)A GROUP BY group_name, agent_name)P LEFT JOIN (SELECT '2023-05-22' AS run_date_monday, A.group_name, C.agent_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, `agent` AS agent_name, 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, `agent`)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-05-22', 36) AND performer_id IN (40094141204, 40096302442, 40098326974, 40068659687, 40079215228, 40097988320, 40080532390, 40139679443, 40134990405, 40071150299, 40070159259) 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 A.group_name, C.agent_name)Q ON P.run_date_monday = Q.run_date_monday AND P.group_name = Q.group_name AND P.agent_name = Q.agent_name ORDER BY P.run_date_monday, P.group_name, P.agent_name
Comments