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)

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:

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

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.

CodePudding user response：

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.

CodePudding user response：

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.