Mysql – Google search console data – find the newly appeared keyword for the month

MySQL

I have a table named sconsole for google searchconsole api query data with the fields specified in the code, I need to find the new keywords (r.query field) appeared each month. I tried using this but it returns zero results. At the moment all the data is saved to the same table. Is there any easy way to do this?

Previously I tried with a very simple query

SELECT DISTINCT s.*
FROM sconsole s
JOIN sconsole t ON (s.site_id = t.site_id AND month(str_to_date(s.month,'%b')) >  month(str_to_date(t.month,'%b')))
where (s.site_id = t.site_id)

It returned an error. the requirement is that the site_id is same and the month is the next month. This way each month should be iterated and returned with the new keywords.

Best Answer

I figured it out after a brief study, here it is,

SELECT * FROM sconsole WHERE sconsole.query IN
                (SELECT  tbl.query FROM
                  (  SELECT  s1.* FROM sconsole s1 WHERE s1.month = '{$params['m1']}' UNION ALL SELECT  s2.* FROM sconsole s2 WHERE s2.month = '{$params['m2']}'
                  ) AS tbl  GROUP BY  tbl.query HAVING COUNT(tbl.query) = 1)

works like a charm.

Related Question