Тестовое задание
на аналитика данных
world of tanks

When solving tasks from this section, you can use any implementation of the SQL type (Oracle, Impala/Hive, MySQL, etc.) Be sure to specify which syntax you are using.

The database contains the player_battles table that contains data about battles played by each player during the day:

where:

• player_id: the unique identifier of a player.

• battle_id: the unique battle identifier that is common for all participants in one battle.

• vehicle_name: the name of the vehicle in which the player entered the battle. Marks like ‘ussr’, ‘uk’, ‘germany’ are identifiers of the vehicle nation.

• dt: battle date.

• exp: the amount of experience received by the player in a battle.

1) Please write an SQL-script that allows to display the list of vehicle nations that have fought more than 10,000 battles as of the selected date, for example, 15.09.2018.

2) Please write an SQL script that allows you to get the information about the number of unique players who fought a certain number of battles by cohorts. The cohorts should be as follows:

• [0;10) battles

• [10;20) battles

• [20;50) battles

• [50;100) battles

• [100; +inf) battles

3) Please write an SQL script that allows you to count the number of players which were active on both days: 14.09.2018 and 15.09.2018.

You have two tables (see below) which one of them contains battle statistics per player and the second one contains a list of unique identifiers of players who are developers (dev_player_id):

where:
• battle_id : the unique battle identifier which is common for all participants in one battle;

• battle_start : the exact time when a battle started;

• player_id : the unique identifier of a player;

• team_id : the identifier of a team in which a player was distributed in a battle. There are two teams
in a battle;

• tank_id : the unique identifier of a vehicle in which the player entered the battle;

• damage : the amount of damage done to the enemies by the player in a battle;

• credits : the number of credits received by the player in a battle;

• exp : the amount of experience received by the player in a battle;

• team_won_id : the unique identifier of the team who won a battle.



Using the tables above, please write an SQL script to gather following metrics for each player the following:

• the amount of damage done to the enemies and earned credits in the last player’s won battle
excluding developers;

• date and time of the first lost player’s battle excluding developers;

In World of Tanks there are launched the seasons of Battle pass with the duration around 3 months each (Battle pass description in the WoT Portal).
The Producer came to you as a Data Analyst and requested to provide the responsible team with monitoring of the Battle pass health metrics to understand the current state of activity and, if necessary, make prompt decisions in the World of Tanks project. Please, complete the following:

• Formulate 10 questions that you would ask the Producer to better understand the in-game activity that is being launched.

• Suggest at least 10 metrics that you would suggest to the Producer to monitor the event status.

• Suggest ways and tools to complete the received request.

* Additionally (optional): Describe the stages/sequence of your actions to complete the request.

The attached data_1.csv file contains battles data for two groups of players divided by availability of some changes in setting and configuration in the game. For group A changes were available, for group B were not. Data includes activity from 2022-10-01 till 2022-11-29. From 2022-10-27 to 2022-11-29 changes were available for Group A.

Please, show the approaches in data analysis and visualization known to you. Feel free use any tools, technics, and approaches to complete the task:

• Examine the data set and carry out the analysis. We expect some report on your research that would show the performance of released changes in the game and other interesting facts that you’re able to find in the data set. It’s supposed to be the report for business stakeholders who make decision about launched improvements.

• Based on the results of the generated report, briefly describe the observations and conclusions that you made when processing and analyzing the data.

*Optional: Based on the results, provide the Product Manager with recommendations on how to improve the performance of updates and new vehicle branches in the future.

In the attached data_2.csv file, there are data on the number of active players during the period of 2016–2017, where:

Make a forecast on the DAU index for the period of 3 months of 2018 (January–March). Please specify the method and approach used to build the predicted values.