Hello,
I am trying to get the cotization of a product at a given date.
I have made a table:

CREATE TABLE PRICES ( NAME, VALIDFROM, PRICE ) AS
  SELECT 'GOLD', DATE '2017-01-01', 36000 FROM DUAL UNION ALL
  SELECT 'GOLD', DATE '2017-09-25', 42000 FROM DUAL UNION ALL
  SELECT 'GOLD', DATE '2017-10-03', 40800 FROM DUAL UNION ALL
  SELECT 'GOLD', DATE '2017-12-12', 39500 FROM DUAL UNION ALL
  SELECT 'GOLD', DATE '2019-09-03', 49700 FROM DUAL UNION ALL
  SELECT 'SILVER', DATE '2017-01-21', 28000 FROM DUAL UNION ALL
  SELECT 'SILVER', DATE '2018-01-23', 23000 FROM DUAL UNION ALL
  SELECT 'SILVER', DATE '2018-02-26', 17000 FROM DUAL;

There is no problem If i solve it with a subquery, like this:

SQL>

SELECT * FROM
    (
        SELECT
            NAME
            ,VALIDFROM
            ,ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY (VALIDFROM) DESC) AS RNK
        FROM PRICES
        WHERE VALIDFROM <=DATE '2017-10-25'
    )        
WHERE RNK=1
;

NAME VALIDFROM RNK
----- ----------------- ---
GOLD 17-10-03 00:00:00 1
SILVER 17-01-21 00:00:00 1

But I would like to encapsulate, as I have to use it in a bigger query, in a list with thousands of results.

I have tried CTE and window function, like this:

SQL>

WITH CTE_RNK (NAME, VALIDFROM, RNK) AS (
    SELECT
        NAME
        ,VALIDFROM
        ,ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY (VALIDFROM) DESC) AS RNK
    FROM PRICES
)
SELECT 
    *
FROM CTE_RNK
WHERE 
    VALIDFROM <=DATE '2017-10-25'
    AND RNK=1;

There is no results.

It looks like it calculates the ROW_NUMBER() before applying the date filter.

I have tried other approaches with window functions and also using a view, that would be a cleaner approach, with the same result.

There is any other approach to solve this with encapsulation, preferably with a view?

Here a view approach:
SQL>

CREATE VIEW PRICES_AT_GIVEN_DATE (NAME, VALIDFROM, RNK) AS 
    (SELECT
        NAME
        ,VALIDFROM
        ,ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY (VALIDFROM) DESC) AS RNK
    FROM PRICES)
    ;
SELECT 
    *
FROM PRICES_AT_GIVEN_DATE
WHERE 
    VALIDFROM <=DATE '2017-10-25'
    AND RNK=1
;    
SELECT 
    *
FROM PRICES_AT_GIVEN_DATE
WHERE 
    VALIDFROM <=DATE '2017-10-25'
    AND RNK=3
;

with cte_rnk (name, validfrom, rnk) as ( 
  select 
      name 
      ,validfrom 
      ,row_number() over (partition by name order by (validfrom) desc) as rnk 
  from prices 
  where validfrom <= date '2017-10-25' 
) 
select  
    * 
from   cte_rnk 
where  rnk=1;

NAME      VALIDFROM              RNK   
GOLD      03-OCT-2017 00:00:00     1 
SILVER    21-JAN-2017 00:00:00     1 

create or replace function filter_prices ( 
  max_valid_date date 
) 
  return clob sql_macro as
  stmt clob;
begin

  stmt := q'!select 
      name 
      ,validfrom 
      ,row_number() over (partition by name order by (validfrom) desc) as rnk 
  from prices 
  where validfrom <= max_valid_date!';

  return stmt;
end filter_prices;
/

select * 
from   filter_prices ( date '2017-10-25' )
where  rnk = 1;

NAME      VALIDFROM              RNK   
GOLD      03-OCT-2017 00:00:00     1 
SILVER    21-JAN-2017 00:00:00     1