Tibia SQL Data Analysis: Practice #2

Posted originally 8 June 2023; 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.

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

The Data

Download the three datasets (CSV):

1. Tibia Kill Statistics.

2. Tibia Game World Servers.

3. Tibia Deprecated Servers. 

You may copy the table creation and data loading from Practice #1.

Practice #2: Second 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 #F: What's the oldest server? Show: worldname, date, servLocation, pvpType.

Desired output:

Question #G: Which are the three newest servers? Show: worldname, date, location, pvpType.

Desired output:

Question #H: What is the newest and oldest server for each server location? Show: worldname, location, date. Display from the oldest to the newest.

Desired output:

Question #I: What's the newest and oldest server for each server location and PvP type? Show: server location ASC, PvO type DESC, date ASC, worldname.

Desired output:

The Answers

Answer #F: What's the oldest server? Show: worldname, date, servLocation, pvpType.

Solution code below:

SELECT TOP 1 worldName
  , serverCETCreatedDate
  , servLocation
  , pvpType
FROM worlds
ORDER BY 2 ASC

Answer #G: Which are the three newest servers? Show: worldname, date, location, pvpType.

Solution code below:

SELECT TOP 3 worldName
  , serverCETCreatedDate
  , servLocation
  , pvpType
FROM worlds
ORDER BY 2 DESC

Answer #H: What is the newest and oldest server for each server location? Show: worldname, location, date. Display from the oldest to the newest.

Solution code below:

WITH dateAndLocation AS (
SELECT MIN(serverCETCreatedDate) AS earliest	
       , servLocation	
       , MAX(serverCETCreatedDate) AS latest	
FROM worlds	
GROUP BY servLocation	
)	
SELECT worldname
       , worlds.servLocation
       , serverCETCreatedDate	
FROM worlds	
JOIN dateAndLocation	
ON worlds.servLocation = dateAndLocation.servLocation 	
       AND (
       (worlds.serverCETCreatedDate = dateAndLocation.earliest) 
       OR (worlds.serverCETCreatedDate = dateAndLocation.latest)
)	
ORDER BY 3	

Answer #I: What's the newest and oldest server for each server location and PvP type? Show: server location ASC, PvO type DESC, date ASC, worldname.

Solution code below:

WITH dateAndLocation AS (
SELECT MIN(serverCETCreatedDate) AS earliest
  , pvpType
  , servLocation
  , MAX(serverCETCreatedDate) AS latest
FROM worlds
GROUP BY servlocation, pvpType
)
SELECT worlds.servLocation
  , worlds.pvpType
  , serverCETCreatedDate
  , worldName
FROM worlds
JOIN dateAndLocation
ON worlds.pvpType = dateAndLocation.pvpType
  AND worlds.servLocation = dateAndLocation.servLocation
  AND (
  (worlds.serverCETCreatedDate = dateAndLocation.earliest) 
  OR (worlds.serverCETCreatedDate = dateAndLocation.latest)
)
ORDER BY 1, 2 DESC, 3