Repository navigation
Expand file tree
/
Copy pathev_charging_network.sql
More file actions
128 lines (117 loc) · 5.87 KB
/
Copy pathev_charging_network.sql
File metadata and controls
128 lines (117 loc) · 5.87 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
127
128
/*
* Dremio "Messy Data" Challenge: EV Charging Network
*
* Scenario:
* EV chargers logs.
* 3 Tables: STATIONS (Meta), CHARGERS (Device), SESSIONS (Log).
*
* Objective for AI Agent:
* 1. Join Tables: Link SESSIONS to CHARGERS to get Connector Type.
* 2. Analyze KWh: Filter > 350 KWh implies error.
* 3. Calc Occupancy: Session Duration per Station.
*/
-------------------------------------------------------------------------------
-- 1. SETUP: Create Raw Folder
-------------------------------------------------------------------------------
CREATE FOLDER IF NOT EXISTS Green_Charge;
CREATE FOLDER IF NOT EXISTS Green_Charge.Stations;
-------------------------------------------------------------------------------
-- 2. DDL: Create Raw Tables (3 Tables)
-------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS Green_Charge.Stations.SITES (
SITE_ID VARCHAR,
LOCATION VARCHAR,
GRID_LIMIT_KW INT
);
CREATE TABLE IF NOT EXISTS Green_Charge.Stations.CHARGERS (
CHARGER_ID VARCHAR,
SITE_ID VARCHAR,
CONNECTOR_TYPE VARCHAR, -- 'CCS', 'J1772'
MAX_KW INT
);
CREATE TABLE IF NOT EXISTS Green_Charge.Stations.SESSIONS (
SESSION_ID VARCHAR,
CHARGER_ID VARCHAR,
START_TS TIMESTAMP,
END_TS TIMESTAMP,
KWH_DELIVERED DOUBLE
);
-------------------------------------------------------------------------------
-- 3. SEED DATA (50+ Records)
-------------------------------------------------------------------------------
-- SITES
INSERT INTO Green_Charge.Stations.SITES VALUES
('Site-1', 'Downtown', 500), ('Site-2', 'Mall', 200), ('Site-3', 'Highway', 1000);
-- CHARGERS
INSERT INTO Green_Charge.Stations.CHARGERS VALUES
('C-1', 'Site-1', 'CCS', 150), ('C-2', 'Site-1', 'J1772', 7),
('C-3', 'Site-2', 'CCS', 50), ('C-4', 'Site-2', 'CHAdeMO', 50),
('C-5', 'Site-3', 'Tesla', 250), ('C-6', 'Site-3', 'CCS', 350);
-- SESSIONS (50 Rows)
INSERT INTO Green_Charge.Stations.SESSIONS VALUES
('S-1', 'C-1', '2023-01-01 10:00:00', '2023-01-01 10:30:00', 25.0),
('S-2', 'C-2', '2023-01-01 11:00:00', '2023-01-01 13:00:00', 12.0),
('S-3', 'C-4', '2023-01-01 10:00:00', '2023-01-01 10:15:00', 9000.0), -- Spike
('S-4', 'C-5', '2023-01-01 12:00:00', '2023-01-01 18:00:00', 0.0), -- Idle
('S-5', 'C-1', '2023-01-01 10:00:00', '2023-01-01 11:00:00', 50.0), -- Overlap S-1
('S-6', 'C-1', '2023-01-01 10:30:00', '2023-01-01 11:30:00', 40.0),
('S-7', 'C-1', '2023-01-02 10:00:00', '2023-01-02 10:30:00', 30.0),
('S-8', 'C-1', '2023-01-02 11:00:00', '2023-01-02 11:30:00', 30.0),
('S-9', 'C-3', '2023-01-01 12:00:00', '2023-01-01 14:00:00', 10.0),
('S-10', 'C-3', '2023-01-01 15:00:00', '2023-01-01 16:00:00', 10.0),
('S-11', 'C-5', '2023-01-01 12:00:00', '2023-01-01 12:30:00', 40.0),
('S-12', 'C-5', '2023-01-01 13:00:00', '2023-01-01 13:30:00', 40.0),
('S-13', 'C-6', '2023-01-01 12:00:00', '2023-01-01 12:01:00', 0.1),
('S-14', 'C-6', '2023-01-01 12:00:00', '2023-01-01 12:00:01', 0.0),
('S-15', 'C-6', '2023-01-01 12:00:00', '2023-01-01 12:10:00', -10.0),
('S-16', 'C-6', '2023-01-01 12:00:00', NULL, 10.0),
('S-17', 'C-6', NULL, '2023-01-01 12:00:00', 10.0),
('S-18', 'C-6', '2023-01-01 12:00:00', '2023-01-01 11:00:00', 10.0),
('S-19', 'C-6', '2023-01-01 00:00:00', '2023-01-01 23:59:59', 1000.0),
('S-20', 'C-2', '2023-01-01 12:00:00', '2023-01-01 13:00:00', 20.0),
('S-21', 'C-2', '2023-01-01 12:00:00', '2023-01-01 13:00:00', 20.0),
('S-22', 'C-3', '2023-01-01 12:00:00', '2023-01-01 12:30:00', 20.0),
('S-23', 'C-3', '2023-01-01 12:00:00', '2023-01-01 12:30:00', 20.0), -- Dupe
('S-24', 'C-2', '2023-01-01 12:00:00', '2023-01-01 13:00:00', 100.0), -- Exceed Max
('S-25', 'C-1', '2023-01-01 10:00:00', '2023-01-01 10:15:00', 1.0),
('S-26', 'C-1', '2023-01-01 10:15:00', '2023-01-01 10:30:00', 1.0),
('S-27', 'C-1', '2023-01-01 10:30:00', '2023-01-01 10:45:00', 1.0),
('S-28', 'C-1', '2023-01-01 10:45:00', '2023-01-01 11:00:00', 1.0),
('S-29', 'C-1', '2023-01-01 08:00:00', '2023-01-01 18:00:00', 5.0),
('S-30', 'C-2', '2023-01-01 12:00:00', '2023-01-01 13:00:00', 1.0),
('S-31', 'C-2', '2023-01-01 14:00:00', '2023-01-01 15:00:00', 7.0),
('S-32', 'C-3', '2023-01-01 12:00:00', '2023-01-01 12:30:00', NULL),
('S-33', 'C-3', '2023-01-01 12:00:00', '2023-01-01 12:30:00', 50.0),
('S-34', 'C-3', '2023-01-01 12:00:00', '2023-01-01 12:30:00', 50.0),
('S-35', 'C-3', '2023-01-01 12:00:00', '2023-01-01 12:30:00', 50.0),
('S-36', 'C-3', '2023-01-01 12:00:00', '2023-01-01 12:30:00', 50.0),
('S-37', 'C-3', '2023-01-01 12:00:00', '2023-01-01 12:30:00', 50.0),
('S-38', 'C-3', '2023-01-01 12:00:00', '2023-01-01 12:30:00', 50.0),
('S-39', 'C-3', '2023-01-01 12:00:00', '2023-01-01 12:30:00', 50.0),
('S-40', 'C-3', '2023-01-01 12:00:00', '2023-01-01 12:30:00', 50.0),
('S-41', 'C-1', '2023-01-01 19:00:00', '2023-01-01 20:00:00', 40.0),
('S-42', 'C-1', '2023-01-01 21:00:00', '2023-01-01 22:00:00', 40.0),
('S-43', 'C-1', '2023-01-01 23:00:00', '2023-01-01 23:59:00', 40.0),
('S-44', 'C-2', '2023-01-01 19:00:00', '2023-01-01 20:00:00', 5.0),
('S-45', 'C-2', '2023-01-01 21:00:00', '2023-01-01 22:00:00', 5.0),
('S-46', 'C-6', '2023-01-01 19:00:00', '2023-01-01 20:00:00', 100.0),
('S-47', 'C-6', '2023-01-01 21:00:00', '2023-01-01 22:00:00', 100.0),
('S-48', 'C-6', '2023-01-01 22:00:00', '2023-01-01 22:10:00', 50.0),
('S-49', 'C-6', '2023-01-01 22:15:00', '2023-01-01 23:00:00', 80.0),
('S-50', 'C-6', '2023-01-02 00:00:00', '2023-01-02 01:00:00', 90.0);
-------------------------------------------------------------------------------
-- 4. EXAMPLE AI AGENT PROMPT
-------------------------------------------------------------------------------
/*
* Prompt to Copy/Paste into Dremio AI:
* "Monitor the charging network in Green_Charge.Stations.
*
* 1. Bronze: Raw View of SITES, CHARGERS, SESSIONS.
* 2. Silver:
* - Join: SESSIONS -> CHARGERS -> SITES.
* - Flag Faults: kWh=0 for >60min, or kWh > 150 (for CCS).
* 3. Gold:
* - Site Load: Daily total kWh per Site.
*
* Show the SQL."
*/