By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tab(
submission_date DATE,
submission_id INT,
hacker_id INT,
score INT
);
INSERT INTO tab VALUES
( '2016-03-01', 11, 1, 0 ),
( '2016-03-01', 12, 3, 15 ),
( '2016-03-01', 13, 2, 60 ),
( '2016-03-03', 14, 1, 0 ),
( '2016-03-03', 15, 4, 60 ),
( '2016-03-03', 16, 2, 25 ),
( '2016-03-03', 17, 2, 60 ),
( '2016-03-08', 18, 1, 0 ),
( '2016-03-08', 19, 5, 70 );
9 rows affected
WITH cte AS (
SELECT *,
DENSE_RANK() OVER(
ORDER BY submission_date ) AS rn_competition,
DENSE_RANK() OVER(
PARTITION BY hacker_id
ORDER BY submission_date) AS rn_submission
FROM tab
)
SELECT *
FROM cte
WHERE rn_competition = rn_submission
ORDER BY submission_id
| submission_date | submission_id | hacker_id | score | rn_competition | rn_submission |
|---|---|---|---|---|---|
| 2016-03-01 | 11 | 1 | 0 | 1 | 1 |
| 2016-03-01 | 12 | 3 | 15 | 1 | 1 |
| 2016-03-01 | 13 | 2 | 60 | 1 | 1 |
| 2016-03-03 | 14 | 1 | 0 | 2 | 2 |
| 2016-03-03 | 16 | 2 | 25 | 2 | 2 |
| 2016-03-03 | 17 | 2 | 60 | 2 | 2 |
| 2016-03-08 | 18 | 1 | 0 | 3 | 3 |