· 6 years ago · May 30, 2019, 03:38 PM
1
2-- close_one_bug.sql
3
4select interpolate('cryptobank', cast('2020-06-01' as datetime));
5
6INSERT INTO tasks_history (task_id,build_id,status) VALUES (28,150,'closed');
7
8select interpolate('cryptobank', cast('2020-06-01' as datetime));
9
10delete from tasks_history where build_id = 150;
11-- close_two_bugs.sql
12
13select interpolate('MySQL', cast('2020-06-01' as datetime));
14
15INSERT INTO tasks_history (task_id,build_id,status) VALUES (6,50,'closed');
16
17select interpolate('MySQL', cast('2020-06-01' as datetime));
18
19INSERT INTO tasks_history (task_id,build_id,status) VALUES (27,50,'closed');
20
21select interpolate('MySQL', cast('2020-06-01' as datetime));
22
23delete from tasks_history where build_id = 50;
24-- open_one_bug.sql
25
26select interpolate('SirKaras', cast('2020-06-01' as datetime));
27
28INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id,type) VALUES (23,'SirKaras','Added manually bug','-','A',6,10,'bug');
29INSERT INTO bug_cause VALUES (23,19);
30INSERT INTO tasks_history (task_id,build_id,status) VALUES (23,1047,'opened');
31
32select interpolate('SirKaras', cast('2020-06-01' as datetime));
33
34DELETE FROM tasks_history where task_id = 23;
35DELETE FROM bug_cause where bug_id = 23;
36DELETE FROM task where id = 23;
37
38-- proc.sql
39
40-- 1. ╨б╨┤╨╡╨╗╨░╨╣╤В╨╡ ╨┐╤А╨╛╤Ж╨╡╨┤╤Г╤А╤Г, ╨║╨╛╤В╨╛╤А╨░╤П ╨┤╨╗╤П ╨┐╤А╨╛╨╡╨║╤В╨░ ╨╕ ╤В╨╛╤З╨║╨╕ ╨╛╤В╤Б╤З╨╡╤В╨░ ╨╛╤Ж╨╡╨╜╨╕╨▓╨░╨╡╤В, ╨║╨╛╨│╨┤╨░ ╨╖╨░╨▓╨╡╤А╤И╨╕╤В╤Б╤П ╨┐╤А╨╛╨╡╨║╤В
41
42use dashboard;
43
44DROP FUNCTION IF EXISTS interpolate;
45DELIMITER $$
46CREATE FUNCTION interpolate(project_name VARCHAR(256), start_date DATETIME) RETURNS DATETIME
47 DETERMINISTIC
48BEGIN
49 DECLARE opened_bugs INT;
50 DECLARE closed_bugs INT;
51 DECLARE time_per_bug TIME;
52 DECLARE time_to_close_all_bugs TIME;
53 DECLARE result DATETIME;
54
55 set @opened_bugs = ifnull((select count(*)
56 from bug_opened
57 join task
58 on task.id = bug_opened.task_id
59 join build
60 on bug_opened.build_id = build.id
61 where task.type = 'bug' and task.project_name = project_name and build.date <= start_date)
62 , 0);
63 set @closed_bugs = ifnull((select count(*)
64 from task_status
65 join task
66 on task.id = task_status.task_id
67 join build
68 on task_status.build_id = build.id
69 where task.type = 'bug' and task.project_name = project_name and status = 'closed' and build.date <= start_date
70 ), 0);
71 -- time / closed_bugs
72 set @time_per_bug = timediff(start_date,(SELECT(min(date)) from build where project_name = project_name)) / cast(@closed_bugs as decimal(40,20));
73 -- milliseconds required to close all opened bugs
74 set @time_to_close_all_bugs = (@opened_bugs - @closed_bugs) * @time_per_bug;
75 set @result = addtime(start_date, @time_to_close_all_bugs);
76 RETURN @result;
77END $$
78-- query_1.sql
79
80-- 1. ╨Э╨░╨╣╤В╨╕ ╤Б╨┐╨╕╤Б╨╛╨║ ╨╛╤В╨║╤А╤Л╤В╤Л╤Е ╨▒╨░╨│╨╛╨▓ ╨▓ ╨╖╨░╨┤╨░╨╜╨╜╨╛╨╝ ╨┐╤А╨╛╨╡╨║╤В╨╡ ╨╕ ╨╛╤В╨▓╨╡╤В╤Б╤В╨▓╨╡╨╜╨╜╤Л╤Е ╨╖╨░ ╨╕╤Е ╨╕╤Б╨┐╤А╨░╨▓╨╗╨╡╨╜╨╕╨╡ ╨┐╤А╨╛╨│╤А╨░╨╝╨╝╨╕╤Б╤В╨╛╨▓
81
82SELECT task_status.task_id,task.project_name,task.title,status,employee.full_name,employee.email
83FROM task_status
84JOIN task
85 ON task_status.task_id = task.id
86JOIN employee
87 ON task.developer_id = employee.id
88WHERE task_status.status IN ('opened', 'solved') and task.type = 'bug';
89-- query_2.sql
90
91-- 2. ╨Э╨░╨╣╤В╨╕ ╨╝╨╜╨╛╨╢╨╡╤Б╤В╨▓╨╛ ╤Д╨╕╤З, ╨║╨╛╤В╨╛╤А╤Л╨╡ ╨╝╨╛╨╢╨╜╨╛ ╨▓╤Л╨║╨╕╨╜╤Г╤В╤М ╨┤╨╗╤П ╤В╨╛╨│╨╛ ╤З╤В╨╛╨▒ ╨╕╤Б╤З╨╡╨╖╨╗╨╕ ╨▓╤Б╨╡ ╨▒╨░╨│╨╕
92-- ╨╕ ╨┐╨╛╤В╨╛╨╝ ╨╜╨░╨╣╤В╨╕ ╨╝╨╜╨╛╨╢╨╡╤Б╤В╨▓╨╛ ╤Д╨╕╤З, ╨║╨╛╤В╨╛╤А╤Л╨╡ ╨┐╨╛╤Б╨╗╨╡ ╤Н╤В╨╛╨│╨╛ ╨╛╤Б╤В╨░╨╜╤Г╤В╤Б╤П ╨▓ ╨┐╤А╨╛╨╡╨║╤В╨╡
93
94-- query 2.1 - features to remove
95SELECT task.id,task.project_name,task.title,task.description
96FROM task WHERE task.id IN (
97 SELECT feature_id
98 FROM task_status
99 JOIN bug_cause
100 ON task_status.task_id = bug_cause.bug_id
101 WHERE task_status.status IN ('opened', 'solved')
102) AND type = 'feature';
103
104-- 2.2 features will be left
105SELECT task.id,task.project_name,task.title,task.description
106FROM task WHERE task.id NOT IN (
107 SELECT feature_id
108 FROM task_status
109 JOIN bug_cause
110 ON task_status.task_id = bug_cause.bug_id
111 WHERE task_status.status IN ('opened', 'solved')
112) AND type = 'feature';
113-- query_3.sql
114
115-- 3. ╨Э╨░╨╣╤В╨╕ ╨▓╤Б╨╡ "╨┐╨╡╤А╨╡╨╛╤В╨║╤А╤Л╤В╤Л╨╡" ╨╖╨░╨┤╨░╤З╨╕, ╤В╨╛ ╨╡╤Б╤В╤М ╤В╨░╨║╨╕╨╡, ╨║╨╛╤В╨╛╤А╤Л╨╡ ╨╜╨░╤Е╨╛╨┤╤П╤В╤Б╤П ╨▓ ╤Б╨╛╤Б╤В╨╛╤П╨╜╨╕╨╕ "╨╛╤В╨║╤А╤Л╤В╨░", ╨╜╨╛ ╤А╨░╨╜╨╡╨╡ ╨▒╤Л╨╗╨╕ ╨▓ ╤Б╨╛╤Б╤В╨╛╤П╨╜╨╕╨╕ "╤А╨╡╤И╨╡╨╜╨░"
116
117SELECT DISTINCT a.task_id,task.project_name,task.title,task.description
118FROM tasks_history AS a
119JOIN tasks_history AS b
120 ON a.task_id = b.task_id AND a.build_id < b.build_id
121JOIN tasks_history AS c
122 ON b.task_id = c.task_id AND b.build_id < c.build_id
123JOIN task
124 ON a.task_id = task.id
125WHERE a.status = 'opened' AND b.status = 'solved' AND c.status = 'opened';
126-- reset_database.sql
127
128DROP DATABASE dashboard;
129CREATE DATABASE dashboard;
130use dashboard;
131
132DROP TABLE IF EXISTS bug_cause;
133
134CREATE TABLE bug_cause (
135 bug_id INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
136 feature_id INTEGER NULL DEFAULT NULL,
137 PRIMARY KEY (bug_id)
138);
139
140DROP TABLE IF EXISTS tasks_history;
141
142CREATE TABLE tasks_history (
143 task_id INTEGER NOT NULL,
144 build_id INTEGER NOT NULL,
145 status VARCHAR(256) NOT NULL,
146 PRIMARY KEY (task_id,build_id,status)
147);
148
149DROP TABLE IF EXISTS build;
150
151CREATE TABLE build (
152 id INTEGER NULL AUTO_INCREMENT NOT NULL,
153 project_name VARCHAR(256) NOT NULL,
154 date TIMESTAMP NOT NULL,
155 PRIMARY KEY (id)
156);
157
158DROP TABLE IF EXISTS manager_assignment;
159
160CREATE TABLE manager_assignment (
161 project_name VARCHAR(256) NOT NULL UNIQUE,
162 manager_id INTEGER NOT NULL,
163 PRIMARY KEY (project_name)
164);
165
166DROP TABLE IF EXISTS task;
167
168CREATE TABLE task (
169 id INTEGER NOT NULL AUTO_INCREMENT,
170 project_name VARCHAR(256) NOT NULL,
171 title VARCHAR(256) NOT NULL,
172 description MEDIUMTEXT NOT NULL,
173 priority CHAR NOT NULL,
174 developer_id INTEGER NULL DEFAULT NULL,
175 tester_id INTEGER NULL DEFAULT NULL,
176 type VARCHAR(64) DEFAULT 'feature',
177 PRIMARY KEY (id)
178);
179
180DROP TABLE IF EXISTS project;
181
182CREATE TABLE project (
183 name VARCHAR(256) NOT NULL,
184 start_time TIMESTAMP NOT NULL,
185 end_time TIMESTAMP NOT NULL,
186 PRIMARY KEY (name)
187);
188
189DROP TABLE IF EXISTS employee;
190
191CREATE TABLE employee (
192 id INTEGER NOT NULL,
193 full_name VARCHAR(256) NOT NULL,
194 email VARCHAR(256) NOT NULL,
195 job VARCHAR(64) NOT NULL,
196 PRIMARY KEY (id)
197);
198
199-- ---
200-- Constraints
201-- ---
202
203ALTER TABLE task ADD CHECK (priority = 'A');
204ALTER TABLE employee ADD CHECK (job IN ('programmer', 'tester', 'manager'));
205ALTER TABLE tasks_history ADD CHECK (status IN ('opened', 'solved', 'closed'));
206ALTER TABLE task ADD CHECK (type IN ('feature', 'bug'));
207
208-- ---
209-- Foreign Keys
210-- ---
211
212ALTER TABLE manager_assignment ADD FOREIGN KEY (project_name) REFERENCES project (name);
213ALTER TABLE manager_assignment ADD FOREIGN KEY (manager_id) REFERENCES employee (id);
214ALTER TABLE tasks_history ADD FOREIGN KEY (task_id) REFERENCES task (id);
215ALTER TABLE tasks_history ADD FOREIGN KEY (build_id) REFERENCES build (id);
216ALTER TABLE task ADD FOREIGN KEY (project_name) REFERENCES project (name);
217ALTER TABLE task ADD FOREIGN KEY (developer_id) REFERENCES employee (id);
218ALTER TABLE task ADD FOREIGN KEY (tester_id) REFERENCES employee (id);
219ALTER TABLE build ADD FOREIGN KEY (project_name) REFERENCES project (name);
220ALTER TABLE bug_cause ADD FOREIGN KEY (bug_id) REFERENCES task (id);
221ALTER TABLE bug_cause ADD FOREIGN KEY (feature_id) REFERENCES task (id);
222
223-- ---
224-- Test Data
225-- ---
226
227-- ---
228-- employee
229-- ---
230
231INSERT INTO employee (id,full_name,email,job) VALUES (3,'Bogdan Laskoviy','lasqa@gde.strim','manager');
232INSERT INTO employee (id,full_name,email,job) VALUES (4,'Harry Potter','harry@hogwartz.mg','manager');
233INSERT INTO employee (id,full_name,email,job) VALUES (5,'Walk Wolf','wolf@walkstreet.mn','manager');
234
235INSERT INTO employee (id,full_name,email,job) VALUES (1,'Bogdan Degtyariov','bogdan@mail.cc','programmer');
236INSERT INTO employee (id,full_name,email,job) VALUES (6,'Vasyan Vasevich','vasya@mail.com','programmer');
237INSERT INTO employee (id,full_name,email,job) VALUES (7,'Hikk Hikkovich','hikka@2ch.hk','programmer');
238INSERT INTO employee (id,full_name,email,job) VALUES (8,'Mikhail Mikhailovich','misha293@mail.cc','programmer');
239
240INSERT INTO employee (id,full_name,email,job) VALUES (2,'Matthew Montgomery','mm@at.mail','tester');
241INSERT INTO employee (id,full_name,email,job) VALUES (9,'Tester Breaker','_`~\'@mail.cc','tester');
242INSERT INTO employee (id,full_name,email,job) VALUES (10,'Lex Lowflex','lex@yandex.ru','tester');
243
244-- ---
245-- projects
246-- ---
247
248INSERT INTO project (name,start_time,end_time) VALUES ('MySQL','13.04.2020','30.06.2020');
249INSERT INTO project (name,start_time,end_time) VALUES ('cryptobank','22.04.2020','19.06.2020');
250INSERT INTO project (name,start_time,end_time) VALUES ('SirKaras','16.04.2020','29.06.2020');
251
252-- ---
253-- manager assignments
254-- ---
255
256INSERT INTO manager_assignment (project_name,manager_id) VALUES ('MySQL',3);
257INSERT INTO manager_assignment (project_name,manager_id) VALUES ('cryptobank',4);
258INSERT INTO manager_assignment (project_name,manager_id) VALUES ('SirKaras',5);
259
260-- ---
261-- tasks
262-- ---
263
264-- (id,project_name,title,description,priority,developer_id,tester_id)
265INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id) VALUES (1,'MySQL','Array storage','Now it can contain something','A',1,2);
266INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id) VALUES (2,'MySQL','Init git','Create git repository','A',6,9);
267INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id,type) VALUES (3,'MySQL','Storage doesn\'t work','It can\'t store anything','A',7,10,'bug');
268INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id) VALUES (4,'MySQL','Create server','Create server to host the database','A',8,2);
269INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id) VALUES (5,'MySQL','Create web interface','Create convenient nice looking web interface like phpMyAdmin','A',1,9);
270INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id,type) VALUES (6,'MySQL','Fix server not working on Windows','Fix server not working on Windows please','A',6,10,'bug');
271INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id,type) VALUES (24,'MySQL','Host git server','-','A',6,10,'bug');
272INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id,type) VALUES (25,'MySQL','Push repo','-','A',6,10,'bug');
273INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id,type) VALUES (26,'MySQL','Pull repo','-','A',8,10,'bug');
274INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id,type) VALUES (27,'MySQL','Use TypeScript','-','A',6,10,'bug');
275INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id,type) VALUES (31,'MySQL','Additional bug','-','A',6,10,'bug');
276
277INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id) VALUES (7,'cryptobank','Change bolivars to roubles','Change bolivars to roubles in appropriate amount','A',7,2);
278INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id) VALUES (8,'cryptobank','Change roubles dollars','Change bolivars to dollars in appropriate amount','A',8,2);
279INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id) VALUES (9,'cryptobank','Card transfer','Transfer money to bank card','A',1,2);
280INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id) VALUES (10,'cryptobank','Mobile app','Create stylish android app','A',6,9);
281INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id,type) VALUES (11,'cryptobank','Mobile app is bad','Android app is not cool, i want it to be on my new vedroid','A',7,10,'bug');
282INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id,type) VALUES (12,'cryptobank','Fix card jacking','Jack fixing cards','A',8,10,'bug');
283INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id,type) VALUES (28,'cryptobank','Update dollars course','-','A',8,10,'bug');
284INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id,type) VALUES (30,'cryptobank','Additional bug','-','A',8,10,'bug');
285
286INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id) VALUES (13,'SirKaras','Catch fish','Catch some fish to start project','A',1,2);
287INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id) VALUES (14,'SirKaras','Make music','Make some cool music using catched fish','A',6,9);
288INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id) VALUES (15,'SirKaras','Make clip','Make some clip not using any fish','A',7,10);
289INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id,type) VALUES (16,'SirKaras','Make clips longer','Why are clips so short? Make it longer!','A',8,9,'bug');
290INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id,type) VALUES (17,'SirKaras','Make music better','Why music so bad? Make it better!','A',1,10,'bug');
291INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id,type) VALUES (18,'SirKaras','Make more music','Make more music using more fish','A',6,10,'bug');
292INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id) VALUES (19,'SirKaras','Make one more music','Make one also track I always wondered to make','A',7,9);
293INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id,type) VALUES (20,'SirKaras','Catch better fish','-','A',6,10,'bug');
294INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id,type) VALUES (21,'SirKaras','Catch more fish','-','A',6,10,'bug');
295INSERT INTO task (id,project_name,title,description,priority,developer_id,tester_id,type) VALUES (29,'SirKaras','Additional bug','-','A',6,10,'bug');
296
297-- ---
298-- builds
299-- ---
300
301INSERT INTO build (id,project_name,date) VALUES (1, 'MySQL','2020-04-13');
302INSERT INTO build (id,project_name,date) VALUES (2, 'MySQL','2020-04-14');
303INSERT INTO build (id,project_name,date) VALUES (3, 'MySQL','2020-04-15');
304INSERT INTO build (id,project_name,date) VALUES (4, 'MySQL','2020-04-16');
305INSERT INTO build (id,project_name,date) VALUES (5, 'MySQL','2020-04-17');
306INSERT INTO build (id,project_name,date) VALUES (6, 'MySQL','2020-04-18');
307INSERT INTO build (id,project_name,date) VALUES (7, 'MySQL','2020-04-19');
308INSERT INTO build (id,project_name,date) VALUES (8, 'MySQL','2020-04-20');
309INSERT INTO build (id,project_name,date) VALUES (9, 'MySQL','2020-04-21');
310INSERT INTO build (id,project_name,date) VALUES (10,'MySQL','2020-04-22');
311INSERT INTO build (id,project_name,date) VALUES (11,'MySQL','2020-04-23');
312INSERT INTO build (id,project_name,date) VALUES (12,'MySQL','2020-04-24');
313INSERT INTO build (id,project_name,date) VALUES (13,'MySQL','2020-04-25');
314INSERT INTO build (id,project_name,date) VALUES (14,'MySQL','2020-04-26');
315INSERT INTO build (id,project_name,date) VALUES (15,'MySQL','2020-04-27');
316INSERT INTO build (id,project_name,date) VALUES (16,'MySQL','2020-04-28');
317INSERT INTO build (id,project_name,date) VALUES (17,'MySQL','2020-04-29');
318INSERT INTO build (id,project_name,date) VALUES (18,'MySQL','2020-04-30');
319INSERT INTO build (id,project_name,date) VALUES (19,'MySQL','2020-05-01');
320INSERT INTO build (id,project_name,date) VALUES (20,'MySQL','2020-05-02');
321INSERT INTO build (id,project_name,date) VALUES (21,'MySQL','2020-05-03');
322INSERT INTO build (id,project_name,date) VALUES (22,'MySQL','2020-05-04');
323INSERT INTO build (id,project_name,date) VALUES (23,'MySQL','2020-05-05');
324INSERT INTO build (id,project_name,date) VALUES (24,'MySQL','2020-05-06');
325INSERT INTO build (id,project_name,date) VALUES (25,'MySQL','2020-05-07');
326INSERT INTO build (id,project_name,date) VALUES (26,'MySQL','2020-05-08');
327INSERT INTO build (id,project_name,date) VALUES (27,'MySQL','2020-05-09');
328INSERT INTO build (id,project_name,date) VALUES (28,'MySQL','2020-05-10');
329INSERT INTO build (id,project_name,date) VALUES (29,'MySQL','2020-05-11');
330INSERT INTO build (id,project_name,date) VALUES (30,'MySQL','2020-05-12');
331INSERT INTO build (id,project_name,date) VALUES (31,'MySQL','2020-05-13');
332INSERT INTO build (id,project_name,date) VALUES (32,'MySQL','2020-05-14');
333INSERT INTO build (id,project_name,date) VALUES (33,'MySQL','2020-05-15');
334INSERT INTO build (id,project_name,date) VALUES (34,'MySQL','2020-05-16');
335INSERT INTO build (id,project_name,date) VALUES (35,'MySQL','2020-05-17');
336INSERT INTO build (id,project_name,date) VALUES (36,'MySQL','2020-05-18');
337INSERT INTO build (id,project_name,date) VALUES (37,'MySQL','2020-05-19');
338INSERT INTO build (id,project_name,date) VALUES (38,'MySQL','2020-05-20');
339INSERT INTO build (id,project_name,date) VALUES (39,'MySQL','2020-05-21');
340INSERT INTO build (id,project_name,date) VALUES (40,'MySQL','2020-05-22');
341INSERT INTO build (id,project_name,date) VALUES (41,'MySQL','2020-05-23');
342INSERT INTO build (id,project_name,date) VALUES (42,'MySQL','2020-05-24');
343INSERT INTO build (id,project_name,date) VALUES (43,'MySQL','2020-05-25');
344INSERT INTO build (id,project_name,date) VALUES (44,'MySQL','2020-05-26');
345INSERT INTO build (id,project_name,date) VALUES (45,'MySQL','2020-05-27');
346INSERT INTO build (id,project_name,date) VALUES (46,'MySQL','2020-05-28');
347INSERT INTO build (id,project_name,date) VALUES (47,'MySQL','2020-05-29');
348INSERT INTO build (id,project_name,date) VALUES (48,'MySQL','2020-05-30');
349INSERT INTO build (id,project_name,date) VALUES (49,'MySQL','2020-05-31');
350INSERT INTO build (id,project_name,date) VALUES (50,'MySQL','2020-06-01');
351
352INSERT INTO build (id,project_name,date) VALUES (101,'cryptobank','2020-04-13');
353INSERT INTO build (id,project_name,date) VALUES (102,'cryptobank','2020-04-14');
354INSERT INTO build (id,project_name,date) VALUES (103,'cryptobank','2020-04-15');
355INSERT INTO build (id,project_name,date) VALUES (104,'cryptobank','2020-04-16');
356INSERT INTO build (id,project_name,date) VALUES (105,'cryptobank','2020-04-17');
357INSERT INTO build (id,project_name,date) VALUES (106,'cryptobank','2020-04-18');
358INSERT INTO build (id,project_name,date) VALUES (107,'cryptobank','2020-04-19');
359INSERT INTO build (id,project_name,date) VALUES (108,'cryptobank','2020-04-20');
360INSERT INTO build (id,project_name,date) VALUES (109,'cryptobank','2020-04-21');
361INSERT INTO build (id,project_name,date) VALUES (110,'cryptobank','2020-04-22');
362INSERT INTO build (id,project_name,date) VALUES (111,'cryptobank','2020-04-23');
363INSERT INTO build (id,project_name,date) VALUES (112,'cryptobank','2020-04-24');
364INSERT INTO build (id,project_name,date) VALUES (113,'cryptobank','2020-04-25');
365INSERT INTO build (id,project_name,date) VALUES (114,'cryptobank','2020-04-26');
366INSERT INTO build (id,project_name,date) VALUES (115,'cryptobank','2020-04-27');
367INSERT INTO build (id,project_name,date) VALUES (116,'cryptobank','2020-04-28');
368INSERT INTO build (id,project_name,date) VALUES (117,'cryptobank','2020-04-29');
369INSERT INTO build (id,project_name,date) VALUES (118,'cryptobank','2020-04-30');
370INSERT INTO build (id,project_name,date) VALUES (119,'cryptobank','2020-05-01');
371INSERT INTO build (id,project_name,date) VALUES (120,'cryptobank','2020-05-02');
372INSERT INTO build (id,project_name,date) VALUES (121,'cryptobank','2020-05-03');
373INSERT INTO build (id,project_name,date) VALUES (122,'cryptobank','2020-05-04');
374INSERT INTO build (id,project_name,date) VALUES (123,'cryptobank','2020-05-05');
375INSERT INTO build (id,project_name,date) VALUES (124,'cryptobank','2020-05-06');
376INSERT INTO build (id,project_name,date) VALUES (125,'cryptobank','2020-05-07');
377INSERT INTO build (id,project_name,date) VALUES (126,'cryptobank','2020-05-08');
378INSERT INTO build (id,project_name,date) VALUES (127,'cryptobank','2020-05-09');
379INSERT INTO build (id,project_name,date) VALUES (128,'cryptobank','2020-05-10');
380INSERT INTO build (id,project_name,date) VALUES (129,'cryptobank','2020-05-11');
381INSERT INTO build (id,project_name,date) VALUES (130,'cryptobank','2020-05-12');
382INSERT INTO build (id,project_name,date) VALUES (131,'cryptobank','2020-05-13');
383INSERT INTO build (id,project_name,date) VALUES (132,'cryptobank','2020-05-14');
384INSERT INTO build (id,project_name,date) VALUES (133,'cryptobank','2020-05-15');
385INSERT INTO build (id,project_name,date) VALUES (134,'cryptobank','2020-05-16');
386INSERT INTO build (id,project_name,date) VALUES (135,'cryptobank','2020-05-17');
387INSERT INTO build (id,project_name,date) VALUES (136,'cryptobank','2020-05-18');
388INSERT INTO build (id,project_name,date) VALUES (137,'cryptobank','2020-05-19');
389INSERT INTO build (id,project_name,date) VALUES (138,'cryptobank','2020-05-20');
390INSERT INTO build (id,project_name,date) VALUES (139,'cryptobank','2020-05-21');
391INSERT INTO build (id,project_name,date) VALUES (140,'cryptobank','2020-05-22');
392INSERT INTO build (id,project_name,date) VALUES (141,'cryptobank','2020-05-23');
393INSERT INTO build (id,project_name,date) VALUES (142,'cryptobank','2020-05-24');
394INSERT INTO build (id,project_name,date) VALUES (143,'cryptobank','2020-05-25');
395INSERT INTO build (id,project_name,date) VALUES (144,'cryptobank','2020-05-26');
396INSERT INTO build (id,project_name,date) VALUES (145,'cryptobank','2020-05-27');
397INSERT INTO build (id,project_name,date) VALUES (146,'cryptobank','2020-05-28');
398INSERT INTO build (id,project_name,date) VALUES (147,'cryptobank','2020-05-29');
399INSERT INTO build (id,project_name,date) VALUES (148,'cryptobank','2020-05-30');
400INSERT INTO build (id,project_name,date) VALUES (149,'cryptobank','2020-05-31');
401INSERT INTO build (id,project_name,date) VALUES (150,'cryptobank','2020-06-01');
402
403INSERT INTO build (id,project_name,date) VALUES (1001,'SirKaras','2020-04-13');
404INSERT INTO build (id,project_name,date) VALUES (1002,'SirKaras','2020-04-14');
405INSERT INTO build (id,project_name,date) VALUES (1003,'SirKaras','2020-04-15');
406INSERT INTO build (id,project_name,date) VALUES (1004,'SirKaras','2020-04-16');
407INSERT INTO build (id,project_name,date) VALUES (1005,'SirKaras','2020-04-17');
408INSERT INTO build (id,project_name,date) VALUES (1006,'SirKaras','2020-04-18');
409INSERT INTO build (id,project_name,date) VALUES (1007,'SirKaras','2020-04-19');
410INSERT INTO build (id,project_name,date) VALUES (1008,'SirKaras','2020-04-20');
411INSERT INTO build (id,project_name,date) VALUES (1009,'SirKaras','2020-04-21');
412INSERT INTO build (id,project_name,date) VALUES (1010,'SirKaras','2020-04-22');
413INSERT INTO build (id,project_name,date) VALUES (1011,'SirKaras','2020-04-23');
414INSERT INTO build (id,project_name,date) VALUES (1012,'SirKaras','2020-04-24');
415INSERT INTO build (id,project_name,date) VALUES (1013,'SirKaras','2020-04-25');
416INSERT INTO build (id,project_name,date) VALUES (1014,'SirKaras','2020-04-26');
417INSERT INTO build (id,project_name,date) VALUES (1015,'SirKaras','2020-04-27');
418INSERT INTO build (id,project_name,date) VALUES (1016,'SirKaras','2020-04-28');
419INSERT INTO build (id,project_name,date) VALUES (1017,'SirKaras','2020-04-29');
420INSERT INTO build (id,project_name,date) VALUES (1018,'SirKaras','2020-04-30');
421INSERT INTO build (id,project_name,date) VALUES (1019,'SirKaras','2020-05-01');
422INSERT INTO build (id,project_name,date) VALUES (1020,'SirKaras','2020-05-02');
423INSERT INTO build (id,project_name,date) VALUES (1021,'SirKaras','2020-05-03');
424INSERT INTO build (id,project_name,date) VALUES (1022,'SirKaras','2020-05-04');
425INSERT INTO build (id,project_name,date) VALUES (1023,'SirKaras','2020-05-05');
426INSERT INTO build (id,project_name,date) VALUES (1024,'SirKaras','2020-05-06');
427INSERT INTO build (id,project_name,date) VALUES (1025,'SirKaras','2020-05-07');
428INSERT INTO build (id,project_name,date) VALUES (1026,'SirKaras','2020-05-08');
429INSERT INTO build (id,project_name,date) VALUES (1027,'SirKaras','2020-05-09');
430INSERT INTO build (id,project_name,date) VALUES (1028,'SirKaras','2020-05-10');
431INSERT INTO build (id,project_name,date) VALUES (1029,'SirKaras','2020-05-11');
432INSERT INTO build (id,project_name,date) VALUES (1030,'SirKaras','2020-05-12');
433INSERT INTO build (id,project_name,date) VALUES (1031,'SirKaras','2020-05-13');
434INSERT INTO build (id,project_name,date) VALUES (1032,'SirKaras','2020-05-14');
435INSERT INTO build (id,project_name,date) VALUES (1033,'SirKaras','2020-05-15');
436INSERT INTO build (id,project_name,date) VALUES (1034,'SirKaras','2020-05-16');
437INSERT INTO build (id,project_name,date) VALUES (1035,'SirKaras','2020-05-17');
438INSERT INTO build (id,project_name,date) VALUES (1036,'SirKaras','2020-05-18');
439INSERT INTO build (id,project_name,date) VALUES (1037,'SirKaras','2020-05-19');
440INSERT INTO build (id,project_name,date) VALUES (1038,'SirKaras','2020-05-20');
441INSERT INTO build (id,project_name,date) VALUES (1039,'SirKaras','2020-05-21');
442INSERT INTO build (id,project_name,date) VALUES (1040,'SirKaras','2020-05-22');
443INSERT INTO build (id,project_name,date) VALUES (1041,'SirKaras','2020-05-23');
444INSERT INTO build (id,project_name,date) VALUES (1042,'SirKaras','2020-05-24');
445INSERT INTO build (id,project_name,date) VALUES (1043,'SirKaras','2020-05-25');
446INSERT INTO build (id,project_name,date) VALUES (1044,'SirKaras','2020-05-26');
447INSERT INTO build (id,project_name,date) VALUES (1045,'SirKaras','2020-05-27');
448INSERT INTO build (id,project_name,date) VALUES (1046,'SirKaras','2020-05-28');
449INSERT INTO build (id,project_name,date) VALUES (1047,'SirKaras','2020-05-29');
450INSERT INTO build (id,project_name,date) VALUES (1048,'SirKaras','2020-05-30');
451INSERT INTO build (id,project_name,date) VALUES (1049,'SirKaras','2020-05-31');
452INSERT INTO build (id,project_name,date) VALUES (1050,'SirKaras','2020-06-01');
453
454-- ---
455-- tasks history
456-- ---
457
458INSERT INTO tasks_history (task_id,build_id,status) VALUES (1,1,'opened');
459INSERT INTO tasks_history (task_id,build_id,status) VALUES (1,8,'closed');
460INSERT INTO tasks_history (task_id,build_id,status) VALUES (2,1,'opened');
461INSERT INTO tasks_history (task_id,build_id,status) VALUES (2,3,'closed');
462INSERT INTO tasks_history (task_id,build_id,status) VALUES (3,9,'opened');
463INSERT INTO tasks_history (task_id,build_id,status) VALUES (3,10,'solved');
464INSERT INTO tasks_history (task_id,build_id,status) VALUES (3,11,'closed');
465INSERT INTO tasks_history (task_id,build_id,status) VALUES (4,12,'opened');
466INSERT INTO tasks_history (task_id,build_id,status) VALUES (5,34,'opened');
467INSERT INTO tasks_history (task_id,build_id,status) VALUES (5,40,'closed');
468INSERT INTO tasks_history (task_id,build_id,status) VALUES (6,41,'opened');
469INSERT INTO tasks_history (task_id,build_id,status) VALUES (6,44,'solved');
470INSERT INTO tasks_history (task_id,build_id,status) VALUES (6,45,'opened');
471INSERT INTO tasks_history (task_id,build_id,status) VALUES (6,46,'solved');
472INSERT INTO tasks_history (task_id,build_id,status) VALUES (24,4,'opened');
473INSERT INTO tasks_history (task_id,build_id,status) VALUES (24,5,'solved');
474INSERT INTO tasks_history (task_id,build_id,status) VALUES (24,6,'closed');
475INSERT INTO tasks_history (task_id,build_id,status) VALUES (25,6,'opened');
476INSERT INTO tasks_history (task_id,build_id,status) VALUES (25,7,'solved');
477INSERT INTO tasks_history (task_id,build_id,status) VALUES (25,9,'closed');
478INSERT INTO tasks_history (task_id,build_id,status) VALUES (26,7,'opened');
479INSERT INTO tasks_history (task_id,build_id,status) VALUES (26,9,'solved');
480INSERT INTO tasks_history (task_id,build_id,status) VALUES (26,12,'closed');
481INSERT INTO tasks_history (task_id,build_id,status) VALUES (27,43,'opened');
482INSERT INTO tasks_history (task_id,build_id,status) VALUES (27,47,'solved');
483INSERT INTO tasks_history (task_id,build_id,status) VALUES (31,39,'opened');
484
485INSERT INTO tasks_history (task_id,build_id,status) VALUES (7,109,'opened');
486INSERT INTO tasks_history (task_id,build_id,status) VALUES (7,110,'closed');
487INSERT INTO tasks_history (task_id,build_id,status) VALUES (8,109,'opened');
488INSERT INTO tasks_history (task_id,build_id,status) VALUES (8,112,'closed');
489INSERT INTO tasks_history (task_id,build_id,status) VALUES (9,112,'opened');
490INSERT INTO tasks_history (task_id,build_id,status) VALUES (9,117,'closed');
491INSERT INTO tasks_history (task_id,build_id,status) VALUES (10,118,'opened');
492INSERT INTO tasks_history (task_id,build_id,status) VALUES (10,131,'closed');
493INSERT INTO tasks_history (task_id,build_id,status) VALUES (11,132,'opened');
494INSERT INTO tasks_history (task_id,build_id,status) VALUES (11,134,'solved');
495INSERT INTO tasks_history (task_id,build_id,status) VALUES (11,135,'closed');
496INSERT INTO tasks_history (task_id,build_id,status) VALUES (12,122,'opened');
497INSERT INTO tasks_history (task_id,build_id,status) VALUES (12,124,'solved');
498INSERT INTO tasks_history (task_id,build_id,status) VALUES (12,127,'opened');
499INSERT INTO tasks_history (task_id,build_id,status) VALUES (12,130,'solved');
500INSERT INTO tasks_history (task_id,build_id,status) VALUES (12,133,'opened');
501INSERT INTO tasks_history (task_id,build_id,status) VALUES (12,135,'solved');
502INSERT INTO tasks_history (task_id,build_id,status) VALUES (12,136,'closed');
503INSERT INTO tasks_history (task_id,build_id,status) VALUES (28,136,'closed');
504INSERT INTO tasks_history (task_id,build_id,status) VALUES (28,149,'solved');
505INSERT INTO tasks_history (task_id,build_id,status) VALUES (30,139,'opened');
506
507INSERT INTO tasks_history (task_id,build_id,status) VALUES (13,1004,'opened');
508INSERT INTO tasks_history (task_id,build_id,status) VALUES (13,1014,'closed');
509INSERT INTO tasks_history (task_id,build_id,status) VALUES (14,1015,'opened');
510INSERT INTO tasks_history (task_id,build_id,status) VALUES (14,1018,'closed');
511INSERT INTO tasks_history (task_id,build_id,status) VALUES (15,1019,'opened');
512INSERT INTO tasks_history (task_id,build_id,status) VALUES (15,1025,'closed');
513INSERT INTO tasks_history (task_id,build_id,status) VALUES (16,1027,'opened');
514INSERT INTO tasks_history (task_id,build_id,status) VALUES (16,1029,'solved');
515INSERT INTO tasks_history (task_id,build_id,status) VALUES (16,1030,'closed');
516INSERT INTO tasks_history (task_id,build_id,status) VALUES (17,1028,'opened');
517INSERT INTO tasks_history (task_id,build_id,status) VALUES (17,1029,'solved');
518INSERT INTO tasks_history (task_id,build_id,status) VALUES (17,1030,'opened');
519INSERT INTO tasks_history (task_id,build_id,status) VALUES (17,1031,'solved');
520INSERT INTO tasks_history (task_id,build_id,status) VALUES (17,1032,'opened');
521INSERT INTO tasks_history (task_id,build_id,status) VALUES (17,1033,'solved');
522INSERT INTO tasks_history (task_id,build_id,status) VALUES (17,1034,'opened');
523INSERT INTO tasks_history (task_id,build_id,status) VALUES (17,1035,'solved');
524INSERT INTO tasks_history (task_id,build_id,status) VALUES (17,1036,'closed');
525INSERT INTO tasks_history (task_id,build_id,status) VALUES (18,1034,'opened');
526INSERT INTO tasks_history (task_id,build_id,status) VALUES (18,1038,'solved');
527INSERT INTO tasks_history (task_id,build_id,status) VALUES (18,1039,'closed');
528INSERT INTO tasks_history (task_id,build_id,status) VALUES (19,1040,'opened');
529INSERT INTO tasks_history (task_id,build_id,status) VALUES (19,1046,'closed');
530INSERT INTO tasks_history (task_id,build_id,status) VALUES (20,1015,'opened');
531INSERT INTO tasks_history (task_id,build_id,status) VALUES (20,1019,'solved');
532INSERT INTO tasks_history (task_id,build_id,status) VALUES (20,1020,'closed');
533INSERT INTO tasks_history (task_id,build_id,status) VALUES (21,1016,'opened');
534INSERT INTO tasks_history (task_id,build_id,status) VALUES (21,1020,'solved');
535INSERT INTO tasks_history (task_id,build_id,status) VALUES (21,1021,'closed');
536INSERT INTO tasks_history (task_id,build_id,status) VALUES (29,1039,'opened');
537
538-- ---
539-- bugs causes
540-- ---
541
542-- (bug_id,feature_id)
543
544INSERT INTO bug_cause VALUES (3,1);
545INSERT INTO bug_cause VALUES (6,1);
546INSERT INTO bug_cause VALUES (24,2);
547INSERT INTO bug_cause VALUES (25,2);
548INSERT INTO bug_cause VALUES (26,2);
549INSERT INTO bug_cause VALUES (27,5);
550INSERT INTO bug_cause VALUES (31,1);
551
552INSERT INTO bug_cause VALUES (11,10);
553INSERT INTO bug_cause VALUES (12,9);
554INSERT INTO bug_cause VALUES (28,8);
555INSERT INTO bug_cause VALUES (30,7);
556
557INSERT INTO bug_cause VALUES (16,15);
558INSERT INTO bug_cause VALUES (17,14);
559INSERT INTO bug_cause VALUES (18,14);
560INSERT INTO bug_cause VALUES (20,13);
561INSERT INTO bug_cause VALUES (21,13);
562INSERT INTO bug_cause VALUES (29,15);
563
564-- view 1
565CREATE OR REPLACE VIEW task_status AS
566SELECT task.id as task_id,build_id,task.project_name,task.title,task.priority,status
567FROM tasks_history AS a
568JOIN task
569 ON task.id = task_id
570WHERE build_id = (
571 SELECT MAX(build_id)
572 FROM tasks_history AS b
573 WHERE a.task_id = b.task_id
574);
575
576
577-- helper view for view 2
578CREATE OR REPLACE VIEW project_task AS
579SELECT task.id, task_status.status, task.project_name, task.type
580FROM task_status
581JOIN task
582 ON task_status.task_id = task.id;
583
584
585-- view 2
586CREATE OR REPLACE VIEW good_developers AS
587select *, (
588 SELECT count(*)
589 FROM task_status
590 JOIN task
591 on task_status.task_id = task.id
592 WHERE task.type = 'bug' AND task_status.status = 'closed' AND task.developer_id = employee.id
593) as closed_bugs
594from employee
595where (
596 SELECT count(*)
597 FROM task_status
598 JOIN task
599 on task_status.task_id = task.id
600 WHERE task.type = 'bug' AND task_status.status = 'closed' AND task.developer_id = employee.id
601) >= (
602 SELECT count(*)
603 FROM task_status
604 JOIN task
605 ON task_status.task_id = task.id
606 GROUP BY task.developer_id, task.type, task_status.status
607 HAVING task.type = 'bug' AND task_status.status = 'closed'
608) / (
609 SELECT count(*)
610 FROM task_status
611 JOIN task
612 ON task_status.task_id = task.id
613 WHERE task.type = 'bug' AND task_status.status = 'closed'
614);
615
616
617-- view 3
618CREATE OR REPLACE VIEW good_developers AS
619select *
620from employee
621where (
622 SELECT count(*)
623 FROM task_status
624 JOIN task
625 on task_status.task_id = task.id
626 WHERE task.type = 'bug' AND task_status.status = 'closed' AND task.developer_id = employee.id
627) >= (
628 SELECT count(*)
629 FROM task_status
630 JOIN task
631 ON task_status.task_id = task.id
632 GROUP BY task.developer_id, task.type, task_status.status
633 HAVING task.type = 'bug' AND task_status.status = 'closed'
634) / (
635 SELECT count(*)
636 FROM task_status
637 JOIN task
638 ON task_status.task_id = task.id
639 WHERE task.type = 'bug' AND task_status.status = 'closed'
640);
641
642-- procedure
643
644CREATE OR REPLACE VIEW bug_opened AS
645SELECT task.id as task_id,build_id,task.project_name,task.description,task.priority,status
646FROM tasks_history AS a
647JOIN task
648 ON task.id = task_id
649WHERE task.type = 'bug' AND build_id = (
650 SELECT MIN(build_id)
651 FROM tasks_history AS b
652 WHERE a.task_id = b.task_id
653);
654
655DROP FUNCTION IF EXISTS interpolate;
656DELIMITER $$
657CREATE FUNCTION interpolate(project_name VARCHAR(256), start_date DATETIME) RETURNS DATETIME
658 DETERMINISTIC
659BEGIN
660 DECLARE opened_bugs INT;
661 DECLARE closed_bugs INT;
662 DECLARE time_per_bug INT;
663 DECLARE time_to_close_all_bugs TIME;
664 DECLARE result DATETIME;
665
666 set @opened_bugs = ifnull((select count(*)
667 from bug_opened
668 join task
669 on task.id = bug_opened.task_id
670 join build
671 on bug_opened.build_id = build.id
672 where task.type = 'bug' and task.project_name = project_name and build.date <= start_date)
673 , 0);
674 set @closed_bugs = ifnull((select count(*)
675 from task_status
676 join task
677 on task.id = task_status.task_id
678 join build
679 on task_status.build_id = build.id
680 where task.type = 'bug' and task.project_name = project_name and status = 'closed' and build.date <= start_date
681 ), 0);
682 -- time / closed_bugs
683 set @time_per_bug = TIMESTAMPDIFF(SECOND, (
684 SELECT(min(date))
685 from build
686 where project_name = project_name
687 ), cast(start_date as datetime)) / @closed_bugs;
688 -- milliseconds required to close all opened bugs
689 set @time_to_close_all_bugs = (@opened_bugs - @closed_bugs) * @time_per_bug;
690 set @result = addtime(start_date, @time_to_close_all_bugs);
691 RETURN @result;
692END $$
693
694-- view_1.sql
695
696-- 1. ╨б╨┤╨╡╨╗╨░╨╣╤В╨╡ ╨┐╤А╨╡╨┤╤Б╤В╨░╨▓╨╗╨╡╨╜╨╕╨╡ ╤Б ╨░╨║╤В╤Г╨░╨╗╤М╨╜╤Л╨╝╨╕ ╤Б╤В╨░╤В╤Г╤Б╨░╨╝╨╕ ╨╖╨░╨┤╨░╤З (╤В╨╛╤В, ╨║╨╛╤В╨╛╤А╤Л╨╣ ╨▒╤Л╨╗ ╨┐╤А╨╕╤Б╨▓╨╛╨╡╨╜ ╨╡╨╣ ╨▓ ╨┐╨╛╤Б╨╗╨╡╨┤╨╜╨╡╨╣ ╤Б╨▒╨╛╤А╨║╨╡)
697
698CREATE OR REPLACE VIEW task_status AS
699SELECT task.id as task_id,build_id,task.project_name,task.title,task.priority,status
700FROM tasks_history AS a
701JOIN task
702 ON task.id = task_id
703WHERE build_id = (
704 SELECT MAX(build_id)
705 FROM tasks_history AS b
706 WHERE a.task_id = b.task_id
707);
708
709SELECT * FROM task_status;
710-- view_2.sql
711
712-- 2. ╨б╨┤╨╡╨╗╨░╨╣╤В╨╡ ╨┐╤А╨╡╨┤╤Б╤В╨░╨▓╨╗╨╡╨╜╨╕╨╡, ╨┐╨╛╨║╨░╨╖╤Л╨▓╨░╤О╤Й╨╡╨╡ ╨╖╨░╨▓╨╡╤А╤И╨╡╨╜╨╜╨╛╤Б╤В╤М ╨┐╤А╨╛╨╡╨║╤В╨░
713-- ╨Я╨╛╨║╨░╨╖╨░╤В╨╡╨╗╤М ╨╖╨░╨▓╨╡╤А╤И╨╡╨╜╨╜╨╛╤Б╤В╨╕ ╨┐╤А╨╛╨╡╨║╤В╨░ - ╤Н╤В╨╛ ╨┐╤А╨╛╤Ж╨╡╨╜╤В ╨╖╨░╨║╤А╤Л╤В╤Л╤Е ╤Д╨╕╤З ╨╕ ╨║╨╛╨╗╨╕╤З╨╡╤Б╤В╨▓╨╛ ╨╛╤В╨║╤А╤Л╤В╤Л╤Е ╨▒╨░╨│╨╛╨▓
714
715CREATE OR REPLACE VIEW project_completeness AS
716SELECT project.name AS project_name, (
717 SELECT count(*)
718 FROM task_status
719 JOIN task
720 ON task_status.task_id = task.id
721 WHERE task.type = 'feature' AND task.project_name = project.name AND task_status.status = 'closed'
722) / (
723 SELECT count(*)
724 FROM task
725 WHERE task.type = 'feature' and task.project_name = project.name
726) AS closed_features_percentage, (
727 SELECT count(*)
728 FROM task_status
729 JOIN task
730 ON task_status.task_id = task.id
731 WHERE task.project_name = project.name AND status IN ('opened', 'solved') AND task.type = 'bug'
732) AS opened_bugs
733FROM project;
734
735SELECT * FROM project_completeness;
736-- view_3.sql
737
738-- 3. ╨б╨┤╨╡╨╗╨░╨╣╤В╨╡ ╨┐╤А╨╡╨┤╤Б╤В╨░╨▓╨╗╨╡╨╜╨╕╨╡, ╨║╨╛╤В╨╛╤А╨╛╨╡ ╤Б╨╛╨┤╨╡╤А╨╢╨╕╤В ╤Б╨┐╨╕╤Б╨╛╨║ ╤Е╨╛╤А╨╛╤И╨╕╤Е ╨┐╤А╨╛╨│╤А╨░╨╝╨╝╨╕╤Б╤В╨╛╨▓ ╨╕ ╨┤╨╗╤П ╨║╨░╨╢╨┤╨╛╨│╨╛ ╨╕╨╖ ╨╜╨╕╤Е - ╤З╨╕╤Б╨╗╨╛ ╨╖╨░╨║╤А╤Л╤В╤Л╤Е ╨▒╨░╨│╨╛╨▓
739-- ╨е╨╛╤А╨╛╤И╨╕╨╝╨╕ ╨┐╤А╨╛╨│╤А╨░╨╝╨╝╨╕╤Б╤В╨░╨╝╨╕ ╤Б╤З╨╕╤В╨░╤О╤В╤Б╤П ╤В╨╡, ╨║╤В╨╛ ╨╖╨░╨║╤А╤Л╨╗ ╨▒╨╛╨╗╤М╤И╨╡ ╨▒╨░╨│╨╛╨▓, ╤З╨╡╨╝ ╤Б╤А╨╡╨┤╨╜╨╡╨╡ ╨║╨╛╨╗╨╕╤З╨╡╤Б╤В╨▓╨╛ ╨╖╨░╨║╤А╤Л╤В╤Л╤Е ╨▒╨░╨│╨╛╨▓ ╨╜╨░ ╨┐╤А╨╛╨│╤А╨░╨╝╨╝╨╕╤Б╤В╨░
740
741CREATE OR REPLACE VIEW bugs_closed AS
742SELECT count(task.developer_id) as bugs_count
743FROM task_status
744JOIN task
745 ON task_status.task_id = task.id
746JOIN employee
747 ON employee.id = task.developer_id
748WHERE task.type = 'bug' AND task_status.status = 'closed'
749GROUP BY task.developer_id;
750
751CREATE OR REPLACE VIEW good_developers AS
752select *
753from employee
754where (
755 SELECT count(*)
756 FROM task_status
757 JOIN task
758 ON task_status.task_id = task.id
759 WHERE task.type = 'bug' AND task_status.status = 'closed' AND task.developer_id = employee.id
760) >= (
761 SELECT SUM(bugs_count)
762 FROM bugs_closed
763) / (
764 SELECT count(*)
765 FROM employee
766 WHERE job = 'programmer'
767);
768
769select (
770 SELECT SUM(bugs_count)
771 FROM bugs_closed
772) / (
773 SELECT count(*)
774 FROM employee
775 WHERE job = 'programmer'
776) as average_closed_bugs_per_programmer;
777
778SELECT employee.full_name, count(task.developer_id) as bugs_closed
779FROM task_status
780JOIN task
781 ON task_status.task_id = task.id
782JOIN employee
783 ON task.developer_id = employee.id
784WHERE task.type = 'bug' AND task_status.status = 'closed' AND task.developer_id = employee.id
785GROUP BY task.developer_id;
786
787SELECT * FROM good_developers;