0

Given the following database schema (using rails-like associations, as this is a ruby-on-rails project, also please forgive me for how I phrase this for I am new to this forum):

Client has_many Deals (0 to many)
Deal has_many DealStages (1 to many)
DealStage belongs_to Stage

where Stage has an attribute position:integer and DealStage has an attribute current:boolean and only 1 DealStage can be marked as current = true

I am trying to get each Client's highest stage by position, with the ultimate goal of filtering a set of Clients by Stage ID. The current stage of each deal is defined by whichever DealStage is marked as current. This is what I have done to achieve this result:

  CREATE VIEW client_max_stages AS
    WITH client_max_stage_positions AS
    (
      SELECT DISTINCT clients.id AS client_id, max(stages.position) AS position
      FROM clients
      INNER JOIN deals ON deals.client_id = clients.id
      INNER JOIN deal_stages ON deal_stages.deal_id = deals.id
      INNER JOIN stages ON stages.id = deal_stages.stage_id
      WHERE deal_stages.current = true AND deals.is_active = true
      GROUP BY clients.id
    )
    SELECT DISTINCT client_max_stage_positions.client_id, stages.id AS stage_id
    FROM client_max_stage_positions
    INNER JOIN deals ON deals.client_id = client_max_stage_positions.client_id
    INNER JOIN deal_stages ON deal_stages.deal_id = deals.id
    INNER JOIN stages ON stages.id = deal_stages.stage_id
    WHERE stages.position = client_max_stage_positions.position;

I strongly believe this can be optimized in some way, but this gets me all the clients grouped by maximum stage, then each client with the ID of it's highest stage.

  • 2
    Your question is unclear to me. Are you obtaining the result you want or is it too slow? Please post the output of EXPLAIN (ANALYZE BUFFERS) for your query. p.s. welcome to the forum! :-) – Vérace Aug 15 '19 at 2:57
  • Keep in mind that WITH (CTE) is an optimization fence in PostgreSQL. This will affect how PostgreSQL plans the query which may or may not be desired. – user369450 Aug 16 '19 at 14:37
0

It seems you need in something similar to:

WITH cte AS ( SELECT *, 
                     RANK() OVER (PARTITION BY clients.id 
                                  ORDER BY stages.position DESC) rnk
              FROM clients
              INNER JOIN deals ON deals.client_id = clients.id
              INNER JOIN deal_stages ON deal_stages.deal_id = deals.id
              INNER JOIN stages ON stages.id = deal_stages.stage_id
              WHERE deal_stages.current = true AND deals.is_active = true )
SELECT *
FROM cte 
WHERE rnk=1

PS. Posted as an answer because it's too long for a comment.

PPS. I'm not sure because WHERE exists in CTE and not exists in main query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.