Select all rows of all user logins, where user had logged in from more than 3 ip address within the past month Our SQL Server UserLogins table has these columns: UserID, DateTime, IpAddress.
How can I select rows of users who have logged in from more than three different ip addresses within a specified time period (e.g., one month), ordered by User ID
We can actually do this in one scan of the table, by using window functions. Unfortunately, we cannot use COUNT(DISTINCT... as a window aggregate, so we must hack it with DENSE_RANK and MAX
SELECT UserID, [DateTime], IpAddress
FROM (
    SELECT UserID, [DateTime], IpAddress,
        distinctIps = MAX(rn) OVER (PARTITION BY UserID)
    FROM (
        SELECT UserID, [DateTime], IpAddress,
            rn = DENSE_RANK() OVER (PARTITION BY UserID ORDER BY IpAddress)
            -- row-number distinct ip addresses
        FROM UserLogins
        WHERE [DateTime] &gt;= DATEADD(month, -1, GETDATE())
    ) ul
) ul
WHERE distinctIps &gt; 3;