SQL to Determine Minimum Sequential Days of Access

SQL to determine minimum sequential days of access?

The answer is obviously:

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

EDIT:

Okay here's my serious answer:

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

EDIT:

[Jeff Atwood] This is a great fast solution and deserves to be accepted, but Rob Farley's solution is also excellent and arguably even faster (!). Please check it out too!

SQL to determine minimum sequential days when value delta was hit

and thanks for sharing this very interesting challenge.
Next time, it would be very helpful if you could provide:
Full DDL, Actual INSERT statements for the sample data, The desired result set in detail, and a somewhat more verbose explanation.
A few people posted answers and deleted them when they realized they misunderstood the question.

I wrote down the DDL and inserts:

CREATE TABLE FOO 
(ID INT PRIMARY KEY, Value DECIMAL(5,2));

INSERT INTO FOO (ID, Value)
VALUES  (  1 , 118.89 ),
         (  2 , 113.90 ),
         (  3 , 110.62 ),
         (  4 , 105.37 ),
         (  5 , 119.16 ),
         (  6 , 118.33 ),
         (  7 , 116.93 ),
         (  8 , 117.74 ),
         (  9 , 118.01 ),
         ( 10 , 125.00 ),
         ( 11 , 130.62 ),
         ( 12 , 137.50 ),
         ( 13 , 136.65 ),
         ( 14 , 133.80 ),
         ( 15 , 132.53 ),
         ( 16 , 133.03 ),
         ( 17 , 131.91 ),
         ( 18 , 134.06 ),
         ( 19 , 131.03 ),
         ( 20 , 132.38 );

SELECT  * 
FROM    FOO;

I hope that I understand your question correctly, so here is my solution approach.

Before getting to the actual SQL solution, I tried to realize the mathematical complexity.
Let's assume that we have 10 rows in the table.
The number of different sequential groups is a divergent series of natural numbers, or a triangular number.
It starts with 1 option for a group of 10 sequential rows from 1-10.
then we have 2 options for any group of 9 sequential rows, 1-9 and 2-10.
Then 3 for any group of 8 rows etc.
The total number of sequential groups at any length can be easily caluclated.
If it were a full traingular number, the forumula would be n(n+1)/2.
Here, since the smallest group consists of 2 rows, and not 1, it comes up to (n-1)(n-1+1)/2 = n(n-1)/2.

I will be using SQL Server syntax for this, as I don't like using PL/pgSQL, and don't have much experience with it.
Someone with more experience in PL/pgSQL is welcome to convert it, shouldn't be too hard.
I never understood why so many RDBMS don't allow combining imperative constructs with SQL on the same script scope.

My first thought was to try a naive, set based approach to calculate all possible groups using a recursive query,
with varying group sizes for the OVER clause.
For 500 rows we would need to compute the total deltas for 500*499/2 groups = ~125K.
It would have been nice if we could do something like:

DECLARE @MaxGroupSize INT = (SELECT COUNT(*) FROM Foo);
DECLARE @Threshold DECIMAL(5,2) = 13.5;
WITH GroupDeltas
AS
(
SELECT  1 AS GroupSize, 
        ID,
        CAST((  LEAD(Value) 
                OVER(ORDER BY ID ASC) - Value) 
        AS DECIMAL(38,2)) AS GroupDelta
FROM    Foo
UNION ALL
SELECT  (GroupSize + 1),
        ID,
        SUM(GroupDelta) 
            OVER (  ORDER BY ID ASC 
                    ROWS BETWEEN CURRENT ROW AND 0 /*NO GO WITH (GroupSize - 2)*/  FOLLOWING)
FROM    GroupDeltas
WHERE   (GroupSize + 1) <= @MaxGroupSize
)
SELECT  * 
FROM    GroupDeltas
WHERE   ABS(GroupDelta) >= @Threshold
        AND
        GroupSize = (
                        SELECT  MIN(GroupSize) 
                        FROM    GroupDeltas 
                        WHERE   GroupSize > 1 -- Eliminate Anchor
                                AND
                                ABS(GroupDelta) >= @Threshold   
                    );

but unfortunately, the frame offset must use a constant expression.
No variables nor column expressions are allowed.
Note that the query above works for the first example with the group size of 2,
but only because I used a literal 0 offset instead of the required (GroupSize - 2) which is not allowed...

It also would have been nice if we could add a stop condition to the recursive member

 AND NOT EXISTS (
                    SELECT  NULL
                    FROM    GroupDeltas
                    WHERE   ABS(GroupDelta) >= 13.5
                )

But we can only refernece the CTE in the recursive member once...

