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)
1Dancing Queen2020-01-191
3Dancing Queen2020-01-121
4Shape Of You2020-01-122
5Blinding Light2020-01-051
7Blinding Light2019-12-291
8Shape Of You2019-12-292
9Blinding Light2019-12-221

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:

Blinding Light2019-12-291
Shape Of You2019-12-292

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

song_namedatechart_positionweeks_in_positionmovement (optional – same / new / up / down)
Blinding Light2019-12-2912same
Shape Of You2019-12-2921new

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.


Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

  SELECT id, song_name, 1 as weeks_in_position, chart_position, dt
  FROM charts WHERE dt='2019-12-29' 
  SELECT, 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 <>
  AND DATEDIFF(cte.dt, charts.dt) <= 7
  AND DATEDIFF(cte.dt, charts.dt) > 0

    SELECT cte.song_name, cte.dt, 
    MAX(cte.weeks_in_position) OVER(PARTITION BY song_name) weeks_in_position,
        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
WHERE dt='2019-12-29' 

You can check the results in this db fiddle.

Method 2

You can add new column to your table for an easy querying, like previous_chart_position type integer (easy comparison for same / new / up / down) and chart_position_updatedat datetime to calculate difference in time for weeks_in_position.

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Notify of

Inline Feedbacks
View all comments
Would love your thoughts, please comment.x