By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE edits (
edit_id INT,
edit_page_id INT,
edit_time INT,
stat INT
);
INSERT INTO edits VALUES
(1, 10, 20210502, 90),
(2, 10, 20210503, 91),
(3, 20, 20210504, 91),
(4, 30, 20210504, 90),
(5, 30, 20210505, 92),
(6, 40, 20210505, 90),
(7, 50, 20210503, 90);
SELECT * FROM edits;
CREATE TABLE pages (
page_id INT,
cat_id INT
);
INSERT INTO pages VALUES
(10, 100),
(20, 100),
(30, 100),
(40, 100),
(50, 200);
SELECT * FROM pages;
| edit_id | edit_page_id | edit_time | stat |
|---|---|---|---|
| 1 | 10 | 20210502 | 90 |
| 2 | 10 | 20210503 | 91 |
| 3 | 20 | 20210504 | 91 |
| 4 | 30 | 20210504 | 90 |
| 5 | 30 | 20210505 | 92 |
| 6 | 40 | 20210505 | 90 |
| 7 | 50 | 20210503 | 90 |
| page_id | cat_id |
|---|---|
| 10 | 100 |
| 20 | 100 |
| 30 | 100 |
| 40 | 100 |
| 50 | 200 |
SELECT edit_page_id, MAX(edit_time) edit_time
FROM edits
GROUP BY edit_page_id
| edit_page_id | edit_time |
|---|---|
| 10 | 20210503 |
| 20 | 20210504 |
| 30 | 20210505 |
| 40 | 20210505 |
| 50 | 20210503 |
SELECT *
FROM pages
JOIN edits ON pages.page_id = edits.edit_page_id
| page_id | cat_id | edit_id | edit_page_id | edit_time | stat |
|---|---|---|---|---|---|
| 10 | 100 | 1 | 10 | 20210502 | 90 |
| 10 | 100 | 2 | 10 | 20210503 | 91 |
| 20 | 100 | 3 | 20 | 20210504 | 91 |
| 30 | 100 | 4 | 30 | 20210504 | 90 |
| 30 | 100 | 5 | 30 | 20210505 | 92 |
| 40 | 100 | 6 | 40 | 20210505 | 90 |
| 50 | 200 | 7 | 50 | 20210503 | 90 |
SELECT *
FROM pages
JOIN edits ON pages.page_id = edits.edit_page_id
JOIN ( SELECT edit_page_id, MAX(edit_time) edit_time
FROM edits
GROUP BY edit_page_id ) last_time ON edits.edit_page_id = last_time.edit_page_id
AND edits.edit_time = last_time.edit_time
| page_id | cat_id | edit_id | edit_page_id | edit_time | stat | edit_page_id | edit_time |
|---|---|---|---|---|---|---|---|
| 10 | 100 | 2 | 10 | 20210503 | 91 | 10 | 20210503 |
| 20 | 100 | 3 | 20 | 20210504 | 91 | 20 | 20210504 |
| 30 | 100 | 5 | 30 | 20210505 | 92 | 30 | 20210505 |
| 40 | 100 | 6 | 40 | 20210505 | 90 | 40 | 20210505 |
| 50 | 200 | 7 | 50 | 20210503 | 90 | 50 | 20210503 |
SELECT cat_id, stat, COUNT(*) cnt
FROM pages
JOIN edits ON pages.page_id = edits.edit_page_id
JOIN ( SELECT edit_page_id, MAX(edit_time) edit_time
FROM edits
GROUP BY edit_page_id ) last_time ON edits.edit_page_id = last_time.edit_page_id
AND edits.edit_time = last_time.edit_time
GROUP BY cat_id, stat
| cat_id | stat | cnt |
|---|---|---|
| 100 | 90 | 1 |
| 100 | 91 | 2 |
| 100 | 92 | 1 |
| 200 | 90 | 1 |