Anyway, this approach doesn't work to begin with, so I didn't test it any further.
I only added it here as an interesting mental exercise I went through.

That leaves us with an iterative approach.
Since you also asked for a 'good performing' query,
I figured we can get away without calculating all possible groups.

My idea was to create a loop that starts with the smallest possible group size,
and stop when we hit a match.
I didn't want to use a RBAR cursor, so I opted for the more efficient window function,
using dynamic execution to circumvent the offset constant limitation.
Following is my attempt.
Note that if there are more than 1 groups that satisfy the threshold, both will be shown.

DROP TABLE IF EXISTS #GroupDeltas;
GO

DECLARE @Threshold DECIMAL(5,2) = 19.2,
        @MaxGroupSize INT = (SELECT COUNT(*) FROM FOO),
        @GroupSize INT = 2, -- Initial Group Size
        @SQL VARCHAR(1000);

CREATE TABLE #GroupDeltas 
    (
        StartID INT, 
        GroupSize INT,
        GroupDelta DECIMAL(9,2),
        PRIMARY KEY (StartID, GroupSize)
    );

WHILE @GroupSize <= @MaxGroupSize
BEGIN
    SET @SQL = '
                ;WITH DeltasFromNext
                AS
                    (
                        SELECT  ID,
                                LEAD(Value) OVER(ORDER BY ID ASC) - Value AS Delta
                        FROM    FOO
                    )
                    SELECT  ID, 
                            ' + CAST(@GroupSize AS VARCHAR(5)) +',
                            SUM(Delta) 
                            OVER (  ORDER BY ID 
                                    ROWS BETWEEN 
                                    CURRENT ROW AND 
                                    ' + CAST(@GroupSize - 2 AS VARCHAR(5)) 
                                    + ' FOLLOWING)
                    FROM DeltasFromNext;
    '
    INSERT INTO #GroupDeltas
    EXECUTE (@SQL);
    IF EXISTS   (
                    SELECT  NULL
                    FROM    #GroupDeltas
                    WHERE   ABS(GroupDelta) >= @Threshold
                )
    BREAK;
    SET @GroupSize += 1
END
SELECT  * 
FROM    #GroupDeltas
WHERE   ABS(GroupDelta) >= @Threshold
ORDER BY GroupSize, StartID;

HTH

PS:
Feedback and improvement suggestions are very welcome. I find this to be a very interesting exercise and there are probably better ways to achieve it...
I may revisit it again if I have some time to spare.

Calculating difference in rows for many columns in SQL (Access)

With a self join:

select t1.ID, t1.[Date],
  t1.[Value] - t2.[Value] as [Difference to previous Day]
from tablename t1 inner join tablename t2
on t2.[ID] = t1.[ID] and t2.[Date] = t1.[Date] - 1

Results:

ID  Date        Difference to previous Day
1   31/1/2019   5
2   31/1/2019   10
3   31/1/2019   10

Edit.

For the case that there are gaps between your dates:

select
  t1.ID, t1.[Date], t1.[Value] - t2.[Value] as [Difference to previous Day]
from (
  select t.ID, t.[Date], t.[Value],
    (select max(tt.[Date]) from tablename as tt where ID = t.ID and tt.[Date] < t.[Date]) as prevdate
  from tablename as t
) as t1 inner join tablename as t2
on t2.ID = t1.ID and t2.[Date] = t1.prevdate

How to find possible consecutive values and return the minimum and the maximum values of that series?

Try the following query:

;WITH GatherDates AS
(
    SELECT DocID,
           StartTime AppointmentTime
    FROM Appointment

    UNION ALL

    SELECT DocID,
           EndTime
    FROM Appointment
)
,RemoveDuplicates AS
(
    SELECT DocID,
           AppointmentTime,
           COUNT(*) DuplicateCount
    FROM GatherDates
    GROUP BY DocID,
             AppointmentTime
)
,FindContinuosAppointment AS
(
    SELECT DocID,
           AppointmentTime,
           ROW_NUMBER() OVER(PARTITION BY DocID
                              ORDER BY AppointmentTime ASC) RN
    FROM RemoveDuplicates
    WHERE DuplicateCount = 1
)
SELECT ST.DocID,
       ST.AppointmentTime StartTime,
       ET.AppointmentTime EndTime
FROM FindContinuosAppointment ST
INNER JOIN FindContinuosAppointment ET
ON ST.DocID = ET.DocID
AND ST.RN = ET.RN - 1
AND ST.RN % 2 = 1
AND ET.RN % 2 = 0;

Check my SQL Fiddle link: http://www.sqlfiddle.com/#!3/04b71/1/0

Do let me know your comments.

SQL to Determine Minimum Sequential Days of Access