# Battle for Evermore Database backups

create table 2023season1matches AS (select * from matches);

create table 2023season1matchPlayers AS (select * from matchPlayers);

select count(matchId), sum(winner), sum(kills), sum(deaths), sum(totalDamageDealt), sum(totalDamageReceived), sum(totalHealing), sum(mobKills) from 2022season2matchPlayers group by nftId order by count(deaths) desc limit 5;

Deaths
select walletId, nftId, count(matchId), sum(deaths) from 2022season2matchPlayers group by nftId order by sum(deaths) desc limit 5;

+--------------------------------------------+-------+----------------+-------------+
| walletId                                   | nftId | count(matchId) | sum(deaths) |
+--------------------------------------------+-------+----------------+-------------+
| 0x4accdfd779e8fa60a3686030a6eb36d663399e13 | 7781  |             71 |         129 |
| 0x3f0f99ec5a55a51c55e2dfb732332437213b5650 | 7187  |             50 |          54 |
| 0x0d671e7a64f0072d4b98b5847ab5925ce8fad86d | 9258  |             39 |          27 |
| 0x4212d149f77308a87ce9928f1095eddb894f4d68 | 1644  |             30 |          53 |
| 0x095b2ce3c0b77bcd0aebf7d6b0ea12d0284422dc | 2419  |             30 |           7 |
+--------------------------------------------+-------+----------------+-------------+

Matches
select walletId, nftId, count(matchId), sum(deaths) from 2022season2matchPlayers group by nftId order by count(matchId) desc limit 5;

Damage Dealt
sum(totalDamageDealt)
select walletId, nftId, count(matchId), sum(totalDamageDealt) from 2022season2matchPlayers group by nftId order by sum(totalDamageDealt) desc limit 5;

