Home > Enterprise >  PHP / MYSQL - Counting How Many Consecutive Previous Weeks Results Have Same Value As Current Week (
PHP / MYSQL - Counting How Many Consecutive Previous Weeks Results Have Same Value As Current Week (

Time:10-12

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.

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.

  • Related