PHP / MYSQL – Counting How Many Consecutive Previous Weeks Results Have Same Value As Current Week (Music Charts)

I’m trying (and failing) miserably to come up with a mysql query to calculate how many times a song has previously been in the same chart position on previous consecutive weeks. So for example, given the data set below, how would I write a query (based on supplying a date) which returns:

• song name
• date
• chart position
• number of previous weeks it has been at the same position (extra brownie points if answer is 0 that it says if it’s risen or dropped in the chart)

id	song_name	date	chart_position
1	Dancing Queen	2020-01-19	1
2	Wannabe	2020-01-19	2
3	Dancing Queen	2020-01-12	1
4	Shape Of You	2020-01-12	2
5	Blinding Light	2020-01-05	1
6	Wannabe	2020-01-05	2
7	Blinding Light	2019-12-29	1
8	Shape Of You	2019-12-29	2
9	Blinding Light	2019-12-22	1
10	Wannabe	2019-12-22	2

So given a simple select:

SELECT song_name, date, chart_position FROM table WHERE date = '2019-12-29' ORDER BY chart_position ASC

We should get the following result:

song_name	date	chart_position
Blinding Light	2019-12-29	1
Shape Of You	2019-12-29	2

However what is need to add the extra info to make it:

song_name	date	chart_position	weeks_in_position	movement (optional – same / new / up / down)
Blinding Light	2019-12-29	1	2	same
Shape Of You	2019-12-29	2	1	new

Any help greatly appreciated as I’ve spent the past 6 hours trying to work things out myself with a lot of searches online and not been able to work it out! Thank you for your time.

Answer

Most likely some optimisations can still be done, but the following will give you all the output you’ve requested. The first part (the CTE) basically exists to calculate how many consecutive weeks a song was at the song position. The second part serves to calculate the position in comparison to the previous week by performing a join to the table to the previous week.

WITH RECURSIVE cte AS (
  SELECT id, song_name, 1 as weeks_in_position, chart_position, dt
  FROM charts WHERE dt='2019-12-29' 
  UNION ALL
  SELECT charts.id, charts.song_name, cte.weeks_in_position+1, charts.chart_position, charts.dt
  FROM cte 
  INNER JOIN charts ON charts.song_name = cte.song_name
  AND charts.chart_position = cte.chart_position
  AND cte.id <> charts.id
  AND DATEDIFF(cte.dt, charts.dt) <= 7
  AND DATEDIFF(cte.dt, charts.dt) > 0
)

SELECT * FROM (
    SELECT cte.song_name, cte.dt, 
    MAX(cte.weeks_in_position) OVER(PARTITION BY song_name) weeks_in_position,
    CASE 
        WHEN charts.dt IS NULL THEN 'new'
        WHEN cte.chart_position > charts.chart_position THEN 'up'
        WHEN cte.chart_position < charts.chart_position THEN 'down'    
        ELSE 'same'
    END AS movement
    FROM cte 
    LEFT JOIN charts 
    ON cte.song_name = charts.song_name
    AND DATE_ADD(charts.dt, INTERVAL 7 DAY)=cte.dt
) AS DATA
WHERE dt='2019-12-29' 

You can check the results in this db fiddle.