SQL: Count within a specific timeframe within a bigger timeframe

user_16462786 0
Viewed 67

I have a table that looks like below. Every time a person speaks up, there will be a new row containing the date, the person who spoke up and a comment ID which is unique to every comment. The table has 3 years of data.

What I would like to do is to find a solution in SQL that elegantly picks up persons who spoke up more than 6 times within 2 days time frame in the past 1 year.

For instance, from the example table, what I would like to see are:

  • A: 2021-10-20 & 2021-10-19, 6 times
  • B: 2021-10-19 & 2021-10-18, 7 times

What's irrelevant are:

  • B: 2021-10-20 & 2021-10-19, 3 times
  • A: 2021-10-19 & 2021-10-18, 4 times
Date Person Comment ID
2021-10-20 A fjeiwarjea
2021-10-20 B ahfeawas
2021-10-20 A iewaokdow
2021-10-20 A oweidarek
2021-10-20 A sqoeidke
2021-10-19 B qejacjes
2021-10-19 A voewaiekd
2021-10-19 B saoweikladf
2021-10-19 A poewieakre
2021-10-18 A biewaldcwe
2021-10-18 A deaireal
2021-10-18 B zfdewoaierje
2021-10-18 B kfewajireuifd
2021-10-18 B mfeaiwruei
2021-10-18 B wrfeiarjeilwaf
2021-10-18 B yhfewaurhdfj
count time sql
2 Answers

Let's take a record, "2021-10-20 | A" and join with the same table to find other rows within the 2 days range. Aggregate them (Group By) and filter (Having)...

SELECT DISTINCT
    C1.[Date] AS FromDate,
    C1.[Date]+1 AS ToDate,
    C1.[Person],
    COUNT(1)
FROM Comments C1
JOIN Comments C2
      ON  C2.Person >= C1.Person
      and C2.[Date] >= C1.[Date]
      and C2.[Date] <= C1.[Date]+1
GROUP BY C1.[Date], C1.[Person]
HAVING COUNT(1) >= 6
user_86503000

Take a look at this:

declare @MyTable as table (
    [Date] datetime,
    Person varchar(3),
    CommentID varchar(50)
)

INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('20/10/21', 'A', 'fjeiwarjea')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('20/10/21', 'B', 'ahfeawas')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('20/10/21', 'A', 'iewaokdow')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('20/10/21', 'A', 'oweidarek')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('20/10/21', 'A', 'sqoeidke')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('19/10/21', 'B', 'qejacjes')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('19/10/21', 'A', 'voewaiekd')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('19/10/21', 'B', 'saoweikladf')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('19/10/21', 'A', 'poewieakre')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('18/10/21', 'A', 'biewaldcwe')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('18/10/21', 'A', 'deaireal')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('18/10/21', 'B', 'zfdewoaierje')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('18/10/21', 'B', 'kfewajireuifd')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('18/10/21', 'B', 'mfeaiwruei')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('18/10/21', 'B', 'wrfeiarjeilwaf')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('18/10/21', 'B', 'yhfewaurhdfj');

with Comments (FromDate, ToDate, Person, Times)
as
(
    select t1.[Date] FromDate, DATEADD(d, -1, t1.[date]) ToDate, t1.Person, 
    (
        select COUNT(*) from @MyTable t2 where t2.Person = t1.Person and t2.[Date] between DATEADD(d, -1, t1.[date]) and t1.[date]
    ) Times
    from @MyTable t1
    where t1.[Date] > DATEADD(yy, -1, getdate()) -- just last year
    group by t1.[Date], DATEADD(d, -1, t1.[date]), t1.Person
)
select * from Comments where Times >= 6

This will return this:

FromDate ToDate Person Times
2021-10-19 00:00:00.000 2021-10-18 00:00:00.000 B 7
2021-10-20 00:00:00.000 2021-10-19 00:00:00.000 A 6

I think the code is self explained, but if you need some explanation I can update the answer.

user_22328680
Related