Ben Guest Ben Guest - 1 month ago 5
MySQL Question

Select records that appear a minimum number of times between dates

My table columns are as follows:

| ID | UserID | AccountName | SortCode (encrypted) | AccountNumber (encrypted) | DateAdded


This table holds a history of bank details for users. If a user changes their bank details in our system, a new row is inserted into this table.

I am trying to create a query which will show users that have changed their details between 2 dates, and possibly a certain number of times between those dates. For example, show users who have changed their details between
2016-01-01
and
2016-06-01
at least 2 times.

Selecting between two dates is trivial, but I am struggling to incorporate the "at least # times". Additionally, the first record for each user should not be included in the results as this record is the user adding their bank details for the first time (so is not a change to their bank details).

Answer
select UserId
from your_table
where DateAdded >= '2016-01-01' 
  and DateAdded <= '2016-06-01'
and ID not in
(
   select min(ID) from your_table group by userId
)
group by UserId
having count(*) >= 2
Comments