So i want to generate rank for second set of values but starting with value greater than max from first set

Intermediary dataframe the intermediary dataframe is col1 col2 col3 group feature g1 f1 12 9.500000 1000 f2 0 8.000000 200 g2 f1 2 7.000000 330 f2 3 7.000000 331 f3 1 7.000000 100 g3 f1 7 7.666667 101 now for the final dataframe which finally gives group feature col1 col2 col3 0 g1 f1 1.0 1.0 1.0 1 g1 f2 2.0 2.0 2.0 2 g2 f1 2.0 1.0 2.0 3 g2 f2 1.0 1.0 1.0 4 g2 f3 3.0 1.0 3.0 5 g3 f1 1.0 1.0 1.0 for the second part of the question i would just change the indexing of the intermediary dataframe and compute rank after grouping on feature which gives feature group col1 col2 col3 0 f1 g1 1.0 1.0 1.0 1 f1 g2 3.0 3.0 2.0 2 f1 g3 2.0 2.0 3.0 3 f2 g1 2.0 1.0 2.0 4 f2 g2 1.0 2.0 1.0 5 f3 g2 1.0 1.0 1.0;i would use groupby on to produce an intermediary dataframe containing the sum avg and max columns not the rank and then again groupby on group only to produce the rank

You might replace the max subquery with a rank max is usually slower only when cus_id is the pi rank might be worse

Every time a rank finds a larger number than that stored have it send its new max to the root rank

If your rank is less than 4 it takes 0 instead it s the max part

While i think gbn s answer is probably sufficient i m wondering whether use of an over clause to establish a max date per id attribute with which to reduce the select in a where clause wouldn t be faster than a rank

If no rank is lower than no rank number there are no gaps in the table the query returns the max number + 1