Tibia SQL Data Analysis: Practice #1

SQL, Excel, Tableau

Practice some SQL, data analysis and visualizations! This is going to be split into a series of practices. Practice #1 and #2 are quite easy, serving as warm-ups.

You can download the datasets below.

Tibia¹ is a massive multiplayer online role-playing game (MMORPG) that exists since 1997²! (and also name of a bone³ =) ).

Figure 1: Tibia Server Growth Throughout The Years

• 1997: The start

• 2002: Expansion to North America

• until 2005: Experiencing meteoric growth

• until 2013: Slower steady growth

• 2014: The big shrink

• 2016: Introduction of South American servers; slow recovery starts

• 2018: South America grows; Europe and North America continues to shrink

• 2021: South America outgrows Europe and North America

• 2023: All times high

Get the data & load it

Download the three datasets (CSV):

1. Tibia Kill Statistics.

2. Tibia Game World Servers.

3. Tibia Deprecated Servers. 

I used Microsoft's SQL Server Management Studio,⁴ so you may need to convert the code from Transact SQL to whatever version you use. I suppose ChatGPT could do that fairly fast and easily.

Create the tables, load the data

--DROP TABLE "worlds"
IF OBJECT_ID('worlds', 'U') IS NOT NULL
DROP TABLE worlds
------
------
CREATE TABLE worlds (
   worldName VARCHAR(10) NOT NULL,
   PRIMARY KEY (worldName),
   onlineRecord INT,
   onlineRecordDate DATE, --- in CET timezone
   servLocation VARCHAR(15), --- "serv" as in short of "server"
   pvpType VARCHAR(25), ---PvP = Player versus Player
   serverCETCreatedDate DATE, ---CET = Central European Time; and
 ---Day set to 1st (DD/MM/YYYY) as default due to data inavailability
   additionalInformation VARCHAR(250)
);
-----
-----
---LOAD CSV Tibia Game World Servers to load worlds table (of table)
BULK INSERT worlds
FROM 'C:\Users\...\Tibia Game World Servers.csv'
WITH (
FORMAT='CSV',
FIRSTROW=2
);
------
------
------
--DROP TABLE "killstats"
IF OBJECT_ID('killstats', 'U') IS NOT NULL
DROP TABLE killstats
------
------
CREATE TABLE killstats (
 serverName VARCHAR(10) NOT NULL,
  -- PRIMARY KEY (serverName),
 race VARCHAR(30) NOT NULL,
 lastDayKilledPlayers INT,
 lastDayKilledbyPlayers INT,
 lastWeekKilledPlayers INT,
 lastWeekKilledbyPlayers INT,
 WeekNumber INT
);
-----
-----
---LOAD CSV Tibia Kill Statistics to load killstats table (of table)
BULK INSERT killstats
FROM 'C:\Users\...\Tibia Kill Statistics.csv'
WITH (
FORMAT='CSV',
FIRSTROW=2
);
------
------
------
--DROP TABLE "oldservers"
IF OBJECT_ID('oldservers', 'U') IS NOT NULL
DROP TABLE oldservers
------
------
CREATE TABLE oldservers (
 servName VARCHAR(10) NOT NULL,
 located VARCHAR(15),
 servType VARCHAR(20), ---PvP = Player versus Player
 onlineSince DATE,
 offlineSince DATE
);
-----
-----
---LOAD CSV Tibia Deprecated Servers to load oldservers table (of table)
BULK INSERT oldservers
FROM 'C:\Users\...\Tibia Deprecated Servers.csv'
WITH (
FORMAT='CSV',
FIRSTROW=2
);
------
------
------

Practice #1: First SQL warm-up

For this warm up, use the dataset: Tibia Game Worlds Server

The Questions

(the answers are provided at the bottom, section called “The Answers”)

Question #A: How many game world servers are there currently in Tibia? 

Desired output:

  Number_of_online_worlds

  88

Question #B: How many unique PvP types of game world servers are there currently in Tibia?

Desired output:

  Pvp_Types_Number

  5

Question #C: How many current worlds are there per PvP Type?

Desired output:

Question #D: How many worlds are there currently in Tibia per server location?

Desired output:

Question #E: How many current online worlds are there per server location and PvP type?
Please display the results from the most to the least amount of servers.

The Answers

Answer #A: How many game world servers are there currently in Tibia? 

Solution code below:

SELECT count(worldname) AS Number_of_online_worlds
FROM worlds

Answer #B: How many unique PvP types of game world servers are there currently in Tibia?

Solution code below:

SELECT COUNT(DISTINCT pvpType) AS PvP_Types_Number
FROM worlds

Answer #C: How many current worlds are there per PvP Type?

Solution code below: (in white letters)
SELECT pvpType
,COUNT(worldname) AS Number_of_online_worlds
FROM worlds
GROUP BY pvpType

Answer #D: How many worlds are there currently in Tibia per server location?

Solution code below:

SELECT servLocation
,COUNT(worldname) AS Number_of_online_worlds
FROM worlds
GROUP BY servLocation

Answer #E: How many current online worlds are there per server location and PvP type?

Solution code below:

SELECT servLocation
, pvpType
,COUNT(worldname) AS Number_of_online_worlds
FROM worlds
GROUP BY servLocation, pvpType
ORDER BY 3 DESC

1. www.tibia.com

2. https://en.wikipedia.org/wiki/Tibia_(video_game)

3. https://en.wikipedia.org/wiki/Tibia

4. https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16