I am using 10.1.39-MariaDB - mariadb.org binary and I have the following table:
| id | date | ticker | close | createdAt | updatedAt | CompanyId |
|-------|---------------------|--------|-----------|---------------------|---------------------|-----------|
| 39869 | 2019-09-18 00:00:00 | AAPL | 221.96160 | 2019-09-18 19:25:30 | 2019-09-18 19:25:30 | 238 |
| 39870 | 2019-09-17 00:00:00 | AAPL | 220.70000 | 2019-09-18 19:25:30 | 2019-09-18 19:25:30 | 238 |
| 39871 | 2019-09-16 00:00:00 | AAPL | 219.90000 | 2019-09-18 19:25:30 | 2019-09-18 19:25:30 | 238 |
| 39872 | 2019-09-13 00:00:00 | AAPL | 218.75000 | 2019-09-18 19:25:30 | 2019-09-18 19:25:30 | 238 |
| 39873 | 2019-09-12 00:00:00 | AAPL | 223.09000 | 2019-09-18 19:25:30 | 2019-09-18 19:25:30 | 238 |
Furthermore, I have the following query to get for the last day the top 5 daily_returns:
SELECT *
FROM (SELECT prices.*,
CAST((`close` - @old_close) / @old_close AS DECIMAL(20, 10)) AS daily_return,
@old_close := `close`
FROM prices,
(SELECT @old_close := 0 AS) AS t
ORDER BY ticker,
`date` ASC) AS tt
WHERE DATE >= DATE(NOW()) - INTERVAL 1 DAY
ORDER BY `date` DESC,
daily_return DESC
LIMIT 5
However, on Monday I only get empty results back as no prices have been inputed to the db as markets stand still.
How to overcome this weekend problem and take the last price that was posted in the db?
1 of the methods is using CASE statements -
SELECT *
FROM (SELECT prices.*,
CAST((`close` - @old_close) / @old_close AS DECIMAL(20, 10)) AS daily_return,
@old_close := `close`
FROM prices,
(SELECT @old_close := 0 AS) AS t
ORDER BY ticker,
`date` ASC) AS tt
WHERE DATE >= CASE WHEN DAYNAME(NOW()) = 'Monday' THEN DATE(NOW()) - INTERVAL 3 DAY -- OR 2 DAY, DEPENDS UPON YOUR REQUIREMENT
ELSE DATE(NOW()) - INTERVAL 1 DAY END
ORDER BY `date` DESC,
daily_return DESC
LIMIT 5