SQL to determine minimum sequential days of access ?

SQL to determine minimum sequential days of access ?

Asked on December 17, 2018 in SQL Server.
Add Comment


  • 5 Answer(s)

    Have a look at this solution,

    SELECT DISTINCT UserId
    FROM UserHistory uh1
    WHERE (
          SELECT COUNT(*)
          FROM UserHistory uh2
          WHERE uh2.CreationDate
          BETWEEN uh1.CreationDate AND DATEADD(d, @days,     uh1.CreationDate)
          ) = @days OR UserId = 52551
    

    We can also do as follows:

    DECLARE @days int
    DECLARE @seconds bigint
    SET @days = 30
    SET @seconds = (@days * 24 * 60 * 60) - 1
    SELECT DISTINCT UserId
    FROM (
        SELECT uh1.UserId, Count(uh1.Id) as Conseq
        FROM UserHistory uh1
        INNER JOIN UserHistory uh2 ON uh2.CreationDate
            BETWEEN uh1.CreationDate AND
                DATEADD(s, @seconds, DATEADD(dd, DATEDIFF(dd, 0,     uh1.CreationDate), 0))
            AND uh1.UserId = uh2.UserId
        GROUP BY uh1.Id, uh1.UserId
        ) as Tbl
    WHERE Conseq >= @days
    
    Answered on December 17, 2018.
    Add Comment

    That can be done much simpler,

    WITH numberedrows
        AS (SELECT ROW_NUMBER() OVER (PARTITION BY UserID
                                                  ORDER BY CreationDate)
            - DATEDIFF(day,'19000101',CreationDate) AS TheOffset,
            CreationDate,
            UserID
         FROM tablename)
    SELECT MIN(CreationDate),
          MAX(CreationDate),
          COUNT(*) AS NumConsecutiveDays,
          UserID
    FROM   numberedrows
    GROUP   BY   UserID,
            TheOffset
    

    We’re looking for a range that has a consistent offset.  Because being that if we have list of the days (as a number), and a row_number, then missed days make the offset between these two lists slightly bigger. So

    Also have use something called “ORDER BY NumConsecutiveDays DESC” at the end of this, or say “HAVING count(*) > 14” for a threshold…

    Keep in mind that it is works in SQL2005 and on, helped by an index on tablename

    Here , we are using TheOffset. Because the Turns out Offset is a reserved word.

    There is better to use COUNT(*) is very valid – Here it shold have done that in the first place but wasn’t really thinking. but Previously instead of using this, it was using datediff(day, min(CreationDate), max(CreationDate))

    Answered on December 17, 2018.
    Add Comment

    The solution for determine minimum sequential days of access in sql is very simple, try this

    SELECT users.UserId, count(1) as cnt
    FROM users
    WHERE users.CreationDate > now() - INTERVAL 30 DAY
    GROUP BY UserId
    HAVING cnt = 30
    
    Answered on December 17, 2018.
    Add Comment
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    WITH
      -- This table contains all the distinct date
      -- instances in the data set
      dates(date) AS (
        SELECT DISTINCT CAST(CreationDate AS DATE)
        FROM Posts
        WHERE OwnerUserId = ##UserId##
      )
    ...
    Answered on January 14, 2019.
    Add Comment
    SELECT users.UserId, count(1) as cnt
    FROM users
    WHERE users.CreationDate > now() - INTERVAL 30 DAY
    GROUP BY UserId
    HAVING cnt = 30
    Answered on January 14, 2019.
    Add Comment


  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.