seo Question posted in
Some basic information to get you started with this can be found here.

MySQL – How to find the rows with the greatest value changes? – SEO

I am trying to search a table where I have daily ranked keywords (SEO keywords). Therefore I have index on a key_id per keyword, and new position value per each keyword.

I would like to find out how I can select the keywords that have the greatest change in value?

MariaDB Table and data:

CREATE TABLE IF NOT EXISTS `daily_rank` (
  `rankID` int(24) NOT NULL AUTO_INCREMENT,
  `created` timestamp NULL DEFAULT current_timestamp(),
  `key_id` int(100) NOT NULL DEFAULT 0,
  `position` int(12) NOT NULL DEFAULT 0,
  `keyword` varchar(50) NOT NULL DEFAULT '0',
  PRIMARY KEY (`rankID`),
  KEY `created` (`created`),
  KEY `key_id` (`key_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3594 DEFAULT CHARSET=latin1;

INSERT INTO `daily_rank` (`rankID`, `created`, `key_id`, `position`, `keyword`) VALUES
    (3594, '2019-10-09 17:59:07', 53, 4, 'SEO'),
    (3595, '2019-10-09 17:59:07', 100, 3, 'agency'),
    (3596, '2019-10-09 17:59:07', 397, 1, 'bureau marketing'),
    (3597, '2019-10-09 17:59:07', 798, 7, 'marketing agency'),
    (3598, '2019-10-09 17:59:07', 98, 8, 'search engine optimization'),
    (3599, '2019-10-09 17:59:07', 346, 8, 'website optimization'),
    (3600, '2019-10-09 17:59:07', 555, 9, 'agency'),
    (3608, '2019-10-08 18:07:00', 53, 4, 'SEO'),
    (3609, '2019-10-08 18:07:00', 100, 4, 'agency'),
    (3610, '2019-10-08 18:07:00', 397, 3, 'bureau marketing'),
    (3611, '2019-10-08 18:07:00', 798, 1, 'marketing agency'),
    (3612, '2019-10-08 18:07:00', 98, 2, 'search engine optimization'),
    (3613, '2019-10-08 18:07:00', 346, 2, 'website optimization'),
    (3614, '2019-10-08 18:07:00', 555, 2, 'agency'),
    (3615, '2019-10-07 18:07:22', 53, 4, 'SEO'),
    (3616, '2019-10-07 18:07:22', 100, 6, 'agency'),
    (3617, '2019-10-07 18:07:22', 397, 6, 'bureau marketing'),
    (3618, '2019-10-07 18:07:22', 798, 6, 'marketing agency'),
    (3619, '2019-10-07 18:07:22', 98, 4, 'search engine optimization'),
    (3620, '2019-10-07 18:07:22', 346, 6, 'website optimization'),
    (3621, '2019-10-07 18:07:22', 555, 6, 'agency'),
    (3622, '2019-10-07 18:07:22', 53, 5, 'SEO'),
    (3623, '2019-10-07 18:07:22', 100, 4, 'agency'),
    (3624, '2019-10-07 18:07:22', 397, 5, 'bureau marketing'),
    (3625, '2019-10-07 18:07:22', 798, 3, 'marketing agency'),
    (3626, '2019-10-07 18:07:22', 98, 6, 'search engine optimization'),
    (3627, '2019-10-07 18:07:22', 346, 3, 'website optimization'),
    (3628, '2019-10-07 18:07:22', 555, 5, 'agency'),
    (3629, '2019-10-06 18:07:44', 53, 1, 'SEO'),
    (3630, '2019-10-06 18:07:44', 100, 2, 'agency'),
    (3631, '2019-10-06 18:07:44', 397, 2, 'bureau marketing'),
    (3632, '2019-10-06 18:07:44', 798, 1, 'marketing agency'),
    (3633, '2019-10-06 18:07:44', 98, 1, 'search engine optimization'),
    (3634, '2019-10-06 18:07:44', 346, 2, 'website optimization'),
    (3635, '2019-10-06 18:07:44', 555, 2, 'agency'),
    (3636, '2019-10-06 18:07:44', 53, 2, 'SEO'),
    (3637, '2019-10-06 18:07:44', 100, 2, 'agency'),
    (3638, '2019-10-06 18:07:44', 397, 3, 'bureau marketing'),
    (3639, '2019-10-06 18:07:44', 798, 2, 'marketing agency'),
    (3640, '2019-10-06 18:07:44', 98, 2, 'search engine optimization'),
    (3641, '2019-10-06 18:07:44', 346, 1, 'website optimization'),
    (3642, '2019-10-06 18:07:44', 555, 1, 'agency'),
    (3643, '2019-10-06 18:07:44', 53, 1, 'SEO'),
    (3644, '2019-10-06 18:07:44', 100, 2, 'agency'),
    (3645, '2019-10-06 18:07:44', 397, 1, 'bureau marketing'),
    (3646, '2019-10-06 18:07:44', 798, 3, 'marketing agency'),
    (3647, '2019-10-06 18:07:44', 98, 2, 'search engine optimization'),
    (3648, '2019-10-06 18:07:44', 346, 1, 'website optimization'),
    (3649, '2019-10-06 18:07:44', 555, 3, 'agency'),
    (3650, '2019-10-06 18:07:44', 53, 3, 'SEO'),
    (3651, '2019-10-06 18:07:44', 100, 1, 'agency'),
    (3652, '2019-10-06 18:07:44', 397, 2, 'bureau marketing'),
    (3653, '2019-10-06 18:07:44', 798, 3, 'marketing agency'),
    (3654, '2019-10-06 18:07:44', 98, 1, 'search engine optimization'),
    (3655, '2019-10-06 18:07:44', 346, 2, 'website optimization'),
    (3656, '2019-10-06 18:07:44', 555, 1, 'agency');

How do I query so I can get the latest position for the keywords, and the change from a given date, and order the result to show the keywords with the greatest change?

I imagine a table like this: 

[Keyword] - [Todays Position] - [Position Change from yesterday]

where it is ordered by the biggest change descending

UPDATE:
When calculating max-min the todays position is within this calculation, and will skew the result somewhat.

And when viewing todays position, I would like to see the keywords that have had the biggest change in position since compared date.

Tags:

2

Answers


  1. 0 
    SELECT keyword, MAX(position) max_position,MIN(position) min_position FROM daily_rank GROUP BY keyword;
+----------------------------+--------------+--------------+
| keyword                    | max_position | min_position |
+----------------------------+--------------+--------------+
| agency                     |            9 |            1 |
| bureau marketing           |            6 |            1 |
| marketing agency           |            7 |            1 |
| search engine optimization |            8 |            1 |
| SEO                        |            5 |            1 |
| website optimization       |            8 |            1 |
+----------------------------+--------------+--------------+
    Login or Signup to reply.
  2. 0

    I think this is what you want.

    SELECT a.keyword, a.position as today_position, b.biggest_position_change_since_yesterday
FROM daily_rank a
JOIN 
(SELECT keyword, MAX(position) - MIN(position) AS biggest_position_change_since_yesterday 
FROM daily_rank 
WHERE cast(created as date) >= ADDDATE(curdate(),-1) 
GROUP BY keyword) b
ON b.keyword = a.keyword
AND cast(created as date) = curdate()
ORDER by biggest_position_change_since_yesterday desc;

    keyword                     today_position  biggest_position_change_since_yesterday
agency                      9               7
agency                      3               7
website optimization        8               6
marketing agency            7               6
search engine optimization  8               6
bureau marketing            1               2
SEO                         4               0

    Test Case:

    DB<>FIDDLE

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search