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
- 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)
|4||Shape Of You||2020-01-12||2|
|8||Shape Of You||2019-12-29||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:
|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)|
|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.
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.
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.
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.