How do I write a query that takes a count of records of one date plus the previous 4 days for every date in a table? – Mysql

We will start by getting the counts per symbol per day with a simple GROUPed query. If you want the results for the week commencing 26th April 2021 you need to remember to include the four days prior to the start of the desired date range –

SELECT `Symbol`, DATE(`timestamp`) `date`, COUNT(*) `count`
FROM `test`.`rawdata`
WHERE `timestamp` BETWEEN '2021-04-22 00:00:00' AND '2021-05-02 23:59:59'
GROUP BY `Symbol`, `date`;

Now we can use SUM as a window function with a 5 day frame to get your counts –

SELECT *, SUM(`count`) OVER (
    PARTITION BY `Symbol`
    ORDER BY `date` ASC
    RANGE INTERVAL 4 DAY PRECEDING
) `5_day_count`
FROM (
    SELECT `Symbol`, DATE(`timestamp`) `date`, COUNT(*) `count`
    FROM `test`.`rawdata`
    WHERE `timestamp` BETWEEN '2021-04-22 00:00:00' AND '2021-05-02 23:59:59'
    GROUP BY `Symbol`, `date`
) tbl;

The above query will return the leading four rows which we can remove by adding another level of nesting (we cannot use a HAVING clause as the filter would be applied before the SELECT list is evaluated) –

SELECT *
FROM (
    SELECT *, SUM(`count`) OVER (
        PARTITION BY `Symbol`
        ORDER BY `date` ASC
        RANGE INTERVAL 4 DAY PRECEDING
    ) `5_day_count`
    FROM (
        SELECT `Symbol`, DATE(`timestamp`) `date`, COUNT(*) `count`
        FROM `test`.`rawdata`
        WHERE `timestamp` BETWEEN '2021-04-22 00:00:00' AND '2021-05-02 23:59:59'
        GROUP BY `Symbol`, `date`
    ) tbl
) t2
WHERE `date` BETWEEN '2021-04-26' AND '2021-05-02';

As an alternative approach, you could use a recursive cte to build a list of ranges to join and group by –

WITH RECURSIVE `cte` (`date`, `start`, `end`) AS (
    SELECT
        CAST('2021-04-26' AS DATE),
        CAST('2021-04-22 00:00:00' AS DATETIME),
        CAST('2021-04-26 23:59:59' AS DATETIME)
    UNION ALL
    SELECT
        `date` + INTERVAL 1 DAY,
        `start` + INTERVAL 1 DAY,
        `end` + INTERVAL 1 DAY
    FROM `cte`
    WHERE `date` < '2021-05-02'
)
SELECT `rawdata`.`symbol`, `cte`.`date`, COUNT(*) `count`
FROM `cte`
JOIN `test`.`rawdata`
    ON `rawdata`.`timestamp` BETWEEN `cte`.`start` AND `cte`.`end`
GROUP BY `rawdata`.`symbol`, `cte`.`date`;