CODEWITHSUNDEEP
sqlpostgresql
Opal
Opal

Reputation: 84864

PostgreSQL query performance and variations

I have an event table with the following columns user_id, points, created. What I need to is to get all points for given month, total points and total rank for given user_id. I need to do it in a single query which resulted in the following attempt:

select
    e.user_id,
    sum(e.points) as total_points,
    (select sum(points) from event where user_id = 1 and extract(month from created) = 1) as month_points,
    (select rank from (select user_id as id, rank() over (order by sum(points) desc) from event group by user_id) as rank where id = 1)
from
    event pte
where
    e.user_id = 1
group by
    e.user_id
;

What I'd like to ask is:

  1. Can this query be slow?
  2. Can it be done better / in an another way?
  3. Is rank function reliable enough or it has been misused?

Upvotes: 0

Views: 68

Answers (1)

Radek Postołowicz
Radek Postołowicz

Reputation: 4784

Assuming postgresql version greater or equal to 9.4, you can use aggregate filter clause to avoid subselects for month_points and rank:

select * from
    (select
        e.user_id,
        sum(e.points) as total_points,
        sum(points) filter (where extract(month from created) = 1) as month_points,
        rank() over (order by sum(points) desc)
    from
        event e
    group by
        e.user_id
    ) as inner_query
where user_id = 1;

Upvotes: 1

Related Questions