How to nest a query in a postgres query

Question

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

Use (SELECT …) AS "most recent". No equals sign. Just an expression before the alias. (Also, get a proper value instead of XYZ) — Bergi
– Bergi, Commented Nov 3, 2020 at 14:59
The query is returning members and their bookings. Each booking has a rating. But I also want to have the date of the last booking each member received. — Jack
– Jack, Commented Nov 3, 2020 at 17:26
so the subquery (to get the most recent booking) needs to be inside the "join booking b on 'urn:booking:' || b.id = r.booking_urn" filter.. My attempt I don't think can do that because it's outside the join and in the select (trying to explain this is pushing my sql knowledge) — Jack
– Jack, Commented Nov 3, 2020 at 17:28

Bergi · Accepted Answer · 2020-11-03 19:52:47Z

1

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;

answered Nov 3, 2020 at 19:52

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jack Over a year ago

I'll do some testing but I think you're right, initial checks using your query it looks good

Collectives™ on Stack Overflow

How to nest a query in a postgres query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related