%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
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter