Oh.. yes. that's obvious now that you say it. It's such a good thing that I force myself to learn new things often. Keeps me humble and empathetic with new users 
Now as for the grouping, I don't think I can just group_by(groupA) because I want the resulting dataset to have both groupA and groupB dimensions AND be sorted by the sum of the field my_amount in only one group. I need both groups later. And rejoining back to the original dataset is sloooooow. I think I made my example above just a wee bit too simple to fully illustrate my use case. Here's a slightly more elaborate (two extra data rows) reprex of what I'm trying to do in SQL:
CREATE TEMP TABLE my_table(groupA char , groupB char, my_amount real) ;
INSERT INTO my_Table(groupA, groupB, my_amount)
VALUES
('A','C',4),
('A','D',2),
('A','D',1),
('B','C',3),
('B','D',4),
('B','D',1);
SELECT
groupA,
groupB,
Row_number() OVER ( PARTITION BY groupA ORDER BY sum(my_amount) ) AS rank,
sum( my_amount ) as my_amount
FROM
my_table
GROUP BY
groupA,
groupB;
which results in:
| groupa |
groupb |
rank |
my_amount |
| B |
C |
1 |
3 |
| B |
D |
2 |
5 |
| A |
D |
1 |
3 |
| A |
C |
2 |
4 |
So you can see it maintains both groupa and groupb dimensions, sums up within the groups, but the rank is based only on grouping by groupa first and then taking the row_number within the group.
Thanks for giving this a think. I really appreciate it.