0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for anime and manga fans

-- Step 3: Extract comments for the posts in batches
WITH CommentBatch AS (
    SELECT 
        c.Id AS CommentId, 
        c.Text, 
        p.Title, 
        p.OwnerUserId AS UserId, 
        u.DisplayName,
        u.Location,
        t.TagName,
        ROW_NUMBER() OVER (PARTITION BY p.Id ORDER BY c.CreationDate DESC) AS CommentRowNum
    FROM Comments c
    INNER JOIN Posts p ON c.PostId = p.Id
    INNER JOIN Users u ON p.OwnerUserId = u.Id
    INNER JOIN PostTags pt ON p.Id = pt.PostId
    INNER JOIN Tags t ON pt.TagId = t.Id
    WHERE t.TagName IN ('python', 'java', 'csharp', 'php', 'javascript', 'go', 'swift') -- Programming languages filter
    AND p.OwnerUserId IN (193688,13,961113,17160,315935,1749403,1090562,10676716,3293881,320615,1687119,521799,250259,1704166,95706,328193,76217,576911,4604579,1197518,4983450,2684539,825,1679863,253056,82294,324584,43089,87189,140750,584518,5741205,5577765,5486,2587435,36565,243782,505154,616443,14104,434551,135589,635608,452307,230513,2836621,2372064,383904,575376,5202563,615,893780,41661,1873365,2970947,1745001,1084437,153982,850848,244296,1103872,7108653,1676363,13070,8690857,2147,869736,367456,4653,128165,546861,10035985,164835,3171,1270695,949476,4639,7429447,48082,1694,13531,454533,5157454,23528,2308683,266304,260127,114066,17516,44330,2648,89391,145190,62076,2319407,425275,582,82187,9990,416467)
   )
SELECT 
    cb.CommentId,  
    cb.Title AS CommentTitle,
    cb.Text AS CommentText,
    cb.UserId, 
    cb.DisplayName AS UserName,
    cb.Location AS UserCountry,
    cb.TagName AS CommentTag
FROM CommentBatch cb
WHERE cb.CommentRowNum BETWEEN 1 AND 500;

fork query

edited oct 26

created oct 24 anonymous