Given this date example:

I need to get a COUNT of user_id's WHERE the for the most recent timestamp (for each user_id) the timestamp is "today" and the total in that row is > 0 AND for the 2nd most recent timestamp for that same user_id the total for that row is = 0.

Using the data above, the resulting COUNT is 2 (user_id 2 and 5 meet the criteria). This is also assuming "today" is 2022-06-24.

I have tried this query (below), which works for all scenarios except when a user_id has three or more timestamps. So in the example above, user_id 2 is not counted.

`

SELECT COUNT(table1.user_id)  
FROM table as table1, table as table2  
    WHERE table1.user_id IN (
        SELECT table1.user_id
        FROM table table1
        GROUP BY table1.user_id
        HAVING COUNT(*) = 2
     )
     AND date(table1.timestamp) = CURDATE()
     AND (table1.user_id = table2.user_id)
     AND date(table2.timestamp) < CURDATE()
     AND table2.total = 0
     AND table1.total > 0

`

CodePudding user response：

Use ROW_NUMBER to number a user's last row #1 and their penultimate row #2. Then aggregate and use conditional aggregation (CASE expression in the aggregation function) to check the values. Then aggregate again to count the matches.

select count(*)
from
(
  select user_id
  from
  (
    select
      t.*,
      row_number() over (partition by user_id order by timestamp desc) as rn
    from mytable t
  ) numbered
  where rn <= 2
  group by user_id
  having max(timestamp) = curdate()
  and max(case when rn = 1 then total end) > 0
  and max(case when rn = 2 then total end) = 0
) matches;

For old MySQL versions replace

row_number() over (partition by user_id order by timestamp desc) as rn

by

(
  select count(*) 
  from same_or_newer
  where same_or_newer.user_id = t.user_id
  and same_or_newer.timestamp >= t.timestamp
) as rn