CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-by
Hytpo
Hytpo

Reputation: 39

Group data by 15 days interval

I need select all users who have placed more than 3 posts in the same category (sql) in the last year with a time interval of less than 15 days. The problem is in the last part "with a time interval of less than 15 days".

This is how I select users for 2018 and tags (you can run it on https://data.stackexchange.com/stackoverflow/query/new):

SELECT OwnerUserId, Id, CreationDate
FROM Posts
WHERE OwnerUserId IN (
    SELECT OwnerUserId
    FROM Posts 
    WHERE YEAR(Posts.CreationDate) >= 2018 AND YEAR(Posts.CreationDate) < 2019 
    AND Posts.Tags LIKE '%sql%'
    GROUP BY OwnerUserId,PostTypeId
    HAVING COUNT(PostTypeId) > 3
)

I think that I should firsty group by date range and then count. But I dont know how can do it. I read how to group by weeks, but it is not what I need because posts can be from different weeks.

In the end I should just get a list of users.

Upvotes: 1

Views: 892

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270493

You can get posts that meet these conditions by using window functions. lead() can pick up the second record after this one, and then you can check whether this appears during the correct time period.

If I understand your terminology correctly:

select p.*
from (select p.*,
             lead(CreationDate, 2) over (partition by OwnerUserId, PostTypeId order by CreationDate) as CreationDate_2
      from posts p
      where p.CreationDate >= '2018-01-01' and
            p.CreationDate < '2019-01-01' and
            p.Tags like '%sql%'
     ) 
where CreationDate_2 < dateadd(day, 15, CreationDate)

Upvotes: 1

Salman Arshad
Salman Arshad

Reputation: 272256

You can use LAG function with offset parameter to find the date of the 2nd previous post, then calculate the date difference:

WITH questions AS (
    SELECT OwnerUserId
         , CreationDate AS PostDate
         , LAG(CreationDate, 2) OVER (PARTITION BY OwnerUserId ORDER BY CreationDate) AS PrevDate
    FROM Posts
    WHERE OwnerUserId IS NOT NULL    -- not community owned
    AND PostTypeId = 1               -- questions only
    AND CreationDate >= '2018-01-01' -- between 2018
    AND CreationDate < '2019-01-01'
    AND Tags LIKE '%<sql>%'          -- tagged sql
)
SELECT *
FROM questions
WHERE DATEDIFF(DAY, PrevDate, PostDate) <= 14

Upvotes: 1

Related Questions