By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE predictions (
`ID` INTEGER,
`_date` VARCHAR(10),
`prediction` FLOAT,
`actual` INTEGER
);
INSERT INTO predictions
(`ID`, `_date`, `prediction`, `actual`)
VALUES
('1', '2020-02-01', '0.81', '1'),
('2', '2020-05-04', '0.22', '0'),
('3', '2020-01-18', '0.84', '1'),
('4', '2020-07-11', '0.92', '1'),
('5', '2020-12-31', '0.44', '0'),
('6', '2020-06-02', '0.71', '1'),
('7', '2020-03-02', '0.11', '0'),
('1000', '2020-11-22', '0.61', '0');
SELECT `ID`,`Label`, SUM(positive) * 1.0/ (SUM(positive)+SUM(negative))
FROM
(SELECT
IF(`_date` <= '2020-11-01',1,2) AS 'ID',
IF(`_date` <= '2020-11-01','Training Data','Test Data') AS 'Label',
IF(`actual` = 1,1,0) as positive ,
IF(`actual` = 0,1,0) as negative
FROM predictions) pred
GROUP BY `ID`,`Label`
| ID | Label | SUM(positive) * 1.0/ (SUM(positive)+SUM(negative)) |
|---|---|---|
| 1 | Training Data | 0.66667 |
| 2 | Test Data | 0.00000 |