sample_queries.sql

-- Selecting IPs used by a username
SELECT username, ip FROM ipd_pretty_associations WHERE username = '%s' ORDER BY count DESC;

-- Selecting usernames that have used a specific IP
SELECT ip, username, count, countryCode FROM ipd_pretty_associations WHERE ip = '%s' 
ORDER BY count DESC;

-- Selecting usernames that have used a wildcarded IP
SELECT ip, username, count, countryCode FROM ipd_pretty_associations WHERE ip LIKE '%s' 
ORDER BY count DESC;

-- Selecting usernames that have used by an IP more than a given # of times 
SELECT ip, username, count, countryCode FROM ipd_pretty_associations WHERE ip LIKE '%s' 
AND count > %d ORDER BY count DESC;

-- Selecting a count of how many IPs the system has seen
SELECT COUNT(*) AS `ips` FROM ipd_ips;

-- Selecting a count of how many usernames the system has seen
SELECT COUNT(*) AS `players` FROM ipd_users;

-- Finding the primary usernames of an IP (i.e. who used the IP most)
SELECT count, username, ip FROM ipd_pretty_associations WHERE ip='%s' ORDER BY count;

-- Find the 10 players who have been booted from the game the most
SELECT username, COUNT(*) AS `dests` FROM ipd_dested_players GROUP BY username 
ORDER BY dests DESC LIMIT 10;

-- Find the IPs used by the most usernames
SELECT * FROM ipd_ip_popularity ORDER BY characters DESC LIMIT 20;

-- Find the sessions for a given username
SELECT * FROM ipd_in_out WHERE username = '%s' ORDER BY `out` DESC;

-- Find the last n sessions for a given username
SELECT * FROM ipd_in_out WHERE username = '%s' ORDER BY `out` DESC LIMIT %d;

-- Find the logout timestamps for a given username.
SELECT * FROM ipd_user_logouts WHERE username = '%s' ORDER BY `when` DESC;

-- Find the login timestamps for a given username.
SELECT * FROM ipd_user_logins WHERE username = '%s' ORDER BY `when` DESC;

-- Call the log_connection procedure to log a new connection
CALL log_connection('%s', '%s');

-- Call the log_disconnect procedure to log someone exiting the game
CALL log_disconnect('%s', '%s', '%s');

-- Find all of the IPs that have been used by two separate usernames
SELECT user.ip, user.count as userUsage, alt.count as altUsage FROM ipd_pretty_associations AS user 
LEFT JOIN ipd_pretty_associations AS alt ON user.ip = alt.ip WHERE user.username = '%s' AND 
alt.username = '%s' ORDER BY altUsage, userUsage DESC;