David Bentzon-Ehlers David Bentzon-Ehlers - 6 months ago 78
SQL Question

PostgreSQL - ERROR: column does not exist SQL state: 42703

I am trying to do a cohort analysis and compare average number of rentals based on the renter's first rental year(= the year where a renter rented first time). Basically, I am asking the question: are we retaining renters whose first year renting was 2013 than renters whose first year was 2015?

Here is my code:

SELECT renter_id,
Min(Date_part('year', created_at)) AS first_rental_year,
( Count(trip_finish) ) AS number_of_trips
FROM bookings
WHERE state IN ( 'approved', 'aboard', 'ashore', 'concluded', 'disputed' )
AND first_rental_year = 2013
GROUP BY 1
ORDER BY 1;


The error message I get is:

ERROR: column "first_rental_year" does not exist
LINE 6: ... 'aboard', 'ashore', 'concluded', 'disputed') AND first_rent...
^

********** Error **********

ERROR: column "first_rental_year" does not exist
SQL state: 42703
Character: 208


Any help is much appreciated.

Answer
SELECT renter_id,
       Count(trip_finish) AS number_of_trips 
FROM (
        SELECT renter_id, 
               trip_finish,
               Min(Date_part('year', created_at)) AS first_rental_year
        FROM   bookings 
        WHERE  state IN ( 'approved', 'aboard', 'ashore', 'concluded', 'disputed' ) 
     ) T
WHERE first_rental_year = 2013  
GROUP  BY renter_id
ORDER  BY renter_id ;