Useful SQL Queries

From Mysqlgame

Here are a few useful queries you can perform on the database dumps to find out various things.

You can use these queries on this database provided by Tom. (select a shard/database on the left (go) then click the SQL tab on the right hand side. Paste in a query from below and click go.)

Contents 1 Ranking By Total Value 2 Most Money, Fuel, etc By Clique In A Shard 3 Most Money, Fuel, etc By Player In A Shard 4 Most Money, Fuel, etc By Row In A Shard 5 Defensive Power By Clique/Player/Row In A Shard 6 Find Rows Owned By A Player/Clique, and Farms 7 Find Vulnerable Rows 8 Find Clique Admins

Ranking By Total Value

Player Rank By Total Value In A Shard

SET @rank=0; SELECT @rank:=@rank+1 rank, name, value, rows FROM (SELECT player_id, players.name, COUNT(row_id) rows FROM rows, players WHERE owner = player_id GROUP BY player_id) rows NATURAL JOIN (SELECT player_id, SUM(value) value FROM (SELECT player_id, SUM(POW(2,money_factories+1)*10-40)+SUM(POW(2,fuel_factories+1)*7.5-30)+SUM(POW(2,attack_multiplier+1)*125-500)+SUM(POW(2,defense_multiplier+1)*125-500)+ROUND(POW(1.5,COUNT(row_id)-1)*160000)-160000+SUM(money)+SUM(attackers)*20+SUM(defenders)*10 value FROM rows, players WHERE owner = player_id GROUP BY player_id) value GROUP BY player_id) value ORDER BY value DESC

Player Rank By Average Total Value Per Row In A Shard

Clique Rank By #Rows, #Players, Total Value In A Shard

SET @rank=0; SELECT @rank:=@rank+1 rank, name, rows, players, value FROM (SELECT clique_id, cliques.name, COUNT(row_id) rows, COUNT(DISTINCT player_id) players FROM rows, players, cliques WHERE owner = player_id AND clique = clique_id GROUP BY clique_id) rows NATURAL JOIN (SELECT clique_id, SUM(value) value FROM (SELECT clique_id, SUM(POW(2,money_factories+1)*10-40)+SUM(POW(2,fuel_factories+1)*7.5-30)+SUM(POW(2,attack_multiplier+1)*125-500)+SUM(POW(2,defense_multiplier+1)*125-500)+ROUND(POW(1.5,COUNT(row_id)-1)*160000)-160000+SUM(money)+SUM(attackers)*20+SUM(defenders)*10 value FROM rows, players, cliques WHERE owner = player_id AND clique = clique_id GROUP BY player_id) value GROUP BY clique_id) value ORDER BY value DESC

Clique Rank By #Rows, #Players, Average Total Value Per Row In A Shard

Most Money, Fuel, etc By Clique In A Shard

Most Money By Clique In A Shard

SELECT cliques.name as cliquename, sum(rows.money) AS money FROM cliques, players, rows WHERE players.player_id = rows.owner AND cliques.clique_id = players.clique GROUP BY cliquename ORDER BY money DESC

Most Fuel By Clique In A Shard

SELECT cliques.name as cliquename, sum(rows.fuel) AS fuel FROM cliques, players, rows WHERE players.player_id = rows.owner AND cliques.clique_id = players.clique GROUP BY cliquename ORDER BY fuel DESC

Most Rows By Clique In A Shard

SELECT cliques.name as name, count(rows.owner) as count FROM cliques, players, rows WHERE players.player_id = rows.owner AND cliques.clique_id = players.clique GROUP BY name ORDER BY count DESC

Most Members In Clique In A Shard

SELECT cliques.name as name, count(players.clique) as count FROM cliques, players WHERE cliques.clique_id = players.clique GROUP BY name ORDER BY count DESC

Most Money, Fuel, etc By Player In A Shard

Most Money By Player In A Shard

SELECT players.name AS player, sum(rows.money) AS money FROM rows, players WHERE players.player_id = rows.owner GROUP BY rows.owner ORDER BY money DESC

Most Fuel By Player In A Shard

SELECT players.name AS player, sum(rows.fuel) AS fuel FROM rows, players WHERE players.player_id = rows.owner GROUP BY rows.owner ORDER BY fuel DESC

Most Rows By Player In A Shard

SELECT players.name AS player, count(owner) AS rows FROM rows, players WHERE players.player_id = rows.owner GROUP BY rows.owner ORDER BY rows DESC

Most Money, Fuel, etc By Row In A Shard

Most Money By Row In A Shard

SELECT players.name AS Owner, rows.row_id AS Row, rows.money AS Money FROM players, rows WHERE players.player_id = rows.owner ORDER by Money DESC

Most Fuel By Row In A Shard

SELECT players.name AS Owner, rows.row_id AS Row, rows.fuel AS Fuel FROM players, rows WHERE players.player_id = rows.owner ORDER by Fuel DESC

Defensive Power By Clique/Player/Row In A Shard

Defensive Power By Clique In A Shard

Defensive Power By Player In A Shard

SELECT players.name,SUM(rows.attackers*rows.attack_multiplier+rows.defenders*rows.defense_multiplier) AS defense_power, SUM(rows.attackers*rows.attack_multiplier) AS attack_power, COUNT(rows.defenders) AS rows FROM rows, players WHERE players.player_id = rows.owner GROUP BY rows.owner ORDER BY defense_power DESC

