SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE scorelist (ID INT, USER_ID INT, SCORE INT, SEASON INT); INSERT INTO scorelist VALUES (1, 1, 35, 5), (2, 1, 45, 2), (3, 2, 80, 4), (4, 2, 85, 1), (5, 3, 65, 2); WITH ordered_scorelist AS ( SELECT scorelist.*, row_number() over (partition by USER_ID order by SEASON DESC) rn, MAX(SEASON) over() last_season FROM scorelist ) SELECT USER_ID, SCORE, SEASON, last_season FROM ordered_scorelist WHERE rn = 1 AND last_season - SEASON <= 3 ORDER BY SCORE DESC;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear