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 'Asker Rep',
    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',
    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