+--------------------------------------------+-------+----------------+-----------------------+
| walletId                                   | nftId | count(matchId) | sum(totalDamageDealt) |
+--------------------------------------------+-------+----------------+-----------------------+
| 0x3f0f99ec5a55a51c55e2dfb732332437213b5650 | 7187  |             50 |                990367 |
| 0x095b2ce3c0b77bcd0aebf7d6b0ea12d0284422dc | 2419  |             30 |                981796 |
| 0x095b2ce3c0b77bcd0aebf7d6b0ea12d0284422dc | 7657  |             21 |                929297 |
| 0x4accdfd779e8fa60a3686030a6eb36d663399e13 | 7781  |             71 |                818160 |
| 0x0d671e7a64f0072d4b98b5847ab5925ce8fad86d | 9258  |             39 |                784695 |
+--------------------------------------------+-------+----------------+-----------------------+```

Damage Dealt
sum(totalDamageDealt)
select walletId, nftId, count(matchId), sum(totalDamageDealt) from 2022season2matchPlayers group by nftId order by sum(totalDamageDealt) desc limit 5;

SELECT DISTINCT walletId
FROM 2022season2matchPlayers
WHERE matchId IN (
    SELECT id
    FROM 2022season2matches
    WHERE mapName = 'twister'
        AND openDate >= '2022-12-06'
        AND openDate < '2022-12-08'
)

SELECT DISTINCT walletId
FROM 2022season2matchPlayers
WHERE matchId IN (
    SELECT id
    FROM 2022season2matches
    WHERE mapName = 'spider'
        AND openDate >= '2022-10-01'
        AND openDate < '2022-11-07'
)
 
+--------------------------------------------+
| walletId                                   |
+--------------------------------------------+
| 0xfacef700458d4fc9746f7f3e0d37b462711ff09e |
| 0x496375c41220c62d73c5993684ad08e64aad72f3 |
| 0x3f0f99ec5a55a51c55e2dfb732332437213b5650 |
| 0x5117856736e109ad021f0d0a2c23a3bb99d5b072 |
| 0x4212d149f77308a87ce9928f1095eddb894f4d68 |
| 0x095b2ce3c0b77bcd0aebf7d6b0ea12d0284422dc |
| 0x4accdfd779e8fa60a3686030a6eb36d663399e13 |
| 0x1776cfdcffc21cd51b35d1efaf5b3db4848da1d7 |
| 0xd5739bf2f0c9d79ea5eea08994f528faef377f0c |
| 0x0d671e7a64f0072d4b98b5847ab5925ce8fad86d |
| 0x0d7baa12d4a2925035271c809690e02b73a5016d |
| 0x1509243cef3bf9b5a046d2d572eb13d2e44f882b |
+--------------------------------------------+

select walletId, nftId, count(winner), count(matchId), count(winner) - count(matchId) as win_ratio from 2022season2matchPlayers group by nftId having count(matchId) > 5 order
 by win_ratio desc limit 5;
+--------------------------------------------+-------+---------------+----------------+-----------+
| walletId                                   | nftId | count(winner) | count(matchId) | win_ratio |
+--------------------------------------------+-------+---------------+----------------+-----------+
| 0x496375c41220c62d73c5993684ad08e64aad72f3 | 6422  |            13 |             13 |         0 |
| 0x496375c41220c62d73c5993684ad08e64aad72f3 | 6660  |             8 |              8 |         0 |
| 0xfacef700458d4fc9746f7f3e0d37b462711ff09e | 1879  |             9 |              9 |         0 |
| 0x293478d314bc925fc80d5af847fe15a14e5b380b | 6448  |             6 |              6 |         0 |
| 0xfacef700458d4fc9746f7f3e0d37b462711ff09e | 2114  |             5 |              6 |        -1 |
+--------------------------------------------+-------+---------------+----------------+-----------+

select walletId, nftId, sum(kills), sum(deaths), sum(kills) - sum(deaths) as pkscore from 2022season2matchPlayers group by nftId order by pkscore desc limit 5;
+--------------------------------------------+-------+------------+-------------+---------+
| walletId                                   | nftId | sum(kills) | sum(deaths) | pkscore |
+--------------------------------------------+-------+------------+-------------+---------+
| 0x095b2ce3c0b77bcd0aebf7d6b0ea12d0284422dc | 7657  |         83 |          16 |      67 |
| 0x293478d314bc925fc80d5af847fe15a14e5b380b | 795   |         58 |          25 |      33 |
| 0x496375c41220c62d73c5993684ad08e64aad72f3 | 6660  |         35 |          10 |      25 |
| 0x496375c41220c62d73c5993684ad08e64aad72f3 | 5002  |         35 |          12 |      23 |
| 0x496375c41220c62d73c5993684ad08e64aad72f3 | 1841  |         29 |           9 |      20 |
+--------------------------------------------+-------+------------+-------------+---------+

select walletId, count(matchId) from 2022season2matchPlayers group by nftId order by count(matchId) desc limit 5;
+--------------------------------------------+----------------+
| walletId                                   | count(matchId) |
+--------------------------------------------+----------------+
| 0x4accdfd779e8fa60a3686030a6eb36d663399e13 |             71 |
| 0x3f0f99ec5a55a51c55e2dfb732332437213b5650 |             50 |
| 0x0d671e7a64f0072d4b98b5847ab5925ce8fad86d |             39 |
| 0x4212d149f77308a87ce9928f1095eddb894f4d68 |             30 |
| 0x095b2ce3c0b77bcd0aebf7d6b0ea12d0284422dc |             30 |
+--------------------------------------------+----------------+

select walletId, count(winner) from 2022season2matchPlayers group by nftId order by count(winner) desc limit 5;
+--------------------------------------------+---------------+
| walletId                                   | count(winner) |
+--------------------------------------------+---------------+
| 0x3f0f99ec5a55a51c55e2dfb732332437213b5650 |            43 |
| 0x4accdfd779e8fa60a3686030a6eb36d663399e13 |            42 |
| 0x0d671e7a64f0072d4b98b5847ab5925ce8fad86d |            27 |
| 0x095b2ce3c0b77bcd0aebf7d6b0ea12d0284422dc |            24 |
| 0x4212d149f77308a87ce9928f1095eddb894f4d68 |            19 |
+--------------------------------------------+---------------+

select walletId, sum(kills) from 2022season2matchPlayers group by nftId order by sum(kills) desc limit 5;
+--------------------------------------------+------------+
| walletId                                   | sum(kills) |
+--------------------------------------------+------------+
| 0x095b2ce3c0b77bcd0aebf7d6b0ea12d0284422dc |         83 |
| 0x293478d314bc925fc80d5af847fe15a14e5b380b |         58 |
| 0x3f0f99ec5a55a51c55e2dfb732332437213b5650 |         57 |
| 0x496375c41220c62d73c5993684ad08e64aad72f3 |         35 |
| 0x496375c41220c62d73c5993684ad08e64aad72f3 |         35 |
+--------------------------------------------+------------+

select walletId, sum(deaths) from 2022season2matchPlayers group by nftId order by sum(deaths) desc limit 5;
+--------------------------------------------+-------------+
| walletId                                   | sum(deaths) |
+--------------------------------------------+-------------+
| 0x4accdfd779e8fa60a3686030a6eb36d663399e13 |         129 |
| 0x0d7baa12d4a2925035271c809690e02b73a5016d |          60 |
| 0x4accdfd779e8fa60a3686030a6eb36d663399e13 |          55 |
| 0x3f0f99ec5a55a51c55e2dfb732332437213b5650 |          54 |
| 0x4212d149f77308a87ce9928f1095eddb894f4d68 |          53 |
+--------------------------------------------+-------------+


select walletId, count(matchId), sum(totalDamageDealt) from 2022season2matchPlayers group by nftId order by sum(totalDamageDealt) desc limit 5;
+--------------------------------------------+----------------+-----------------------+
| walletId                                   | count(matchId) | sum(totalDamageDealt) |
+--------------------------------------------+----------------+-----------------------+
| 0x3f0f99ec5a55a51c55e2dfb732332437213b5650 |             50 |                990367 |
| 0x095b2ce3c0b77bcd0aebf7d6b0ea12d0284422dc |             30 |                981796 |
| 0x095b2ce3c0b77bcd0aebf7d6b0ea12d0284422dc |             21 |                929297 |
| 0x4accdfd779e8fa60a3686030a6eb36d663399e13 |             71 |                818160 |
| 0x0d671e7a64f0072d4b98b5847ab5925ce8fad86d |             39 |                784695 |
+--------------------------------------------+----------------+-----------------------+

select walletId, sum(totalDamageReceived) from 2022season2matchPlayers group by nftId order by sum(totalDamageReceived) desc limit 5;
+--------------------------------------------+--------------------------+
| walletId                                   | sum(totalDamageReceived) |
+--------------------------------------------+--------------------------+
| 0x0d671e7a64f0072d4b98b5847ab5925ce8fad86d |                   709256 |
| 0x3f0f99ec5a55a51c55e2dfb732332437213b5650 |                   533719 |
| 0x095b2ce3c0b77bcd0aebf7d6b0ea12d0284422dc |                   518160 |
| 0x4accdfd779e8fa60a3686030a6eb36d663399e13 |                   500242 |
| 0x0d7baa12d4a2925035271c809690e02b73a5016d |                   415410 |
+--------------------------------------------+--------------------------+

select walletId, sum(totalHealing) from 2022season2matchPlayers group by nftId order by sum(totalHealing) desc limit 5;
+--------------------------------------------+-------------------+
| walletId                                   | sum(totalHealing) |
+--------------------------------------------+-------------------+
| 0x0d671e7a64f0072d4b98b5847ab5925ce8fad86d |            298452 |
| 0xfacef700458d4fc9746f7f3e0d37b462711ff09e |            112553 |
| 0x4212d149f77308a87ce9928f1095eddb894f4d68 |            102578 |
| 0x095b2ce3c0b77bcd0aebf7d6b0ea12d0284422dc |             87805 |
| 0xfacef700458d4fc9746f7f3e0d37b462711ff09e |             67527 |
+--------------------------------------------+-------------------+

select walletId, sum(mobKills) from 2022season2matchPlayers group by nftId order by sum(mobKills) desc limit 5;
+--------------------------------------------+---------------+
| walletId                                   | sum(mobKills) |
+--------------------------------------------+---------------+
| 0x095b2ce3c0b77bcd0aebf7d6b0ea12d0284422dc |           512 |
| 0x4accdfd779e8fa60a3686030a6eb36d663399e13 |           441 |
| 0x3f0f99ec5a55a51c55e2dfb732332437213b5650 |           398 |
| 0x095b2ce3c0b77bcd0aebf7d6b0ea12d0284422dc |           346 |
| 0x095b2ce3c0b77bcd0aebf7d6b0ea12d0284422dc |           215 |
+--------------------------------------------+---------------+

SELECT DISTINCT walletId
FROM 2022season2matchPlayers
WHERE matchId IN (
    SELECT id
    FROM 2022season2matches
    WHERE mapName = 'tutorial'
        AND openDate >= '2022-01-01'
        AND openDate < '2022-12-31'
)

+--------------------------------------------+
| walletId                                   |
+--------------------------------------------+
| 0x9017c92270f465fe35c8adf47ad9b2c344765561 |
| 0x8760e565273b47195f76a22455ce0b68a11af5b5 |
| 0x0f4663223411e3cc280b1cd69aa50deffa7788a2 |
| 0x4b1e684d5143eb7435e5d78385fa8cbdccd8f7ec |
| 0x5117856736e109ad021f0d0a2c23a3bb99d5b072 |
| 0x496375c41220c62d73c5993684ad08e64aad72f3 |
| 0x0d7baa12d4a2925035271c809690e02b73a5016d |
| 0xe9822f18f2654e606a8dff9d75edd98367e7c0ae |
| 0x095b2ce3c0b77bcd0aebf7d6b0ea12d0284422dc |
| 0x4accdfd779e8fa60a3686030a6eb36d663399e13 |
| 0xfacef700458d4fc9746f7f3e0d37b462711ff09e |
| 0x3f0f99ec5a55a51c55e2dfb732332437213b5650 |
+--------------------------------------------+

Birthday cake
0xfacef700458d4fc9746f7f3e0d37b462711ff09e
0x4212d149f77308a87ce9928f1095eddb894f4d68
Long Bow
0x4212d149f77308a87ce9928f1095eddb894f4d68


Crowns:
0x095b2ce3c0b77bcd0aebf7d6b0ea12d0284422dc

belts: