James James - 5 months ago 9
SQL Question

MySQL daily score change with global rank

I am building a system which records a users total score ("XP") at the end of each day so players can track their progress over time.
At the moment I'm trying to write a query that can return a leaderboard based on the XP change from the previous X days, with a rank. I don't want to build a dedicated leaderboard table as interval X can change.

EDIT

I've added a SQL Fiddle here:

http://sqlfiddle.com/#!9/a7c1c/9 - Here's a working version without rank
http://sqlfiddle.com/#!9/a7c1c/11 - Here's the closest I can get with rank incorporated (this isn't working, but hopefully clear what I'm attempting)

Problems:


  • I have a double nested subquery . I don't like this, but couldn't find a way to count the
    HAVING BY
    clause in the
    countsub
    subquery without it;

  • As a result of the double nested subquery, the
    xp_change
    column is unavailable in the
    countsub
    query, so I can't actually compare changes



It seems to me like I've either written the query incorrectly, or I'm missing something. I've been trying to figure out a way to remove the
COUNT
subquery, but haven't had any luck so far. If anyone can point me in the right direction, that would be great!

/EDIT

Here are my schemas:

accounts

CREATE TABLE `accounts` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`display_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`slug` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`scanned_at` datetime DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `accounts_display_name_index` (`display_name`),
KEY `accounts_last_tracked_index` (`scanned_at`),
KEY `accounts_slug_index` (`slug`)
)


account_instances

The game in question has multiple game types, each with a different leaderboard, and so an account can have multiple "instances":

CREATE TABLE `account_instances` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`account_id` int(10) unsigned NOT NULL,
`game_type_id` int(10) unsigned NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `account_instances_account_id_game_type_id_unique` (`account_id`,`game_type_id`),
KEY `account_instances_game_type_id_foreign` (`game_type_id`),
CONSTRAINT `account_instances_game_type_id_foreign` FOREIGN KEY (`game_type_id`) REFERENCES `game_types` (`id`)
)


stats

These are the stats which a user can have an XP score in:

CREATE TABLE `stats` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`display_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `stats_name_unique` (`name`),
UNIQUE KEY `stats_display_name_unique` (`display_name`),
KEY `stats_name_index` (`name`)
)


account_instance_stats

Associates an account instance and a stat with a score (
xp
) for a given day:

CREATE TABLE `account_instance_stats` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`account_instance_id` int(10) unsigned NOT NULL,
`stat_id` int(10) unsigned NOT NULL,
`xp` bigint(20) DEFAULT NULL,
`date` date NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `account_instance_stats_account_instance_id_stat_id_date_unique` (`account_instance_id`,`stat_id`,`date`),
KEY `account_instance_stats_stat_id_foreign` (`stat_id`),
KEY `account_instance_stats_xp_index` (`xp`),
KEY `account_instance_stats_date_index` (`date`),
CONSTRAINT `account_instance_stats_account_instance_id_foreign` FOREIGN KEY (`account_instance_id`) REFERENCES `account_instances` (`id`),
CONSTRAINT `account_instance_stats_stat_id_foreign` FOREIGN KEY (`stat_id`) REFERENCES `stats` (`id`)
)


Here is the query I have written so far. It won't run, but hopefully you can see what I'm trying here:

SELECT
a.*,
SUM(ais.xp - ais2.xp) AS xp_change,
(
select count(*) FROM
(
SELECT COUNT(sub.id)
FROM account_instance_stats AS sub
LEFT JOIN account_instance_stats sub2
ON sub.account_instance_id = sub2.account_instance_id
AND sub.stat_id = sub2.stat_id
AND sub2.date = date_sub(sub.date, INTERVAL 1 day)
JOIN account_instances AS ai ON sub.account_instance_id = ai.id
WHERE ai.game_type_id = 1
AND sub.date = curdate()
AND sub.stat_id = 1
GROUP BY sub.id
HAVING SUM(sub.xp - sub2.xp) > xp_change
) AS countsub
) AS rank

FROM account_instance_stats AS ais
LEFT JOIN account_instance_stats ais2
ON ais.account_instance_id = ais2.account_instance_id
AND ais.stat_id = ais2.stat_id
AND ais2.date = date_sub(ais.date, INTERVAL 1 day)
JOIN account_instances AS ai ON ais.account_instance_id = ai.id
JOIN accounts AS a ON ai.account_id = a.id
WHERE ai.game_type_id = 1
AND ais.date = curdate()
AND ais.stat_id = 1
GROUP BY a.id
ORDER BY rank DESC
LIMIT 10;


The bulk of the parent query is a
left join
on
account_instance_stats
to itself based on the interval so I can compare the
xp
column for the two dates. This bit works as expected. The part I'm struggling with is the
rank
subquery. This pretty much performs the same query but calculates the rank by counting how many accounts have a higher
xp_change
.

Thanks!

Answer

Here is a solution that uses SQL variables

Which shows this code.

SELECT *, @rank := COALESCE(@rank + 1, 1) AS ranking
FROM (
  SELECT a.ID AS ID, a.display_name AS display_name
   , SUM(ais.xp - ais2.xp) AS xp_change
  FROM accounts AS a 
  JOIN account_instances AS ai 
    ON ai.account_id = a.id
  JOIN account_instance_stats AS ais 
    ON ais.account_instance_id = ai.id 
  LEFT JOIN account_instance_stats ais2 
    ON ais.account_instance_id = ais2.account_instance_id 
       AND ais.stat_id = ais2.stat_id 
       AND ais2.date = date_sub(ais.date, INTERVAL 1 day) 
  WHERE ai.game_type_id = 1
       AND ais.date = '2016-06-02'
       AND ais.stat_id = 1
  GROUP BY a.id
  ORDER BY xp_change DESC) AS t1

I rearranged your table JOIN order to (IMO) a more logical order.

Given this solution: if you want to a single account (assume ID=2), I would change the code to:

SELECT * 
FROM (
  SELECT *, @rank := COALESCE(@rank + 1, 1) AS ranking
  FROM (
    SELECT a.ID AS ID, a.display_name AS display_name
     , SUM(ais.xp - ais2.xp) AS xp_change
    FROM accounts AS a 
    JOIN account_instances AS ai 
      ON ai.account_id = a.id
    JOIN account_instance_stats AS ais 
      ON ais.account_instance_id = ai.id 
    LEFT JOIN account_instance_stats ais2 
      ON ais.account_instance_id = ais2.account_instance_id 
         AND ais.stat_id = ais2.stat_id 
         AND ais2.date = date_sub(ais.date, INTERVAL 1 day) 
    WHERE ai.game_type_id = 1
         AND ais.date = '2016-06-02'
         AND ais.stat_id = 1
    GROUP BY a.id
    ORDER BY xp_change DESC) AS t1
) AS foo
WHERE id = 2;
Comments