Defeats To Elden Ring's Malenia Data Analysis

Malenia Project

Image: Defeats To Elden Ring's Malenia Dashboard, created using Power BI

Technologies used: SQL Server (T-SQL), Excel, Power BI
Dashboard Files: malenia_dashboard.pbix, malenia_excel_dashboard.xlsx
Dataset: Malenia-DB.xls
T-SQL Queries: Malenia_Queries_1.sql
Github: malenia-DB-T-SQL

"I am Malenia. Blade of Miquella. And I have never known defeat." Be honest. How many times did you lose to Malenia?

In case you are not too well acquainted with video games, Elden Ring is an action RPG developed by FromSoftware. It was a highly anticipated game and arguably lived up to its hype, featuring iconic boss fights - one of which is Malenia.

Malenia, Blade of Miquella. She's perhaps the most notorious boss in the game, owing to - you guessed it - how strong she is. Both casual and more adept players could find themselves putting in an unbelievable amount of time in trying to beat her. She has never known defeat after all.

In this project, I cleaned, queried and visualized a dataset (source: Kaggle Datasets) about player deaths to Malenia. It comprises the players' location, health, minimum damage dealt and more, at the moment of demise. The data cleaning was accomplished using Microsoft Excel, the queries were written in T-SQL (SQL Server) and the data's important aspect were visualized in dashboard built using Microsoft Power BI.

Some queries...

See the full list of queries here.

Finding the player build with the highest min. damage dealt.

Input: 

SELECT TOP(1) Host_Build AS [Host Build with Most Min Damage], Min_Damage AS [Min Damage]
FROM MaleniaDB..MaleniaDB
ORDER BY Min_Damage DESC
Output:

Host Build with Most Min Damage Min Damage
HYBRID 94

• It seems like players not focusing on a specific build dealt the most min. damage to Malenia. Perhaps, having hybrid build provides flexibility and more opportunity to deal damage and survive longer.

Finding the level with the highest number of deaths for each player build.

Input: 

WITH deaths_per_build AS (
	SELECT Host_Build, [Level], COUNT(*) AS [No Of Deaths]
	FROM MaleniaDB..MaleniaDB
	GROUP BY Host_Build, [Level]
)
SELECT dpb1.Host_Build, dpb1.[Level] AS [Level With Most Deaths], dpb1.[No Of Deaths]
FROM deaths_per_build dpb1
WHERE dpb1.[No Of Deaths] = (
	SELECT MAX(dpb2.[No Of Deaths])
	FROM deaths_per_build dpb2
	WHERE dpb1.Host_Build = dpb2.Host_Build
)
Output:

Host_Build Level With Most Deaths No Of Deaths
RAW_MELEE 132 23
RAW_CAST 162 19
RAW_CAST 179 19
PROC_MELEE 120 21
PROC_MELEE 180 21
PROC_CAST 138 19
HYBRID 166 23

• Even experienced players lose numerous times with a hybrid build. 🤔

Finding the level with the most deaths, the location that yielded the most deaths and if more than 50% of the players used a phantom for each host build.

Input: 

WITH host_level_deathcount AS (
	SELECT Host_Build, [Level], COUNT(*) AS [Death Count]
	FROM MaleniaDB..MaleniaDB
	GROUP BY Host_Build, [Level]
),
host_level_most_deaths AS (
	SELECT hld1.Host_Build, hld1.[Level], hld1.[Death Count]
	FROM host_level_deathcount hld1
	WHERE hld1.[Death Count] = (
		SELECT MAX([Death Count])
		FROM host_level_deathcount hld2
		WHERE hld1.Host_Build = hld2.Host_Build
	)
),
host_loc_deathcount AS (
	SELECT Host_Build, [Location], COUNT(*) AS [Death Count]
	FROM MaleniaDB..MaleniaDB
	GROUP BY Host_Build, [Location]
),
host_loc_most_deaths AS (
	SELECT hld1.Host_Build, hld1.[Location], hld1.[Death Count]
	FROM host_loc_deathcount hld1
	WHERE hld1.[Death Count] = (
		SELECT MAX([Death Count])
		FROM host_loc_deathcount hld2
		WHERE hld1.Host_Build = hld2.Host_Build
	)
),
host_deathcount AS (
	SELECT Host_Build, 
		   CAST(COUNT(*) AS FLOAT) AS Host_DeathCount
	FROM MaleniaDB..MaleniaDB
	GROUP BY Host_Build
),
host_phantuser_count AS (
	SELECT Host_Build,
		   CAST(COUNT(*) AS FLOAT) AS Phant_UserCount
	FROM MaleniaDB..MaleniaDB
	WHERE Phantom_Count = 2
	GROUP BY Host_Build
),
host_ifmajor_phant AS (
	SELECT hd.Host_Build,
		   CASE 
			WHEN (hpc.Phant_UserCount / hd.Host_DeathCount) > 0.5 THEN '>50% used a phantom'
				 ELSE '>50% did NOT use a phantom'
		   END AS [Mostly Used Phantom?]
	FROM host_phantuser_count hpc INNER JOIN
		 host_deathcount hd ON
		 hpc.Host_Build = hd.Host_Build
)
SELECT level_md.Host_Build AS [Host Build],
	   level_md.[Level] AS [Level(s) With Most Deaths],
	   loc_md.[Location] AS [Location(s) With Most Deaths],
	   maj_phant.[Mostly Used Phantom?]
FROM host_level_most_deaths level_md INNER JOIN
	 host_loc_most_deaths loc_md ON
	 level_md.Host_Build = loc_md.Host_Build INNER JOIN
	 host_ifmajor_phant maj_phant ON
	 maj_phant.Host_Build = level_md.Host_Build

Output:

Host Build Level(s) With Most Deaths Location(s) With Most Deaths Mostly Used Phantom?
RAW_MELEE 132 CENTER >50% used a phantom
RAW_CAST 162 CENTER >50% used a phantom
RAW_CAST 179 CENTER >50% used a phantom
PROC_MELEE 120 CENTER >50% used a phantom
PROC_MELEE 180 CENTER >50% used a phantom
PROC_CAST 138 CENTER >50% used a phantom
HYBRID 166 OUTER >50% used a phantom

• For every host build, the majority of players used a phantom.
• Players with hybrid builds mostly died in the outer area of the arena.

Citations

Jordan Carlen. 2022. Elden Ring: Deaths to Malenia, Blade of Miquella, Version 3. [Online Resource]