MariaDB 10.9

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


10.9.2-MariaDB-1:10.9.2+maria~deb11


CREATE TABLE `end_day_quotes_AVG` (
  `id` int(11) NOT NULL,
  `quote_date` date NOT NULL,
  `bid` decimal(15,5) NOT NULL,
  `stock_id` int(11) DEFAULT NULL,
  `etf_id` int(11) DEFAULT NULL,
  `crypto_id` int(11) DEFAULT NULL,
  `certificate_id` int(11) DEFAULT NULL,
  `currency_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `end_day_quotes_AVG` (`id`, `quote_date`, `bid`, `stock_id`, `etf_id`, `crypto_id`, `certificate_id`, `currency_id`) VALUES
(10537515, '2023-01-02', '16.48286', 40581, NULL, NULL, NULL, 2),
(10537514, '2023-01-02', '3.66786', 40569, NULL, NULL, NULL, 2),
(10537513, '2023-01-02', '9.38013', 40400, NULL, NULL, NULL, 2),
(10537512, '2023-01-02', '8.54444', 40396, NULL, NULL, NULL, 2),
(10537511, '2023-01-01', '15.48286', 40581, NULL, NULL, NULL, 2),
(10537510, '2023-01-01', '2.66786', 40569, NULL, NULL, NULL, 2),
(10537509, '2023-01-01', '8.38013', 40400, NULL, NULL, NULL, 2),
(10537508, '2023-01-01', '7.54444', 40396, NULL, NULL, NULL, 2);




ALTER TABLE `end_day_quotes_AVG`
  ADD PRIMARY KEY (`id`),
  ADD INDEX currency_stock_date (currency_id,stock_id,quote_date);

ALTER TABLE `end_day_quotes_AVG`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10570526;
show create table end_day_quotes_AVG;

Records: 8  Duplicates: 0  Warnings: 0



Records: 0  Duplicates: 0  Warnings: 0



Records: 8  Duplicates: 0  Warnings: 0




Table
Create Table


end_day_quotes_AVG
CREATE TABLE `end_day_quotes_AVG` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `quote_date` date NOT NULL,
  `bid` decimal(15,5) NOT NULL,
  `stock_id` int(11) DEFAULT NULL,
  `etf_id` int(11) DEFAULT NULL,
  `crypto_id` int(11) DEFAULT NULL,
  `certificate_id` int(11) DEFAULT NULL,
  `currency_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `currency_stock_date` (`currency_id`,`stock_id`,`quote_date`)
) ENGINE=InnoDB AUTO_INCREMENT=10570526 DEFAULT CHARSET=utf8mb4


select * from end_day_quotes_AVG


id
quote_date
bid
stock_id
etf_id
crypto_id
certificate_id
currency_id


10537508
2023-01-01
7.54444
40396
null
null
null
2

10537509
2023-01-01
8.38013
40400
null
null
null
2

10537510
2023-01-01
2.66786
40569
null
null
null
2

10537511
2023-01-01
15.48286
40581
null
null
null
2

10537512
2023-01-02
8.54444
40396
null
null
null
2

10537513
2023-01-02
9.38013
40400
null
null
null
2

10537514
2023-01-02
3.66786
40569
null
null
null
2

10537515
2023-01-02
16.48286
40581
null
null
null
2


with stock_ids(stock_id) as (
    # mariadb syntax; in mysql, you need: values row(2),row(23),...
    values (2),(23),(19),(41),(40),(26),(9),(43),
           (22),(44),(28),(32),(30),(34),(20),(10),
           (13),(17),(27),(35),(8),(29),(39),(16),
           (33),(5),(36589),(25),(18),(6),(38),(37),
           (3),(45),(7),(21),(46),(15),(4),(24),
           (31),(36),(38423),(40313),(22561),(36787),(35770),(36600),
           (35766),(42),(22567),(40581),(40569),(29528),(22896),(24760),
           (40369),(40396),(40400),(40374),(36799),(1),(27863),(29659),
           (40367),(27821),(24912),(36654),(21125),(22569),(22201),(23133),
           (40373),(36697),(36718),(26340),(36653),(47),(34019),(36847),
           (36694)
),
last_dates as (
    select t.currency_id, t.stock_id, max(t.quote_date) as quote_date
    from stock_ids
    join end_day_quotes_AVG t on
        t.currency_id=2 and
        t.stock_id=stock_ids.stock_id and
        t.quote_date <= '2023-01-31'
    group by t.currency_id,t.stock_id
),
next_to_last_dates as (
    select t.currency_id, t.stock_id, max(t.quote_date) as quote_date
    from last_dates l
    join end_day_quotes_AVG t on
        t.currency_id=l.currency_id and
        t.stock_id=l.stock_id and
        t.quote_date < l.quote_date
    group by t.currency_id,t.stock_id
)
select 'last' as 'when', currency_id, stock_id, quote_date, bid
from last_dates
join end_day_quotes_AVG using (currency_id, stock_id, quote_date)
union all
select 'next-to-last', currency_id, stock_id, quote_date, bid
from next_to_last_dates
join end_day_quotes_AVG using (currency_id, stock_id, quote_date)


when
currency_id
stock_id
quote_date
bid


last
2
40396
2023-01-02
8.54444

last
2
40400
2023-01-02
9.38013

last
2
40569
2023-01-02
3.66786

last
2
40581
2023-01-02
16.48286

next-to-last
2
40396
2023-01-01
7.54444

next-to-last
2
40400
2023-01-01
8.38013

next-to-last
2
40569
2023-01-01
2.66786

next-to-last
2
40581
2023-01-01
15.48286

id	quote_date	bid	stock_id	etf_id	crypto_id	certificate_id	currency_id
10537508	2023-01-01	7.54444	40396	null	null	null	2
10537509	2023-01-01	8.38013	40400	null	null	null	2
10537510	2023-01-01	2.66786	40569	null	null	null	2
10537511	2023-01-01	15.48286	40581	null	null	null	2
10537512	2023-01-02	8.54444	40396	null	null	null	2
10537513	2023-01-02	9.38013	40400	null	null	null	2
10537514	2023-01-02	3.66786	40569	null	null	null	2
10537515	2023-01-02	16.48286	40581	null	null	null	2

when	currency_id	stock_id	quote_date	bid
last	2	40396	2023-01-02	8.54444
last	2	40400	2023-01-02	9.38013
last	2	40569	2023-01-02	3.66786
last	2	40581	2023-01-02	16.48286
next-to-last	2	40396	2023-01-01	7.54444
next-to-last	2	40400	2023-01-01	8.38013
next-to-last	2	40569	2023-01-01	2.66786
next-to-last	2	40581	2023-01-01	15.48286