%jdbc(hive)
set tez.queue.name=user_transaction_statement;
set hive.execution.engine=tez;
-- CASE 15
SELECT '2023-05-22' AS run_date_monday, `group` as group_name, source
, COUNT(DISTINCT CASE WHEN created_time BETWEEN DATE_SUB('2023-05-22', 36) AND DATE_SUB('2023-05-22', 30) THEN ticket_id ELSE NULL END) AS Week1_tickets
, COUNT(DISTINCT CASE WHEN created_time BETWEEN DATE_SUB('2023-05-22', 29) AND DATE_SUB('2023-05-22', 23) THEN ticket_id ELSE NULL END) AS Week2_tickets
, COUNT(DISTINCT CASE WHEN created_time BETWEEN DATE_SUB('2023-05-22', 22) AND DATE_SUB('2023-05-22', 16) THEN ticket_id ELSE NULL END) AS Week3_tickets
, COUNT(DISTINCT CASE WHEN created_time BETWEEN DATE_SUB('2023-05-22', 15) AND DATE_SUB('2023-05-22', 9) THEN ticket_id ELSE NULL END) AS Week4_tickets
, COUNT(DISTINCT CASE WHEN created_time BETWEEN DATE_SUB('2023-05-22', 8) AND DATE_SUB('2023-05-22', 2) THEN ticket_id ELSE NULL END) AS Week5_tickets
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`, source
Comments