Increment Row Index And Union With Other Tables On Dynamic Dates

Question

Question:

I looked at various other examples to increment over rows, but all resulted in the same wrong output. The problem which I encountered was that my code did not successfully increment over rows to build a correct index per new row in the result-set per episode (highlighted in red below).

My first try was:

SET @ep_1 = "Peaky Blinders";
SET @curRow_1 = 0;

SELECT
    DATE_FORMAT(created_at, "%Y%m%d") AS year_month_day,
    @curRow_1 := @curRow_1 + 1 AS row_number,
    @ep_1 AS episode_title,
    COUNT(id) AS episode_plays
FROM netflix.episode_plays
WHERE
    episode_id = "xyz"
    AND created_at >= "2019-07-01" AND created_at <= "2019-07-07"
GROUP BY 1

Other than the rows not incrementing correctly; I also got the following error when I tried setting some variables in the beginning of my code:

Error running query: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

(Note: I have no affiliation with Netflix, I just used Netflix dummy data to answer my question)

I made the necessary updates. Is it possible to delete your comment? — d_-, Oct 07 '19 at 20:44
See [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) ... As you are doing some wierd things in your qeuries like using unneeded `SET`, well @curRow_1 is indeed needed for the row_number... Also `GROUP BY 1` syntax is deprecated and removed since ANSI/ISO SQL 99 standard ... SQL 92 BNF ` ::= | # <- here it is ` vs SQL 1999 BNF ` ::= # <- missing` — Raymond Nijland, Oct 08 '19 at 08:44

d_- · Answer 1 · 2019-10-07T18:18:22.193

I broke down my question in various sections and got to the final answer below.

The most important part was to add the initial result-sets into a subqueries, and thereafter select the data from tables x1,x2, etc.
The second part of the question was, how to combine multiple datasets together (in my case: how do one not only do it for one specific netflix episode, but multiple episodes)? I settled on the UNION ALL - clause.
In the first iteration I tried hard-coding the dates, and thereafter found the INTERVAL-function very helpful.
Finally, the unicode-error I fixed by adding COLLATE utf8_unicode_ci after setting my variables.

If you find mistakes in my code or have any other suggestions, please feel free to suggest them.

-- SET DATA

-- variables for table x1
SET @ep_1 = "Peaky Blinders" COLLATE utf8_unicode_ci;
SET @id_1 = (SELECT id FROM netflix.episodes WHERE episode_title = @ep_1);
SET @date_1 = (SELECT created_at FROM netflix.episodes WHERE episode_title = @ep_1);
SET @curRow_1 = 0;

-- variables for table x2
SET @ep_2 = "Brooklyn Nine-Nine" COLLATE utf8_unicode_ci;
SET @id_2 = (SELECT id FROM netflix.episodes WHERE episode_title = @ep_2);
SET @date_2 = (SELECT created_at FROM netflix.episodes WHERE episode_title = @ep_2);
SET @curRow_2 = 0;


-- QUERY DATA

SELECT
    x1.year_month_day,
    @curRow_1 := @curRow_1 + 1 AS row_number,
    x1.episode_title,
    x1.episode_plays
FROM (
    SELECT
        DATE_FORMAT(created_at, "%Y%m%d") AS year_month_day,
        @ep_1 AS episode_title,
        COUNT(id) AS episode_plays
    FROM netflix.episode_plays
    WHERE
        episode_id = @id_1
        AND created_at >= @date_1 AND created_at <= DATE_ADD(@date_1 , INTERVAL 7 DAY)
    GROUP BY 1) x1

UNION ALL

SELECT
    x2.year_month_day,
    @curRow_2 := @curRow_2 + 1 AS row_number,
    x2.episode_title,
    x2.episode_plays
FROM (
    SELECT
        DATE_FORMAT(created_at, "%Y%m%d") AS year_month_day,
        @ep_2 AS episode_title,
        COUNT(id) AS episode_plays
    FROM netflix.episode_plays
    WHERE
        episode_id = @id_2
        AND created_at >= @date_2 AND created_at <= DATE_ADD(@date_2 , INTERVAL 7 DAY)
    GROUP BY 1) x2

There needs to be explicit `ORDER BY`, as MySQL deprecated in the more modern MySQL versions (5.7+) implicit sorting with `GROUP BY`... MySQL 8 does not auto sort `GROUP BY` anymore. — Raymond Nijland, Oct 08 '19 at 08:49

Increment Row Index And Union With Other Tables On Dynamic Dates

1 Answers1