'select the rows that have more that X occurrences of the same foreign key and ignore the X most recent (timestamp)

I have a table with a property that is a foreign key and another property that is a timestamp

Table

id fk timestamp
1 2 16-02-2022
2 2 01-02-2022
3 2 02-02-2021
4 3 24-05-2020
5 3 11-01-2022
6 3 16-09-2021
7 3 01-01-2022

I want to select the rows that have more that X ocurrences of the same foreign key and i want to ignore the X most recent(timestamp) elements for each foreign key

So basically with a X of 2 the select would return

id fk timestamp
3 2 02-02-2021
6 3 16-09-2021
4 3 24-05-2020

And with a X of 3 the select would return

id fk timestamp
4 3 24-05-2020

Edit:

Thanks alot @forpas for the awesome aproach

Solution

SELECT id, fk, timestamp 
FROM (
  SELECT *,
         COUNT(*) OVER (PARTITION BY fk) counter,
         ROW_NUMBER() OVER (PARTITION BY fk ORDER BY timestamp DESC) rn
  FROM tablename
) t
WHERE counter > ? AND rn > ?;


Solution 1:[1]

SELECT 
id, fk, timestamp
FROM
(
SELECT id, fk, timestamp, ROW_NUMBER() OVER(PARTITION BY fk ORDER BY timestamp ASC) AS X,
COUNT(1) OVER(PARTITION BY fk) AS Total
FROM MyTable
) AS S
WHERE A.Total - S.X >= @X

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1