Перед початком скачайте файл dump.sql
та загрузіть його в базу даних PosgreSQL за допомогою спеціальної команди або вручну виконавши команди для створення та заповнення таблиць
- Joins
- Distinct
- Window functions
- Group by
- Order by
- Unions
Ваше завдання складається з 5 пунктів, які рекомендовано виконувати послідовно. Результатом пунктів 1 та 2 має бути SQL запит який створить PostgresSQL View.
Починаючи з пункту 3 можна використовувати будь-яку мову програмування(бажано python), або можете використати SQL
Можете створювати будь-яку кількість проміжних таблиць та views
Таблиця містить в собі всі задачі
issue_key
- ключ задачіproject_key
- ключ проектуissue_type
- тип задачі (Task, Bug, Improvement, etc)assignee_key
- ключ людини, яка назначена на задачу в даний моментcreated_at
- дата створення задачі
Таблиця містить в собі всі зміни статусів задач
issue_key
- ключ задачіauthor_key
- ключ автора, який змінив статусfrom_status
- з якого статусу задачу перенеслиto_status
- в який статус задачу перенеслиcreated_at
- дата зміни статусу
- Розрахуйте з якого по який час задача була в певному статусі.
Результат:
View який поверне наступну структуру -issue_key
,author_key
,status
,start_date
,end_date
- Для кожного запису вирахувати скільки календарного часу в секундах кожна задача була в певному статусі
- Для кожної задачі над якою працювала людина вирахувати скільки робочого часу задача була в статусі "In progress". Робочий час вважати з 10:00 по 20:00 з понеділка по пятницю
- Якщо людина працює над декількома задачами одночасно - робочий час за цей період (коли він працює над декількома задачами) ділиться на кількість задач, над якими він працює.
Наприклад,
Jason працював над задачею A з 10:00 19.01.2024 до 14:00 23.01.2024
над задачею B з 10:00 22.01.2024 до 20:00 23.01.2024
Маємо отримати
issue_key | author_key | calendar_duration | working_duration |
---|---|---|---|
A | Jason | 360000 | 61200 |
B | Jason | 122400 | 46800 |
Схематичне відображення:
5. Розрахувати загальну вартість всіх задач в межах типу задач. Тип задач знаходиться в таблиці
jira__issues
колонка issue_type
. Розрахувати за такою логікою:
- Якщо
author_key
починається з "JIRAUSER" - ціна години часу такого спеціаліста $1.00 - Якщо
author_key
НЕ починається з "JIRAUSER" - ціна години часу такого спеціаліста $2.00
Результат:
Таблиця з полями - issue_type
, total_cost
Рекомендація:
Для початку розрахуйте вартість кожної задачі. Наступним кроком дістаньте тип для кожної задачі та порахуйте суму для кожного типу