0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Ubuntu users and developers

SELECT DISTINCT posts.title,
                Concat('https://stackoverflow.com/questions', '/', posts.id) AS
                Link,
                CASE
                  WHEN ( 2023 - Year(posts.creationdate) > 0 ) THEN (
                  posts.viewcount / ( 2023 - Year(posts.creationdate) ) )
                  ELSE ( posts.viewcount / 1 )
                END                                                          AS
                Traffic,
                posts.creationdate,
                posts.viewcount,
                posts.tags,
                posts.acceptedanswerid,
                posts.score,
                posts.answercount,
                posts.closeddate
FROM   posts
       -- only includes posts that have tags
       INNER JOIN posttags
               ON posttags.postid = posts.id
       INNER JOIN tags
               ON tags.id = posttags.tagid
WHERE  posts.viewcount > 3000
       AND posts.creationdate IS NOT NULL
       -- define a range
       AND posts.creationdate > '2017-01-01'
       AND posts.creationdate < '2023-04-10'
       -- include specific tags
       AND posts.tags LIKE '%##java##%'
       AND posts.tags LIKE '%##tag##%'
       AND posts.tags NOT LIKE '%##notag1##%'
       AND posts.tags NOT LIKE '%##notag2##%'
       AND posts.tags NOT LIKE '%##notag3##%'
       --, 'jpa', 'spring data', 'spring data jpa', 'eclipse link')
       -- include only questions
       AND posts.posttypeid = 1
       AND posts.commentcount >= 1
       AND posts.answercount >= 1
       -- only include questions with accepted answer
       AND posts.acceptedanswerid IS NOT NULL
       AND posts.score >= 1
       -- include only ones with traffic > 3000
       AND ( Year(posts.creationdate) = 2023
              OR ( posts.viewcount / ( 2023 - Year(posts.creationdate) ) >= 3000
                 ) )
ORDER  BY posts.viewcount DESC,
          posts.creationdate DESC
-- GROUP BY Posts.ViewCount

view current version

edited may 19 23

created may 19 23 jon.doe41475