Esim - Clutch Queries
Login:
Password:

Forgot password Register

Article


71
Clutch Queries (Old article)
Posted 10 years ago by
Phelps McManus    
Report


In response to inquiries from people interested in carrying on that clutch calculations, I decided to just publish the SQL used to make the report.

Obviously, this requires a database with the e-sim data in order to work. Everything is available from the API, and I will try to explain how it maps to relational database (MSSQL, MySQL, etc) tables.

FightEntities
This API call makes up the bulk of the data. I started with Battle #1, Round 1 in October, 2012 and sequentially pulled every round until I counted 8 wins (or got an error) and every battle since then. In total, I pulled 6,298 Battles, 65,682 Rounds, comprising of 25,170,143 Fights. I recommend anyone starting from scratch to consider pulling from battle #6000 forward.

RoundEntities
As I parsed the Battle API above to store fights into FightEntities, I aggregated some stats for each round and stored them in a separate table:
ID - Identity, did not match with BattleRoundID used by e-sim for AJAX updates.
BattleID - Foreign Key into BattleEntities
StartTime - Time stamp of first fight for that round. This should be the last entry in the Battle API result
EndTime - Time stamp of last fight for that round. This should be the first entry in the Battle API result
AttDamage - Attacker side total damage. SUM(CASE WHEN IsDefender THEN 0 ELSE Damage END)
DefDamage - Defender side total damage. SUM(CASE WHEN IsDefender THEN Damage ELSE 0 END)


BattleEntities
ID - Matches e-Sim battle id
IsCW - Is this a civil war? This would need to be flagged manually or unscrupulously web scraped. I decided to filter out civil war damage because, at the time, it was an efficient way for small, organized countries to generate gold by staging massive BH ties. This is not so much of a concern today, so you could probably safely ignore the distinction.
AttWins - Attacker wins, when this reaches 8, can move on to next battle pull
DefWins - Defender wins, when this reaches 8, can move on to next battle pull

CountryEntities
Manual entry. 1=Poland, etc...

CitizenHeader
After I ran my citizen clutch query, I would then pull the names of the citizens who made the list, unless they were already in my database. I only stored ID and name (login). Then I would re-run the clutch query, which spits out the data in the same format I use in my articles.

MUEntities
After I ran my military unit clutch query, I would then pull the names of the units that made the list, unless they were already in my database. I stored ID, name, and country, although I ended up filtering out country due to how quickly that data became stale.


Citizen Clutch Query

DECLARE @Start DATE = '2013-8-6'
DECLARE @End DATE = DATEADD(d,14,@Start)

--ALL TIME - Day 1-385
--DECLARE @Start DATE = '2012-1-1'
--DECLARE @End DATE = '2013-9-15'

;WITH Rounds(RoundID, CitizenID, Citizenship, Damage, IsDefender, AttDamage, DefDamage) AS
(
SELECT R.ID, F.CitizenID, MAX(F.Citizenship), SUM(F.Damage), F.IsDefender, R.AttDamage, R.DefDamage
FROM Secura.dbo.RoundEntities R
INNER JOIN Secura.dbo.FightEntities F ON R.ID = F.RoundID
INNER JOIN Secura.dbo.BattleEntities B ON R.BattleID = B.ID
WHERE R.StartTime >= @Start AND R.StartTime < @End AND B.IsCW=0
GROUP BY R.ID, F.CitizenID, F.IsDefender, R.AttDamage, R.DefDamage
)
,Clutch(CitizenID, Citizenship, TotalDamage, TotalRounds, ClutchRounds) AS
(
SELECT R.CitizenID, MAX(R.Citizenship), SUM(R.Damage), COUNT(R.RoundID)
,SUM(CASE WHEN R.IsDefender = 1 THEN
CASE WHEN R.Damage > (R.DefDamage - R.AttDamage) AND R.DefDamage > R.AttDamage THEN 1 ELSE 0 END
ELSE
CASE WHEN R.Damage > (R.AttDamage - R.DefDamage) AND R.AttDamage > R.DefDamage THEN 1 ELSE 0 END
END)
FROM Rounds R
GROUP BY R.CitizenID
),Ranks(Rank, CitizenID, Citizenship, TotalDamage, TotalRounds, ClutchRounds, ClutchPct) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY ClutchRounds DESC, TotalRounds)
, X.CitizenID, X.Citizenship, X.TotalDamage, X.TotalRounds, X.ClutchRounds, CAST(X.ClutchRounds AS REAL) / CAST(X.TotalRounds AS REAL)
FROM Clutch X
)
SELECT ISNULL(X.Rank,0) AS Rank,
C.Name, X.CitizenID, CC.Name AS Country, ISNULL(X.TotalDamage,0) AS TotalDamage, ISNULL(X.TotalRounds,0) AS TotalRounds
, ISNULL(X.ClutchRounds,0) AS ClutchRounds
FROM Ranks X
LEFT JOIN Secura.dbo.CitizenHeader C ON X.CitizenID = C.ID
INNER JOIN Secura.dbo.CountryEntities CC ON X.Citizenship = CC.ID
--WHERE X.CitizenID=36605 --ggarbi
ORDER BY Rank


Military Unit Clutch Query
Note requirement of HAVING at least 100,000,000 damage over the 2 week period.

