Stack Exchange
log in help

WITH PostUpvotes AS ( SELECT PostId, COUNT(*) AS PostUpvo...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Science Fiction and Fantasy

Q&A for science fiction and fantasy enthusiasts

WITH PostUpvotes AS (
    SELECT 
        PostId, 
        COUNT(*) AS PostUpvoteCount
    FROM 
        Votes
    WHERE 
        VoteTypeId = 2
    GROUP BY 
        PostId
),
CommentUpvotes AS (
    SELECT 
        PostId AS CommentOnPostId, 
        COUNT(*) AS CommentUpvoteCount
    FROM 
        Votes
    WHERE 
        VoteTypeId = 2 AND PostId IN (SELECT Id FROM Comments)
    GROUP BY 
        PostId
)

SELECT 
    p.Id AS PostId,
    p.Body AS PostContent, -- Added line for post content
    p.OwnerUserId AS PostUserId, 
    p.CreationDate AS PostCreationDate,
    c.Text AS CommentContent, -- Added line for comment content
    c.UserId AS CommentingUserId,
    c.CreationDate AS CommentCreationDate,
    p.Tags AS Tags,
    up.Reputation AS PostUserReputation,
    up.UpVotes AS PostUserUpVotes,
    up.DownVotes AS PostUserDownVotes,
    up.Views AS PostUserViews,
    uc.Reputation AS CommentingUserReputation,
    uc.UpVotes AS CommentingUserUpVotes,
    uc.DownVotes AS CommentingUserDownVotes,
    uc.Views AS CommentingUserViews,
    pu.PostUpvoteCount,
    cu.CommentUpvoteCount
FROM 
    Posts p
LEFT JOIN 
    Comments c ON c.PostId = p.Id
LEFT JOIN
    Users up ON up.Id = p.OwnerUserId
LEFT JOIN
    Users uc ON uc.Id = c.UserId
LEFT JOIN
    PostUpvotes pu ON pu.PostId = p.Id
LEFT JOIN
    CommentUpvotes cu ON cu.CommentOnPostId = c.PostId -- Fixed to reference c.PostId for accurate join

WHERE 
    p.CreationDate >= '2022-07-19' AND
    p.CreationDate <= '2022-08-05' AND
    (
    p.Tags LIKE '%<python>%' OR
    p.Tags LIKE '%<machine-learning>%' OR
    p.Tags LIKE '%<data-science>%' OR
    p.Tags LIKE '%<scikit-learn>%' OR
    p.Tags LIKE '%<tensorflow>%' OR
    p.Tags LIKE '%<keras>%' OR
    p.Tags LIKE '%<pytorch>%' OR
    p.Tags LIKE '%<javascript>%' OR
    p.Tags LIKE '%<html>%' OR
    p.Tags LIKE '%<css>%' OR
    p.Tags LIKE '%<reactjs>%' OR
    p.Tags LIKE '%<angularjs>%' OR
    p.Tags LIKE '%<vue.js>%' OR
    p.Tags LIKE '%<django>%' OR
    p.Tags LIKE '%<flask>%' OR
    p.Tags LIKE '%<sql>%' OR
    p.Tags LIKE '%<mysql>%' OR
    p.Tags LIKE '%<postgresql>%' OR
    p.Tags LIKE '%<oracle>%' OR
    p.Tags LIKE '%<mongodb>%' OR
    p.Tags LIKE '%<redis>%' OR
    p.Tags LIKE '%<linux>%' OR
    p.Tags LIKE '%<windows>%' OR
    p.Tags LIKE '%<macos>%' OR
    p.Tags LIKE '%<bash>%' OR
    p.Tags LIKE '%<powershell>%' OR
    p.Tags LIKE '%<c>%' OR
    p.Tags LIKE '%<c++>%' OR
    p.Tags LIKE '%<haskell>%' OR
    p.Tags LIKE '%<scala>%' OR
    p.Tags LIKE '%<clojure>%' OR
    p.Tags LIKE '%<elixir>%' OR
    p.Tags LIKE '%<f#>%' OR
    p.Tags LIKE '%<android>%' OR
    p.Tags LIKE '%<ios>%' OR
    p.Tags LIKE '%<flutter>%' OR
    p.Tags LIKE '%<react-native>%'
    )
view current version

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results