MySQL: Get the dataset for a given date - the last existing dataset before this if one does not exist

Question

My table structure is as follows:

CREATE TABLE `market_trend_record` (
`date` date NOT NULL,
`symbol` CHAR(40),
`identifier` CHAR(20),
`trend` CHAR(9),
`duration` int,
`daynr` int,
`price_quote` decimal(16,6),
PRIMARY KEY (`date` , `symbol` , `identifier`));

To get the latest entry for each symbol (they are not necessarily the same date due to national holidays), I do this:

SELECT market_trend_record.symbol, market_trend_record.date, market_trend_record.trend,
market_trend_record.duration, market_trend_record.price_quote
    FROM (select symbol, MAX(date)
       AS date FROM market_trend_record GROUP BY symbol) AS latest_record INNER JOIN
       market_trend_record ON market_trend_record.symbol = latest_record.symbol AND
       market_trend_record.date = latest_record.date;

Works so well that I run it through a python script every Friday. Now to compare this week's data with last week's data, I created a second query:

select market_trend_record.symbol, market_trend_record.date, market_trend_record.trend,
market_trend_record.duration, market_trend_record.price_quote FROM (select symbol, MAX(date) -
INTERVAL 7 DAY AS date
    FROM market_trend_record GROUP BY symbol) AS latest_record INNER JOIN market_trend_record
    ON market_trend_record.symbol = latest_record.symbol AND market_trend_record.date =
    latest_record.date;

The idea is to use the last available date and subtract 7 days to get the Friday of the previous week. This also works well...until it doesn't. For some symbols, data for last Friday does not exist, so these data sets are skipped. For example one of the data missing symbols:

| 2022-05-31 | FTSE 100 | FTSE | uptrend | 7 | 44711 | 7607.660156 |
| 2022-06-01 | FTSE 100 | FTSE | uptrend | 8 | 44712 ​​| 7532.950195 |
| 2022-06-06 | FTSE 100 | FTSE | uptrend | 9 | 44717 | 7608.220215 |
| 2022-06-07 | FTSE 100 | FTSE | uptrend | 10 | 44718 | 7598.930176 |
| 2022-06-08 | FTSE 100 | FTSE | uptrend | 11 | 44719 | 7593.000000 |
| 2022-06-09 | FTSE 100 | FTSE | sideways | 1 | 44720 | 7476.209961 |
| 2022-06-10 | FTSE 100 | FTSE | sideways | 2 | 44721 | 7317.520020 |

The last date is 2022-06-10. According to the above query, one week earlier is 2022-06-03, but there is no data set.

I would like to modify the second query in such a way that if the interval date is missing (2022-06-01 in the example above), it will take the last available date. I don't know where to put the "<=" relationship in the above query. If that's not possible with date fields, maybe pass date numbers since they are integers? Any tips would be greatly appreciated!

Answer 1

Possible solutions:

SELECT MTR.symbol, MTR.date, MTR.trend, MTR.duration, MTR.price_quote
FROM (
    SELECT DT.*,
           RANK() OVER (PARTITION BY DT.symbol ORDER BY DT.date Desc) record_number
    FROM market_trend_record DT
    INNER JOIN (
        SELECT symbol,
        MAX(date) - INTERVAL 7 DAY AS date
        FROM market_trend_record
        GROUP BY symbol
    ) AS DT2 ON DT.symbol = DT2.symbol AND DT.date <= DT2.date
) AS MTR
WHERE MTR.record_number = 1;

Works like a charm.