Search code examples
mysqlsqlredash

I want to calculate the sum of last transaction for A&B

Let's say the table looks like this:

user id date Amount
123 2022/11/01 5
456 2022/11/02 6
789 2022/11/03 8
123 2022/11/02 9
456 2022/11/04 6
789 2022/11/05 8

I want to calculate the sum of the very last transaction (only one for each user) for A & B FYI I'm using redash and I'm a beginner not sure what other info would you need, I tried MAX but was not sure how to apply it on more than one specific user.

Solution

  • Get the sum of Amount where user is A or B and date is the most recent date for each user

     SELECT SUM(AMOUNT) AS total
 FROM (
   SELECT AMOUNT, ROW_NUMBER() OVER (PARTITION BY USERID ORDER BY DATE DESC) AS RN
   FROM tableyoudidnotname
   WHERE userid in ('A','B')
 ) X
 WHERE X.RN = 1