DECLARE @Start DATE = '2013-8-6'
DECLARE @End DATE = DATEADD(d,14,@Start)

;WITH MURounds(RoundID, MUID, Damage, IsDefender) AS
(
SELECT R.ID, F.MUID, SUM(CAST(F.Damage AS BIGINT)), F.IsDefender
FROM Secura.dbo.RoundEntities R
INNER JOIN Secura.dbo.FightEntities F ON R.ID = F.RoundID
INNER JOIN Secura.dbo.BattleEntities B ON R.BattleID = B.ID
WHERE R.StartTime >= @Start AND R.StartTime < @End AND F.MUID IS NOT NULL AND B.IsCW=0
GROUP BY R.ID, F.MUID, F.IsDefender
)
SELECT MR.MUID, M.Name, C.Name AS Country, SUM(MR.Damage) AS TotalDamage , COUNT(MR.RoundID) AS TotalRounds
,SUM(CASE WHEN MR.IsDefender = 1 THEN
CASE WHEN MR.Damage > (R.DefDamage - R.AttDamage) AND R.DefDamage > R.AttDamage THEN 1 ELSE 0 END
ELSE
CASE WHEN MR.Damage > (R.AttDamage - R.DefDamage) AND R.AttDamage > R.DefDamage THEN 1 ELSE 0 END
END) AS ClucthRounds
,SUM(CASE WHEN MR.IsDefender = 1 THEN
CASE WHEN MR.Damage > (R.DefDamage - R.AttDamage) AND R.DefDamage > R.AttDamage THEN 1 ELSE 0 END
ELSE
CASE WHEN MR.Damage > (R.AttDamage - R.DefDamage) AND R.AttDamage > R.DefDamage THEN 1 ELSE 0 END
END) / CAST(COUNT(MR.RoundID) AS REAL) * 100.0 AS Pct
FROM MURounds MR
LEFT JOIN Secura.dbo.MUEntities M ON MR.MUID=M.ID
INNER JOIN Secura.dbo.RoundEntities R ON MR.RoundID = R.ID
INNER JOIN Secura.dbo.CountryEntities C ON M.CountryID = C.ID
GROUP BY M.Name, MR.MUID, C.Name
HAVING SUM(MR.Damage)> 100000000
ORDER BY Pct DESC

Previous article:
Final Clutch List - Day 1 to 385 (10 years ago)

ESim
or
Register for free:
Only letters, numbers, underscore and space are allowed (A-Z,a-z,0-9,_,' ')
Show more

By clicking 'Sign Up!', you agree to the Rules and that you have read the Privacy Policy.

About the game:


USA as a world power? In E-Sim it is possible!

In E-Sim we have a huge, living world, which is a mirror copy of the Earth. Well, maybe not completely mirrored, because the balance of power in this virtual world looks a bit different than in real life. In E-Sim, USA does not have to be a world superpower, It can be efficiently managed as a much smaller country that has entrepreneurial citizens that support it's foundation. Everything depends on the players themselves and how they decide to shape the political map of the game.

Work for the good of your country and see it rise to an empire.

Activities in this game are divided into several modules. First is the economy as a citizen in a country of your choice you must work to earn money, which you will get to spend for example, on food or purchase of weapons which are critical for your progress as a fighter. You will work in either private companies which are owned by players or government companies which are owned by the state. After progressing in the game you will finally get the opportunity to set up your own business and hire other players. If it prospers, we can even change it into a joint-stock company and enter the stock market and get even more money in this way.


In E-Sim, international wars are nothing out of the ordinary.

"E-Sim is one of the most unique browser games out there"

Become an influential politician.

The second module is a politics. Just like in real life politics in E-Sim are an extremely powerful tool that can be used for your own purposes. From time to time there are elections in the game in which you will not only vote, but also have the ability to run for the head of the party you're in. You can also apply for congress, where once elected you will be given the right to vote on laws proposed by your fellow congress members or your president and propose laws yourself. Voting on laws is important for your country as it can shape the lives of those around you. You can also try to become the head of a given party, and even take part in presidential elections and decide on the shape of the foreign policy of a given state (for example, who to declare war on). Career in politics is obviously not easy and in order to succeed in it, you have to have a good plan and compete for the votes of voters.


You can go bankrupt or become a rich man while playing the stock market.

The international war.

The last and probably the most important module is military. In E-Sim, countries are constantly fighting each other for control over territories which in return grant them access to more valuable raw materials. For this purpose, they form alliances, they fight international wars, but they also have to deal with, for example, uprisings in conquered countries or civil wars, which may explode on their territory. You can also take part in these clashes, although you are also given the opportunity to lead a life as a pacifist who focuses on other activities in the game (for example, running a successful newspaper or selling products).


At the auction you can sell or buy your dream inventory.

E-Sim is a unique browser game. It's creators ensured realistic representation of the mechanisms present in the real world and gave all power to the players who shape the image of the virtual Earth according to their own. So come and join them and help your country achieve its full potential.


Invest, produce and sell - be an entrepreneur in E-Sim.


Take part in numerous events for the E-Sim community.


forum | Terms of Service | Privacy policy | Support | Wikia | Alpha | Luxia | Primera | Secura | Suna | Xena | Mega | Arcadia | esim political game
PLAY ON