By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table product (
productid number(6),
productname varchar2(10)
)
create table product_sequence (
productid number(6),
sequenceid number(6),
orderid number(6),
ordername varchar2(10)
)
select
p.productid, p.productname, ps.orderid, ps.orderName,
count(distinct p.productid) over() as total
from product p
join product_sequence ps on p.productid = ps.productid
where ps.orderid in ('12','13','14')
and p.productid in (
select *
from (
select distinct p.productid
from product p
join product_sequence ps on p.productid = ps.productid
where p.productname like '%theremin%' -- filter #1
and ps.orderid in ('12','13','14') -- filter #2
) x
order by productid
offset 300 rows -- get the 4th page
fetch first 100 rows only -- page size set to 100 rows
)