Repository navigation
Expand file tree
/
Copy pathcustomer_support_tickets.sql
More file actions
126 lines (115 loc) · 6.24 KB
/
Copy pathcustomer_support_tickets.sql
File metadata and controls
126 lines (115 loc) · 6.24 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
/*
* Dremio "Messy Data" Challenge: Customer Support Tickets
*
* Scenario:
* Helpdesk data.
* 3 Tables: AGENTS (Staff), CUSTOMERS (Users), TICKETS (Issues).
*
* Objective for AI Agent:
* 1. Join Tables: TICKETS -> AGENTS and CUSTOMERS.
* 2. SLA Check: Calc 'Response_Hours' from Created/Resolved TS.
* 3. Text Analysis: Parse 'Priority' from Description keywords.
*/
-------------------------------------------------------------------------------
-- 1. SETUP: Create Raw Folder
-------------------------------------------------------------------------------
CREATE FOLDER IF NOT EXISTS Zendesk_Clone;
CREATE FOLDER IF NOT EXISTS Zendesk_Clone.Support;
-------------------------------------------------------------------------------
-- 2. DDL: Create Raw Tables (3 Tables)
-------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS Zendesk_Clone.Support.AGENTS (
AGENT_ID VARCHAR,
NAME VARCHAR,
LEVEL VARCHAR -- 'L1', 'L2'
);
CREATE TABLE IF NOT EXISTS Zendesk_Clone.Support.CUSTOMERS (
CUST_ID VARCHAR,
EMAIL VARCHAR,
TIER VARCHAR -- 'Free', 'VIP'
);
CREATE TABLE IF NOT EXISTS Zendesk_Clone.Support.TICKETS (
TICKET_ID VARCHAR,
CUST_ID VARCHAR,
AGENT_ID VARCHAR,
CREATED_TS TIMESTAMP,
RESOLVED_TS TIMESTAMP,
DESCRIPTION VARCHAR
);
-------------------------------------------------------------------------------
-- 3. SEED DATA (50+ Records)
-------------------------------------------------------------------------------
-- AGENTS
INSERT INTO Zendesk_Clone.Support.AGENTS VALUES
('A-1', 'Alice', 'L1'), ('A-2', 'Bob', 'L2');
-- CUSTOMERS
INSERT INTO Zendesk_Clone.Support.CUSTOMERS VALUES
('C-1', 'user1@test.com', 'Free'), ('C-2', 'vip@test.com', 'VIP');
-- TICKETS (50 Rows)
INSERT INTO Zendesk_Clone.Support.TICKETS VALUES
('T-1', 'C-1', 'A-1', '2023-01-01 10:00:00', '2023-01-01 11:00:00', 'Login issue'),
('T-2', 'C-1', 'A-1', '2023-01-01 12:00:00', '2023-01-01 13:00:00', 'Reset password'),
('T-3', 'C-1', 'A-1', '2023-01-01 14:00:00', '2023-01-01 15:00:00', 'Billing'),
('T-4', 'C-1', 'A-1', '2023-01-01 16:00:00', '2023-01-01 17:00:00', 'URGENT outage'),
('T-5', 'C-2', 'A-2', '2023-01-01 10:00:00', '2023-01-01 10:05:00', 'VIP Request'),
('T-6', 'C-2', 'A-2', '2023-01-01 11:00:00', '2023-01-01 11:05:00', 'Help'),
('T-7', 'C-1', 'A-1', '2023-01-02 10:00:00', '2023-01-02 11:00:00', 'Bug'),
('T-8', 'C-1', 'A-1', '2023-01-02 12:00:00', '2023-01-02 13:00:00', 'Bug'),
('T-9', 'C-1', 'A-1', '2023-01-02 14:00:00', '2023-01-02 15:00:00', 'Bug'),
('T-10', 'C-1', 'A-1', '2023-01-02 16:00:00', '2023-01-02 17:00:00', 'Bug'),
('T-11', 'C-1', NULL, '2023-01-03 10:00:00', NULL, 'Unassigned'), -- Open
('T-12', 'C-1', 'A-1', '2023-01-03 10:00:00', '2023-01-02 10:00:00', 'Time Travel'), -- Error
('T-13', 'C-1', 'A-1', '2023-01-03 12:00:00', '2023-01-03 13:00:00', 'Issue'),
('T-14', 'C-1', 'A-1', '2023-01-03 13:00:00', '2023-01-03 14:00:00', 'Issue'),
('T-15', 'C-1', 'A-1', '2023-01-04 10:00:00', '2023-01-04 11:00:00', 'Issue'),
('T-16', 'C-1', 'A-1', '2023-01-04 11:00:00', '2023-01-04 12:00:00', 'Issue'),
('T-17', 'C-1', 'A-1', '2023-01-04 12:00:00', '2023-01-04 13:00:00', 'Issue'),
('T-18', 'C-1', 'A-1', '2023-01-04 13:00:00', '2023-01-04 14:00:00', 'Issue'),
('T-19', 'C-1', 'A-1', '2023-01-04 14:00:00', '2023-01-04 15:00:00', 'Issue'),
('T-20', 'C-1', 'A-1', '2023-01-04 15:00:00', '2023-01-04 16:00:00', 'Issue'),
('T-21', 'C-1', 'A-1', '2023-01-05 10:00:00', '2023-01-05 11:00:00', 'Issue'),
('T-22', 'C-1', 'A-1', '2023-01-05 11:00:00', '2023-01-05 12:00:00', 'Issue'),
('T-23', 'C-1', 'A-1', '2023-01-05 12:00:00', '2023-01-05 13:00:00', 'Issue'),
('T-24', 'C-1', 'A-1', '2023-01-05 13:00:00', '2023-01-05 14:00:00', 'Issue'),
('T-25', 'C-1', 'A-1', '2023-01-05 14:00:00', '2023-01-05 15:00:00', 'Issue'),
('T-26', 'C-1', 'A-1', '2023-01-05 15:00:00', '2023-01-05 16:00:00', 'Issue'),
('T-27', 'C-1', 'A-1', '2023-01-06 10:00:00', '2023-01-06 11:00:00', 'Issue'),
('T-28', 'C-1', 'A-1', '2023-01-06 11:00:00', '2023-01-06 12:00:00', 'Issue'),
('T-29', 'C-1', 'A-1', '2023-01-06 12:00:00', '2023-01-06 13:00:00', 'Issue'),
('T-30', 'C-1', 'A-1', '2023-01-06 13:00:00', '2023-01-06 14:00:00', 'Issue'),
('T-31', 'C-1', 'A-1', '2023-01-06 14:00:00', '2023-01-06 15:00:00', 'Issue'),
('T-32', 'C-1', 'A-1', '2023-01-06 15:00:00', '2023-01-06 16:00:00', 'Issue'),
('T-33', 'C-1', 'A-1', '2023-01-07 10:00:00', '2023-01-07 11:00:00', 'Issue'),
('T-34', 'C-1', 'A-1', '2023-01-07 11:00:00', '2023-01-07 12:00:00', 'Issue'),
('T-35', 'C-1', 'A-1', '2023-01-07 12:00:00', '2023-01-07 13:00:00', 'Issue'),
('T-36', 'C-1', 'A-1', '2023-01-07 13:00:00', '2023-01-07 14:00:00', 'Issue'),
('T-37', 'C-1', 'A-1', '2023-01-07 14:00:00', '2023-01-07 15:00:00', 'Issue'),
('T-38', 'C-1', 'A-1', '2023-01-07 15:00:00', '2023-01-07 16:00:00', 'Issue'),
('T-39', 'C-1', 'A-1', '2023-01-08 10:00:00', '2023-01-08 11:00:00', 'Issue'),
('T-40', 'C-1', 'A-1', '2023-01-08 11:00:00', '2023-01-08 12:00:00', 'Issue'),
('T-41', 'C-1', 'A-1', '2023-01-08 12:00:00', '2023-01-08 13:00:00', 'Issue'),
('T-42', 'C-1', 'A-1', '2023-01-08 13:00:00', '2023-01-08 14:00:00', 'Issue'),
('T-43', 'C-1', 'A-1', '2023-01-08 14:00:00', '2023-01-08 15:00:00', 'Issue'),
('T-44', 'C-1', 'A-1', '2023-01-08 15:00:00', '2023-01-08 16:00:00', 'Issue'),
('T-45', 'C-1', 'A-1', '2023-01-09 10:00:00', '2023-01-09 11:00:00', 'Issue'),
('T-46', 'C-1', 'A-1', '2023-01-09 11:00:00', '2023-01-09 12:00:00', 'Issue'),
('T-47', 'C-1', 'A-1', '2023-01-09 12:00:00', '2023-01-09 13:00:00', 'Issue'),
('T-48', 'C-1', 'A-1', '2023-01-09 13:00:00', '2023-01-09 14:00:00', 'Issue'),
('T-49', 'C-1', 'A-1', '2023-01-09 14:00:00', '2023-01-09 15:00:00', 'Issue'),
('T-50', 'C-1', 'A-1', '2023-01-09 15:00:00', '2023-01-09 16:00:00', 'Issue');
-------------------------------------------------------------------------------
-- 4. EXAMPLE AI AGENT PROMPT
-------------------------------------------------------------------------------
/*
* Prompt to Copy/Paste into Dremio AI:
* "Improve support stats in Zendesk_Clone.Support.
*
* 1. Bronze: Raw View of TICKETS, AGENTS, CUSTOMERS.
* 2. Silver:
* - Join: TICKETS -> AGENTS.
* - SLA Breach: If (Resolved - Created) > 4 Hours (for 'VIP' > 1 Hour).
* 3. Gold:
* - Agent Scorecard: Avg Resolution Time per Agent.
*
* Show the SQL."
*/