Анализ и статистика данных по учащимся.

Part 1. Создание базы данных
Part 2. Получение данных

Скрипт part1.sql, создает базу данных и все таблицы, описанные ниже Также в скрипт есть процедуры, позволяющие импортировать и экспортировать данные для каждой таблицы из файла/в файл с расширением .csv.
В качестве параметра каждой процедуры указывается разделитель csv файла.

• Ник пира
• День рождения

• Название задания
• Название задания, являющегося условием входа
• Максимальное количество XP

Чтобы получить доступ к заданию, нужно выполнить задание, являющееся его условием входа. Для упрощения будем считать, что у каждого задания всего одно условие входа. В таблице должно быть одно задание, у которого нет условия входа (т.е. поле ParentTask равно null).

Создать тип перечисления для статуса проверки, содержащий следующие значения:

• Start - начало проверки
• Success - успешное окончание проверки
• Failure - неудачное окончание проверки

• ID
• ID проверки
• Ник проверяющего пира
• Статус P2P проверки
• Время

Каждая P2P проверка состоит из 2-х записей в таблице: первая имеет статус начало, вторая - успех или неуспех.
В таблице не может быть больше одной незавершенной P2P проверки, относящейся к конкретному заданию, пиру и проверяющему.
Каждая P2P проверка (т.е. обе записи, из которых она состоит) ссылается на проверку в таблице Checks, к которой она относится.

• ID
• ID проверки
• Статус проверки Verter'ом
• Время

Каждая проверка Verter'ом состоит из 2-х записей в таблице: первая имеет статус начало, вторая - успех или неуспех.
Каждая проверка Verter'ом (т.е. обе записи, из которых она состоит) ссылается на проверку в таблице Checks, к которой она относится.
Проверка Verter'ом может ссылаться только на те проверки в таблице Checks, которые уже включают в себя успешную P2P проверку.

• ID
• Ник пира
• Название задания
• Дата проверки

Описывает проверку задания в целом. Проверка обязательно включает в себя один этап P2P и, возможно, этап Verter. Для упрощения будем считать, что пир ту пир и автотесты, относящиеся к одной проверке, всегда происходят в один день.

Проверка считается успешной, если соответствующий P2P этап успешен, а этап Verter успешен, либо отсутствует. Проверка считается неуспешной, хоть один из этапов неуспешен. То есть проверки, в которых ещё не завершился этап P2P, или этап P2P успешен, но ещё не завершился этап Verter, не относятся ни к успешным, ни к неуспешным.

• ID
• Ник проверяющего пира
• Ник проверяемого пира
• Количество переданных пир поинтов за всё время (только от проверяемого к проверяющему)

При каждой P2P проверке проверяемый пир передаёт один пир поинт проверяющему. Эта таблица содержит все пары проверяемый-проверяющий и кол-во переданных пир поинтов, то есть, другими словами, количество P2P проверок указанного проверяемого пира, данным проверяющим.

• ID
• Ник первого пира
• Ник второго пира

Дружба взаимная, т.е. первый пир является другом второго, а второй -- другом первого.

• ID
• Ник пира
• Ник пира, к которому рекомендуют идти на проверку

Каждому может понравиться, как проходила P2P проверка у того или иного пира. Пир, указанный в поле Peer, рекомендует проходить P2P проверку у пира из поля RecommendedPeer. Каждый пир может рекомендовать как ни одного, так и сразу несколько проверяющих.

• ID
• ID проверки
• Количество полученного XP

За каждую успешную проверку пир, выполнивший задание, получает какое-то количество XP, отображаемое в этой таблице. Количество XP не может превышать максимальное доступное для проверяемой задачи. Первое поле этой таблицы может ссылаться только на успешные проверки.

• ID
• Ник пира
• Дата
• Время
• Состояние (1 - пришел, 2 - вышел)

Данная таблица содержит информация о посещениях пирами кампуса. Когда пир входит в кампус, в таблицу добавляется запись с состоянием 1, когда покидает - с состоянием 2.

В заданиях, относящихся к этой таблице, под действием "выходить" подразумеваются все покидания кампуса за день, кроме последнего. В течение одного дня должно быть одинаковое количество записей с состоянием 1 и состоянием 2 для каждого пира.

Например:

