CODEWITHSUNDEEP
mysqlsql
Pastor Bones
Pastor Bones

Reputation: 7351

Advanced SQL statement to detect non changing values - MySQL

I have two tables described below. What I need is a single query that will tell me the players whose score hasn't changed in a given number of days.

CREATE TABLE players (
  pid INT(50),
  name VARCHAR(255),
  updatedAt DATETIME
);

CREATE TABLE pl_scores (
  pid INT(50),
  score INT(255),
  updatedAt DATETIME
);

The players table keeps a master list of all players along with other non-changing data not shown here for the sake of brevity. The pl_scores table keeps a running history of score changes in order to track growth and other values that might change. A new record is inserted into pl_scores for each player every 6 hours.

What I would like to get is the pid of the players that haven't had a change of score in a certain number of days, but I am unsure of how to group that in order to get the correct values.

Example dataset

(only shows last score of each day which is only one needing to be compared really)

+------+------+-------+------+---------+---------------------+
| pid  | aid  | score | rank | cityCnt | updatedAt           |
+------+------+-------+------+---------+---------------------+
| 1660 |    0 |   801 | 2111 |       1 | 2012-06-20 22:14:11 |
| 1660 |    0 |   801 | 2250 |       1 | 2012-06-21 22:15:45 |
| 1660 |    0 |   801 | 2387 |       1 | 2012-06-22 22:17:06 |
| 1660 |    0 |   801 | 2547 |       1 | 2012-06-23 22:17:09 |
| 1660 |    0 |   801 | 2702 |       1 | 2012-06-24 22:19:50 |
| 1660 |    0 |   801 | 2836 |       1 | 2012-06-25 22:21:07 |
| 1660 |    0 |   801 | 2956 |       1 | 2012-06-26 21:42:44 |
+------+------+-------+------+---------+---------------------+

EDIT

The answer found below worked perfectly, but now I'd like to take it a step further and limit the results by a hardcoded value found in a 3rd table. Here is the working SQL statement

SELECT a.pid, c.aid, b.name AS pName, c.name AS aName, a.score FROM pl_scores AS a 
JOIN players AS b ON a.pid = b.pid 
JOIN alliances AS c ON b.aid = c.aid 
WHERE a.updatedAt >= CURRENT_DATE() - INTERVAL 3 DAY GROUP BY a.pid HAVING MIN(a.score) = MAX(a.score);

Each player has multiple cities throughout the world. I'd like to limit the results by players with cities found on a given continent. For example, I want to find every player that hasn't changed their score in the last 3 days on continent 34. The cities table looks like this:

CREATE TABLE cities (
  cid INT(50),
  pid INT(50),
  name VARCHAR(255),
  cont INT(10),
  updatedAt DATETIME
);

Upvotes: 2

Views: 275

Answers (5)

Cuhl
Cuhl

Reputation: 11

SELECT a.pid, c.aid, b.name AS pName, c.name AS aName, a.score FROM pl_scores AS a 
JOIN players AS b ON a.pid = b.pid 
JOIN alliances AS c ON b.aid = c.aid 
JOIN cities AS d ON a.pid = d.pid
WHERE 
a.updatedAt >= CURRENT_DATE() - INTERVAL 3 DAY 
AND d.cont = 34
GROUP BY a.pid HAVING MIN(a.score) = MAX(a.score);

Upvotes: 1

Andriy M
Andriy M

Reputation: 77687

You could get pids of players with their scores unchanged like this:

SELECT pid
FROM pl_scores
WHERE updatedAt >= CURRENT_DATE() - INTERVAL n DAY
GROUP BY pid
HAVING MIN(score) = MAX(score)

Now you can use those pids to get full (or just more) information about the corresponding players, i.e. like this:

SELECT *  /* or you could specify the necessary columns here */
FROM players
WHERE pid IN (  
  SELECT pid
  FROM pl_scores
  WHERE updatedAt >= CURRENT_DATE() - INTERVAL n DAY
  GROUP BY pid
  HAVING MIN(score) = MAX(score)
)

Upvotes: 1

Palladium
Palladium

Reputation: 3763

Assuming your updatedAt contains when an entry was made and not the update time, using 3 days as the time to check:

SELECT pid FROM
    (SELECT * FROM pl_score GROUP BY pid HAVING updatedAt > DATE_SUB(NOW(), INTERVAL 3 DAY) AND COUNT(DISTINCT(score)) = 1) AS y
JOIN pl_score ON y.pid = pl_score.pid;

Read this page to learn more about this sort of problem.

I can't guarantee that this'll work, but give it a try.

Upvotes: -1

raina77ow
raina77ow

Reputation: 106375

I suppose it can be done with something like this:

      SELECT plf.pid, 
             COALESCE(plf.score, 0) AS former_score, 
             COALESCE(pll.score, 0) AS latter_score
        FROM pl_scores AS plf
  RIGHT JOIN (
             SELECT pid, score FROM pl_scores 
             WHERE DATE(updatedAt) = DATE(NOW())
             ) as pll
          ON plf.pid = pll.pid
       WHERE DATE(updatedAt) = DATE(DATE_SUB(NOW(), INTERVAL 3 DAY)) 
      HAVING former_score = latter_score

Upvotes: 2

Joshua Martell
Joshua Martell

Reputation: 7212

I think something like this will work:

SELECT pid, MIN(updatedAt) AS last FROM pl_scores GROUP BY pid, score HAVING last > '2012-06-24'

Upvotes: 0

Related Questions