User Posts (Download-Friendly)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Iota

Q&A for users of Iota, the open-source cryptocurrency for IoT that does not use a blockchain

DECLARE @UserId INT = ##UserId:INT##;

SELECT
-- p - Posts Table
-- u - Users Table

    ROW_NUMBER() OVER (ORDER BY p.Id ASC) AS 'ID',
    --p.Id AS [Post Link],
    q.Title AS 'Question',
    IIF(p.PostTypeId = 1, 'Q', 'A') AS 'Type', -- 1 = question, 2 = answer...
    qu.DisplayName AS 'Asker',
    qu.Reputation AS 'AskRep',
    q.Score AS 'Q Score',
    q.CreationDate AS 'Asked',

    --p.OwnerUserId,
    --u.DisplayName, -- after the 1st JOIN
    -- instead of: 
    --p.OwnerDisplayName, -- NULL, why does it exist?

    --u.Id, -- = p.OwnerUserId (the 1st JOIN)
    --u.AccountId, -- StackExchange Account Number

    p.Score AS 'P Score',
    q.ViewCount AS 'Views',
    q.AnswerCount AS 'Answers',
    au.DisplayName AS 'Answerer',
    au.Reputation AS 'AnsRep',
    a.Score AS 'AA Score',
    p.CreationDate AS 'Answered',
    q.CommunityOwnedDate AS 'Community Owned',
    q.ClosedDate AS 'Closed',
    -- How does 'PostsWithDeleted' work?
    --q.DeletionDate AS 'Deleted', -- NULL even with 'PostsWithDeleted' !?
    q.Tags AS 'Tags', -- How to split into columns?

    -- questions only 
    --p.AcceptedAnswerId, -- questions only
    --p.ViewCount, -- questions only
    --p.CommunityOwnedDate, -- questions only
    --p.ClosedDate, -- questions only
    --p.DeletionDate, -- questions only: doesn't work!?
    --p.Title, -- questions only (is in 'p.Id AS [Post Link]')
    --p.Tags, -- questions only
    
    -- IDs (to create links)
    IIF(p.ParentId IS NULL,p.Id,p.ParentId) AS 'Question ID', -- questions
    q.AcceptedAnswerId AS 'Acc. Answer ID', -- accepted answers
    p.Id AS 'Post ID', -- user's questions and answers
    qu.Id AS 'Asker ID',
    au.Id AS 'Answerer ID'

FROM Posts AS p
JOIN Users AS u ON p.OwnerUserId = u.Id
-- Needs a left join to not loose the answers!?
LEFT JOIN Posts AS q ON IIF(p.ParentId IS NULL, p.Id, p.ParentId) = q.Id
LEFT JOIN Users AS qu ON qu.Id = q.OwnerUserId 
LEFT JOIN Posts AS a ON q.AcceptedAnswerId = a.Id 
LEFT JOIN Users AS au ON au.Id = a.OwnerUserId

WHERE p.OwnerUserId=@UserId

ORDER BY p.Id ASC;

Enter Parameters

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