Correct use of GROUP BY

Question

I am trying to say whether is entity from table opportunities upsell or not. One company can have multiple opportunities (1:N) and upsell is true when opportunity has a connection to a company and some other opportunity in the past has already finished business successfully (therefore status is 7) with the same company. Put in other words, first opportunity with status 7 is not upsell, all others with the same company are upsell regardless of their status.

So this is what I've tried

SELECT opportunities.id ,true as upsell FROM opportunities, (
  SELECT companies.id as company_id, opportunities.id as opportunity_id 
  FROM opportunities 
  JOIN companies ON companies.id = opportunities.company_id
  WHERE opportunities.status = 7
  GROUP BY companies.id, opportunities.id
  HAVING min(opportunities.created_at) = opportunities.created_at)
AS subs WHERE subs.opportunity_id = opportunities.id

Idea here is to select all first successfully finished opportunities (based on created_at column) in subselect and then remove them from all opportunities who has the same company. However even the subselect doesn't work correctly, it returns all opportunities filtered by status = 7 so I've probably misunderstood the group by clause. -

Answer 1

If I understand correctly, you want opportunities to be tagged as true when:

• the status = 7
• it is not the first record for the company

If so, I think this is much simpler:

select o.*,
       (row_number() over (partition by o.company_id, o.status
                           order by created_at
                          ) > 1 or
        status <> 7
       ) as upsell_flag
from opportunities o;