Query to get rows for specific user where revenue > revenue of earliest date for that user

I have a table called user_revenue which looks like this

So the query I need, if ran for the user 'AAA' should only return the last row.

SELECT *
FROM user_revenue 
WHERE user_Id = 'AAA'  /*Change according to user_id*/
AND revenues > (SELECT revenues FROM (
SELECT revenues, MIN(date) FROM user_revenue
WHERE user_id='AAA' ))/*Change according to user_id*/

I managed to write this which gets the job done, but ideally I'd like to only have to type the user_id one time in the query (whereas here it needs to be typed twice), is there a way to do that?

Solution

You can alias the table user_revenue like u in the main body of the query and use u.user_Id in the subquery wherever needed:

SELECT u.*
FROM user_revenue u
WHERE u.user_Id = 'AAA'
AND u.revenues > (
  SELECT revenues FROM user_revenue
  WHERE user_Id = u.user_Id 
  AND date = (SELECT MIN(date) FROM user_revenue WHERE user_Id = u.user_Id)
)

or:

SELECT u.*
FROM user_revenue u
WHERE u.user_Id = 'AAA'
AND u.revenues > (
  SELECT revenues 
  FROM user_revenue
  WHERE user_Id = u.user_Id
  ORDER BY date
  LIMIT 1
)