CODEWITHSUNDEEP
postgresql
Jack
Jack

Reputation: 377

How to nest a query in a postgres query

I'm trying to build a Postgres query (shown below) for the following requirement.

I have 2 tables, member and booking. For each booking the member receives a rating (a rating can be between 1 to 5, 5 being the top score). The output of this query is currently showing each member's overall score.

I now need to add the most recent booking that each member has but I'm not sure how!? :-( This should be possible as the booking table has a column time_starts which I can use. You can see my attempt below, I'm basically trying to add the following onto my existing query:

Get the latest booking for each member

SELECT time_starts from booking WHERE id = XYZ ORDER BY time_starts DESC LIMIT 1

Query

with member_ratings AS (                        
    select                  
        r.member_id,                
        sum(IsNull(r.rating, 0)) "total",               
        cast(count(*) * 5 as decimal) "possible_max",               
        cast(total/possible_max as decimal(10,2)) "score_as_percentage",                
        cast(5 * score_as_percentage as decimal(10,2)) "score"
    from review r
    join booking b on 'urn:booking:' || b.id = r.booking_urn        
    group by 1                  
)
SELECT *            
FROM member_ratings                     
where score < 4.8                       
order by score desc

My attempt

with member_ratings AS (                        
    select                  
        r.member_id,                
        sum(IsNull(r.rating, 0)) "total",               
        cast(count(*) * 5 as decimal) "possible_max",               
        cast(total/possible_max as decimal(10,2)) "score_as_percentage",                
        cast(5 * score_as_percentage as decimal(10,2)) "score",
        "most_recent" = SELECT time_starts from booking WHERE id = XYZ ORDER BY time_starts DESC LIMIT 1
    from review r
    join booking b on 'urn:booking:' || b.id = r.booking_urn        
    group by 1                  
)
SELECT *            
FROM member_ratings

Many thanks

Query updates available here

https://dbfiddle.uk/?rdbms=postgres_13&fiddle=409abbd3519d30f6de63003752d7d9fb

Upvotes: 0

Views: 218

Answers (1)

Bergi
Bergi

Reputation: 665536

I don't think you need a subquery here. You're already selecting from the booking table, grouped by the member - all you need is an aggregate function giving you the last time_starts date in each group:

select
    r.member_id,
    sum(IsNull(r.rating, 0)) "total",
    cast(count(*) * 5 as decimal) "possible_max",
    cast(total/possible_max as decimal(10,2)) "score_as_percentage",
    cast(5 * score_as_percentage as decimal(10,2)) "score",
    max(b.time_starts) "most_recent"
from review r
join booking b on 'urn:booking:' || b.id = r.booking_urn
group by 1;

Upvotes: 1

Related Questions