Both datasets are SQLite databases (Dataset-ships.db, Dataset-purchases.db). Данные можно открыть, например, через DB Browser for SQLite (sqlitebrowser.org).
Задание #1 — Dataset-ships.db
В базе 4 таблицы:
— arenas — данные о боях: игровой режим, карта, длительность боя, команда-победитель (winner_team_id, -1 — ничья), start_dt, duration_sec, map_type_id, team_build_type_id, battle_level_id;
— arena_members — участники боёв: account_db_id (отрицательные значения — боты), team_id, clan_db_id, vehicle_type_id, ships_killed, planes_killed, damage, team_damage, received_damage, regen_hp, max_hp, is_alive, distance, credits, exp;
— glossary_ships — описание кораблей: страна, класс, уровень;
— catalog_items — справочник игровых сущностей (ARENA_TYPES, TEAM_BUILD_TYPE и т.д.).
Нужно написать SQL-запрос, который строит агрегированную таблицу — прототип fact-таблицы для дашборда мониторинга метрик кораблей. Требования:
— дневная гранулярность (daily);
— только реальные игроки (без ботов);
— исключить игры CUSTOM и TUTORIAL;
— режимы PVE и PVE_PREMADE объединить в один PVE;
— с возможностью фильтрации по: team build type, кораблю, классу корабля, уровню корабля, карте;
— метрики (по дням, на корабль/класс): Winrate, Survivability, средний урон за бой, отношение фрагов к смертям, средний доход в кредитах, средний доход в опыте.
Пришлите SQL-код. Комментарии к коду и к самому заданию приветствуются.
Задание #2 — Dataset-purchases.db
База состоит из одной таблицы purchases: purchaser_id, purchase (только два значения — common_pack и exclusive_pack), purchase_dt (дата покупки).
Любыми инструментами посчитайте количество пользователей для каждого уникального паттерна покупок. Паттерн — комбинация покупок exclusive/common-паков по правилам:
1. У каждого пользователя один паттерн (подпоследовательности не считаются).
2. Покупки в паттерне упорядочены по дате (ранняя — первой), временные промежутки игнорируются.
3. Каждая покупка exclusive-пака должна присутствовать в паттерне.
4. Серия подряд идущих покупок common-паков считается за одну покупку в паттерне.
Например, история common → exclusive → exclusive → common → common → exclusive даёт паттерн: Common Pack — Exclusive Pack — Exclusive Pack — Common Pack — Exclusive Pack. Приложите решение к результатам.