Interesting SQL Queries: Server Statistics

[maksumic]

Find Average Look Type Color

The query finds how many players use a look type and the average colors for that look type.

SELECT COUNT(`looktype`)         AS `NumberOfPlayers`,
       `looktype`                AS `Type`,
       ROUND(AVG(`lookhead`), 0) AS `Head`,
       ROUND(AVG(`lookbody`), 0) AS `Body`,
       ROUND(AVG(`looklegs`), 0) AS `Legs`,
       ROUND(AVG(`lookfeet`), 0) AS `Feet`
FROM     `players`
GROUP BY `looktype`
ORDER BY `NumberOfPlayers` DESC;

Output from my old server:

E.g.: 975 players used the look type 128 with the average colors: 77 for head, 70 for body, 58 for legs, 103 for feet
Replace the numbers in this url to match your numbers: sleqqus.com/tlg/#128:77:70:58:103:0:0

The result of the first outfit

is very close to the starting outfit

The result of the second outfit

is ugly..

Skipping 3..

The result of the fourth outfit

is interesting.

Have fun


Average Level by Vocation

SELECT   `vocation`, ROUND(AVG(`level`), 0) AS `avg. level`
FROM     `players`
GROUP BY `vocation`;

Example output:



Most Popular Player Items in Server

SELECT    COUNT(`itemtype`) AS `Count`, `itemtype` AS `Item ID`
FROM      `player_items`
GROUP BY  `itemtype`
ORDER BY  `Count` DESC;

Top 10 Most Popular Donation Items in Shop
Most frequently purchased items in shop

SELECT    `offer_id` AS `Item`, COUNT(`offer_id`) AS `# Purchases`
FROM      `z_shop_history_item`
GROUP BY  `Item`
ORDER BY  `# Purchases` DESC
LIMIT     10;

 

[Exeus]

Avg Balance per profession?

Coorelation between pvp deaths and level? - e.g low lvls tend to not die in pvp, 300 lvls tend to die in pvp a lot, 700 lvls rarely die in pvp

 

[FenX]

Cool stuff, would be nice to see what else people can come up with