В этом примере "выходом" является только запись с ID, равным 2. Пир с ником Aboba выходил из кампуса на 14 минут.

Скрипт part2.sql, содержит все описанные далее процедуры и функции.

Ник пира 1, ник пира 2, количество переданных пир поинтов.
Количество отрицательное, если пир 2 получил от пира 1 больше поинтов.

Пример вывода:

В таблицу включать только задания, успешно прошедшие проверку (определять по таблице Checks).
Одна задача может быть успешно выполнена несколько раз. В таком случае в таблицу включать все успешные проверки.

Пример вывода:

Параметры функции: день, например 12.05.2022.
Функция возвращает только список пиров.

Формат вывода: процент успешных, процент неуспешных

Пример вывода:

Результат вывести отсортированным по изменению числа поинтов.
Формат вывода: ник пира, изменение в количество пир поинтов

Пример вывода:

Результат вывести отсортированным по изменению числа поинтов.
Формат вывода: ник пира, изменение в количество пир поинтов

Пример вывода:

При одинаковом количестве проверок каких-то заданий в определенный день, вывести их все.
Формат вывода: день, название задания

Пример вывода:

Под длительностью подразумевается разница между временем, указанным в записи со статусом "начало", и временем, указанным в записи со статусом "успех" или "неуспех".
Формат вывода: длительность проверки

Параметры процедуры: название блока, например "CPP".
Результат вывести отсортированным по дате завершения.
Формат вывода: ник пира, дата завершения блока (т.е. последнего выполненного задания из этого блока)

Пример вывода:

Определять нужно исходя из рекомендаций друзей пира, т.е. нужно найти пира, проверяться у которого рекомендует наибольшее число друзей.
Формат вывода: ник пира, ник найденного проверяющего

Пример вывода:

• Приступили только к блоку 1
• Приступили только к блоку 2
• Приступили к обоим
• Не приступили ни к одному

Пир считается приступившим к блоку, если он проходил хоть одну проверку любого задания из этого блока (по таблице Checks)

Параметры процедуры: название блока 1, например SQL, название блока 2, например A.
Формат вывода: процент приступивших только к первому блоку, процент приступивших только ко второму блоку, процент приступивших к обоим, процент не приступивших ни к одному

Пример вывода:

Параметры процедуры: количество пиров N.
Результат вывести отсортированным по кол-ву друзей.
Формат вывода: ник пира, количество друзей

Пример вывода:

Также определите процент пиров, которые хоть раз проваливали проверку в свой день рождения.
Формат вывода: процент успехов в день рождения, процент неуспехов в день рождения

Пример вывода:

Если одна задача выполнена несколько раз, полученное за нее кол-во XP равно максимальному за эту задачу.
Результат вывести отсортированным по кол-ву XP.
Формат вывода: ник пира, количество XP

Пример вывода:

Параметры процедуры: названия заданий 1, 2 и 3.
Формат вывода: список пиров

То есть сколько задач нужно выполнить, исходя из условий входа, чтобы получить доступ к текущей.
Формат вывода: название задачи, количество предшествующих

Пример вывода:

Параметры процедуры: количество идущих подряд успешных проверок N.
Временем проверки считать время начала P2P этапа.
Под идущими подряд успешными проверками подразумеваются успешные проверки, между которыми нет неуспешных.
При этом кол-во опыта за каждую из этих проверок должно быть не меньше 80% от максимального.
Формат вывода: список дней

Формат вывода: ник пира, число выполненных заданий

Пример вывода: Output example:

Формат вывода: ник пира, количество XP

Пример вывода:

Формат вывода: ник пира

Параметры процедуры: время, количество раз N.
Формат вывода: список пиров

Параметры процедуры: количество дней N, количество раз M.
Формат вывода: список пиров

Формат вывода: ник пира

Параметры процедуры: количество минут N.
Формат вывода: список пиров

Для каждого месяца посчитать, сколько раз люди, родившиеся в этот месяц, приходили в кампус за всё время (будем называть это общим числом входов).
Для каждого месяца посчитать, сколько раз люди, родившиеся в этот месяц, приходили в кампус раньше 12:00 за всё время (будем называть это числом ранних входов).
Для каждого месяца посчитать процент ранних входов в кампус относительно общего числа входов.
Формат вывода: месяц, процент ранних входов

Пример вывода: