Find records created in a period of 24hours (not especially the last 24hrs)

Question

I'm using SQLServer 2012 and I have a table timestamped like that :

Id           INT      NOT NULL,  --PK
IdUser       INT      NOT NULL   --FK from table USER
-- Some other fields
CreationDate DATETIME NOT NULL

This table records some type of action made by the user.
I'm trying to find IF a user did this type of action more than 20 times (ie there is 20 records with the same IdUser in that table) in a period of 24 hours.

The problem is I'm not trying to retrieve the records in the last 24 hours, but the records in a period of 24 hours (from the 1st record to today)

This is what I wrote :

SELECT IdUser 
FROM MyTable
WHERE IdUser = 1 
    AND CreationDate BETWEEN DATEADD(day, -1, GETDATE()) AND GETDATE() -- <= WRONG

But the WHERE clause doesn't fit my needs as I have no idea how to translate "seek 20 records from the user id=1 in a period of 24 hours, not especially the last 24 hours" in SQL

SAMPLE
Let's say our user Id=1 did 154 times this action. So I have 154 records in my table, with the datetime of the record.

IdUser = 1 ; CreationDate = 2016-07-29 12:24:54.590
IdUser = 1 ; CreationDate = 2016-07-29 16:51:55.856
IdUser = 1 ; CreationDate = 2016-07-27 14:12:36.125
(151 omitted rows)

What I'm seeking is if I can find 20 records in a period of 24 hours for a particular user. In this sample, only the 2 firsts are on a period of 24 hours. In my case, I'm seeking if there is 20 or more records in this period.

Could some one help me ?
Thank you

Answer 1

A rather painful way to do this (performance-wise) is with a join/group by or apply operator.

The apply should be better from a performance perspective:

select t.*
from t cross apply
     (select count(*) as cnt
      from t t2
      where t2.iduser = t.iduser and
            t2.creationdate >= t.creationdate and
            t2.creationdate < dateadd(day, 1, t.creationdate)
     ) t24
where t24.cnt >= 20;

An index on (iduser, creationdate) is a win for this query.