Postgresql subqueries using a calculated column

I am new to this platform and need to get a value using a column I already calculated. I know I need a subquery, but am confused by the proper syntax.

SELECT well_id, reported_date, oil,
  (EXTRACT(EPOCH FROM age(reported_date,
                          LAG(reported_date) OVER w))/3600)::int as hourly_rate,
                         (oil/hourly_rate)::double precision as six
                          FROM public.production
WINDOW w AS (PARTITION BY well_id ORDER BY well_id, reported_date 
             ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)

The error I am getting is ERROR: column "hourly_rate" does not exist LINE 4: (oil/hourly_rate)::double precision as six ^ HINT: Perhaps you meant to reference the column "production.hour_rate". SQL state: 42703 Character: 171 Which I understand...I have tried brackets, naming the sub queries and different tactics. I know this is a syntax thing can someone please give me a hand. Thank you

Solution

I'm a bit confused with your notation, but it looks like there are parenthesis issues: your from statement is not linked to the select.

In my opinion, the best way to manage subqueries is to wrinte someting like this :

WITH query1 AS (
   select col1, col2
      from table1
),
query2 as (
   select col1, col2
      from query1
   (additional clauses)
),
select (what you want)
 from query2
(additional statements)

Then you can manipulate your data progressively until you have the right organisation of your data for the final select, including aggregations