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',
    qu.DisplayName AS 'Asker',
    qu.Reputation AS 'AskRep',
    IIF(p.PostTypeId = 1, 'Q', 'A') AS 'Type', -- 1 = question, 2 = answer...
    p.CreationDate AS 'Asked',
    p.Score AS 'Q Score',

    --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

    q.ViewCount AS 'Views',
    q.AnswerCount AS 'Answers',
    au.DisplayName AS 'Answerer',
    au.Reputation AS 'AnsRep',
    a.Score AS 'A Score',
    q.CommunityOwnedDate AS 'Community Owned',
    q.ClosedDate AS 'Closed',
    q.DeletionDate AS 'Deleted',
    q.Tags AS 'Tags',

    -- questions only 
    --p.AcceptedAnswerId, -- questions only
    --p.ViewCount, -- questions only
    --p.CommunityOwnedDate, -- (probably) 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

    IIF(p.ParentId IS NULL,p.Id,p.ParentId) AS 'Question ID',
    q.AcceptedAnswerId AS 'Acc. Answer ID',
    p.Id AS 'My ID',
    qu.Id AS 'Asker ID',
    au.Id AS 'Answerer ID'

FROM PostsWithDeleted AS p
JOIN Users AS u ON p.OwnerUserId = u.Id
-- Needs a left join to not loose the answers!?
LEFT JOIN PostsWithDeleted 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 PostsWithDeleted 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