Defensive Power By Row In A Shard

SELECT players.name, rows.row_id, (rows.attackers*rows.attack_multiplier+rows.defenders*rows.defense_multiplier) AS total_power, rows.attackers, rows.attack_multiplier, (rows.attackers*rows.attack_multiplier) AS attack_power, rows.defenders, rows.defense_multiplier, (rows.defenders*rows.defense_multiplier) AS defense_power FROM rows, players WHERE players.player_id = rows.owner ORDER BY total_power DESC

Find Rows Owned By A Player/Clique, and Farms

Find Rows Owned By A Player

SELECT row_id, money_factories AS MF, money, fuel_factories AS FF, fuel, attackers, attack_multiplier AS AM, defenders, defense_multiplier AS DM FROM rows, players WHERE players.player_id = rows.owner AND players.name LIKE "lunacy"

Note: if the tables are case insensitive you may get unexpected results for names that differ from others only by capitalization, e.g. "example" vs "Example" are treated the same under a case insensitive table.

Find Rows Owned By A Clique

SELECT cliques.name, row_id, money, fuel, attackers, attack_multiplier AS AM, defenders, defense_multiplier AS DM FROM `rows`, players, cliques WHERE players.player_id = rows.owner AND players.clique = cliques.clique_id AND cliques.name LIKE "outsid"

Note: if the tables are case insensitive you may get unexpected results for names that differ from others only by capitalization, e.g. "example" vs "Example" are treated the same under a case insensitive table.

Find A Farm

SELECT row_id, money, fuel, attackers, attack_multiplier AS AM, defenders, defense_multiplier AS DM FROM `rows` WHERE rows.defenders <= 3

Find Farms Closest To Given Row

SET @your_row_id = 1337;
SELECT row_id, money, fuel, attackers, attack_multiplier AS AM, defenders, defense_multiplier AS DM, ABS(row_id - @your_row_id) AS distance FROM `rows` WHERE rows.defenders <= 3 ORDER BY distance ASC;

Note: Put your row_id in @your_row_id variable.

Find High Level Farms Closest To Given Row

SET @your_row_id = 1337;
SELECT row_id, money, fuel, attackers, attack_multiplier AS AM, defenders, defense_multiplier AS DM, ABS(row_id - @your_row_id) AS distance FROM `rows` WHERE rows.defenders <= 3 AND (attack_multiplier >= 11 OR defense_multiplier >= 11) ORDER BY distance ASC;

Note: Put your row_id in @your_row_id variable.

Players That Like Resource Factories

SELECT name, (tot_money_fac + tot_fuel_fac) AS total FROM `totals` ORDER BY total DESC

Find Vulnerable Rows

When will row ### be vulnerable?

SELECT who.name AS Player, who.last AS Last_login, sum(rows.money_factories) + sum(rows.fuel_factories) + sum(rows.attack_multiplier) + sum(rows.defense_multiplier) AS idle_days_until_vulnerable, who.last + INTERVAL sum(rows.money_factories) + sum(rows.fuel_factories) + sum(rows.attack_multiplier) + sum(rows.defense_multiplier) Day AS date_vulnerable FROM (SELECT players.player_id AS player, players.name AS name, players.last_logged_in AS last FROM players INNER JOIN rows ON rows.owner = players.player_id WHERE rows.row_id = ###) who INNER JOIN rows ON rows.owner = who.player WHERE rows.owner = who.player

Note: Replace ### in the query with the row number.

List next 30 vulnerable players by vulnerability date.

SELECT * FROM (SELECT players.name AS Player, players.last_logged_in AS Last_login, sum(rows.money_factories) + sum(rows.fuel_factories) + sum(rows.attack_multiplier) + sum(rows.defense_multiplier) AS idle_days_until_vulnerable, players.last_logged_in + INTERVAL sum(rows.money_factories) + sum(rows.fuel_factories) + sum(rows.attack_multiplier) + sum(rows.defense_multiplier) Day AS date_vulnerable FROM players INNER JOIN rows ON rows.owner = players.player_id GROUP BY player_id ORDER BY date_vulnerable) AS dated WHERE date_vulnerable > NOW() LIMIT 0,30

List next 30 vulnerable players and their rows, by vulnerability date.

SET group_concat_max_len = 512; SELECT * FROM (SELECT players.name AS Player, sum(rows.money_factories) + sum(rows.fuel_factories) + sum(rows.attack_multiplier) + sum(rows.defense_multiplier) AS idle_days_until_vulnerable, players.last_logged_in + INTERVAL sum(rows.money_factories) + sum(rows.fuel_factories) + sum(rows.attack_multiplier) + sum(rows.defense_multiplier) Day AS date_vulnerable, group_concat(rows.row_id ORDER BY rows.row_id SEPARATOR ', ') AS rowlist FROM players INNER JOIN rows ON rows.owner = players.player_id GROUP BY player_id) AS dated WHERE date_vulnerable > NOW() ORDER BY date_vulnerable LIMIT 0,30

Find Clique Admins

Find Clique Admins

SELECT cliques.name AS Clique, players.name AS Name, clique_super_admin AS `Super Admin`, clique_admin AS `Admin` FROM `players` , `cliques` WHERE cliques.clique_id = players.clique AND (players.clique_super_admin = 1 OR players.clique_admin = 1) ORDER BY players.clique_super_admin DESC, cliques.name