Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Articles  (
  ID INTEGER,
  Title  VARCHAR(50),
  IsFavorite VARCHAR(1),
  IsFeatured VARCHAR(1),
  DatePublished  Date
);


 

  

INSERT ALL 
  INTO Articles VALUES ('1', 'My favourite featured article', 'Y', 'Y', to_date('2020-05-05', 'YYYY-MM-DD'))
  INTO Articles VALUES ('2', 'My favourite article', 'Y', 'N', to_date('2020-05-01', 'YYYY-MM-DD'))
  INTO Articles VALUES ('3', 'My featured article', 'N', 'Y', to_date('2020-04-30', 'YYYY-MM-DD'))
  INTO Articles VALUES ('4', 'My article', 'N', 'N', to_date('2020-04-30', 'YYYY-MM-DD'))
  INTO Articles VALUES ('5', 'Other favourite featured article', 'Y', 'Y', to_date('2020-05-04', 'YYYY-MM-DD'))
  INTO Articles VALUES ('6', 'Other favourite article', 'Y', 'N', to_date('2020-05-05', 'YYYY-MM-DD'))
  INTO Articles VALUES ('7', 'Other featured article', 'N', 'Y', to_date('2020-04-30', 'YYYY-MM-DD'))
  INTO Articles VALUES ('8', 'Other article', 'N', 'N', to_date('2020-04-29', 'YYYY-MM-DD'))
  INTO Articles VALUES ('9', 'Another favourite featured article', 'Y', 'Y', to_date('2020-05-07', 'YYYY-MM-DD'))
  INTO Articles VALUES ('10', 'Another favourite article', 'Y', 'N', to_date('2020-05-03', 'YYYY-MM-DD'))
  INTO Articles VALUES ('11', 'Another featured article', 'N', 'Y', to_date('2020-05-06', 'YYYY-MM-DD'))
  INTO Articles VALUES ('12', 'Another article', 'N', 'N', to_date('2020-05-06', 'YYYY-MM-DD'))
SELECT 1 FROM DUAL;
12 rows affected

-- first 2 (n) featured, first 3 (m) favourites, then 3 others (8 (k) total)
WITH favfeatrank AS (
  -- rank articles by date and whether they are favourite or featured
  SELECT ID, Title, IsFavorite, IsFeatured, DatePublished,
         ROW_NUMBER() OVER (ORDER BY IsFeatured DESC, DatePublished DESC) feat,
         ROW_NUMBER() OVER (ORDER BY IsFavorite DESC, DatePublished DESC) fav
  FROM Articles
),
numfavfeat AS (
  -- number of favourites that are also in the first n featured
  -- use m in the next line
  SELECT COUNT(CASE WHEN fav <= 3 THEN 1 END) AS featfav
  FROM favfeatrank
  WHERE feat <= 2 -- use n here
), 
articlerank AS (
  -- articles ranked according to featured, favourite and date published
  SELECT ID, Title, IsFavorite, IsFeatured, DatePublished,
         ROW_NUMBER() OVER (ORDER BY CASE WHEN feat <= 2 THEN 0 -- use n here
                                          WHEN fav <= 3 + featfav THEN 1 -- use m here
                                          ELSE 2
                                          END,
                                      DatePublished DESC) AS rn
           
  FROM favfeatrank
  CROSS JOIN numfavfeat
)
SELECT ID, Title, IsFavorite, IsFeatured, DatePublished
FROM articlerank 
WHERE rn <= 8 -- use k here


ID
TITLE
ISFAVORITE
ISFEATURED
DATEPUBLISHED


9
Another favourite featured article
Y
Y
07-MAY-20

11
Another featured article
N
Y
06-MAY-20

1
My favourite featured article
Y
Y
05-MAY-20

6
Other favourite article
Y
N
05-MAY-20

5
Other favourite featured article
Y
Y
04-MAY-20

12
Another article
N
N
06-MAY-20

10
Another favourite article
Y
N
03-MAY-20

2
My favourite article
Y
N
01-MAY-20

ID	TITLE	ISFAVORITE	ISFEATURED	DATEPUBLISHED
9	Another favourite featured article	Y	Y	07-MAY-20
11	Another featured article	N	Y	06-MAY-20
1	My favourite featured article	Y	Y	05-MAY-20
6	Other favourite article	Y	N	05-MAY-20
5	Other favourite featured article	Y	Y	04-MAY-20
12	Another article	N	N	06-MAY-20
10	Another favourite article	Y	N	03-MAY-20
2	My favourite article	Y	N	01-MAY-20