I'm trying to query the following data from stackexchange:

Fetch all the questions asked about Android, in past five years, and display post's id (a.iD), associated tags (a.Tags), for the person asking the question { display - name (a.OwnerDisplayName), its reputation (d.Reputation), associated badge (g.Name) }, the date when the question was asked (c.CreationDate), for the people who have answered the question {display - their names (b.OwnerDisplayName), reputations (e.Reputation), associated badges (f.Name) }

This is the query that I'm using (pardon my rusty SQL) :

    select a.Id, a.Tags, a.OwnerDisplayName, d.Reputation, g.Name, 
    c.CreationDate, b.OwnerDisplayName, e.Reputation, f.Name
    from Posts a 
    inner join Posts b on b.ParentId=a.Id
    inner join PostHistory c on c.Id=a.Id 
    inner join Users d on d.Id=a.Id
    inner join Users e on e.Id=b.Id
    inner join Badges f on f.Id=b.Id
    inner join Badges g on g.Id=a.Id
    where a.PostTypeId=1 and a.Tags like '%android%' 
    and c.CreationDate between '2008-03-01 00:00:00' and '2013-03-01 00:00:00'
    order by a.Id

When I run this query, I never get the correct number of people who have answered any question. Also from the data retrieved, there are many empty fields!?

I'm not sure if there is a problem in the query or if I'm missing something out. Any help would be appreciated.

  • 1
    As an aside, are questions about data explorer queries appropriate for StackOverflow? Or should they be on Meta? – user206222 Apr 7 '13 at 3:29
  • 1
    @Telthien Meta's a better choice. – Adam Lear Apr 7 '13 at 3:31
  • @r2j2 What do you mean by "associated badge"? – Adam Lear Apr 7 '13 at 3:34
  • @AnnaLear every user has a badge associated with it, depending on the number of questions asked, answers liked by others and other factors that I'm not too sure of. Like in your profile ,there are 64 badges associated with you. I'm trying to pull badges of all the people who have asked a question pertaining Android and those who have answered them. – r2j2 Apr 7 '13 at 3:39
  1. You're joining Posts and Users on post id which doesn't do anything useful. Use Posts.OwnerUserId instead.
  2. You don't need PostHistory to look up when the question was posted. Posts.CreationDate will do the job. But if you do use PostHistory, you should restrict the PostHistoryTypeId to 1, 2, or 3 - Initial Title, Initial Body, or Initial Tags.
  3. To reliably grab the display names, get them from the Users table instead.
  4. You will hit 50,000 row max for Data Explorer results long before you get all the data you are looking for. You might want to narrow down your date range.
  5. I'm a little hazy on how OwnerUserId and OwnerDisplayName are populated, but I believe your query right now will ignore questions and answers posted by users who have been deleted or posts that have been otherwise disassociated from the accounts that originally created them.

Ignoring #5 for simplicity's sake, you're looking at something like this:

select a.Id as [Post Link], a.Tags, d.DisplayName, d.Reputation,
(select count(*) from Badges where UserId = a.OwnerUserId) as AskerBadges,
a.CreationDate, e.DisplayName, e.Reputation,
(select count(*) from Badges where UserId = b.OwnerUserId) as AnswererBadges
from Posts a 
inner join Posts b on b.ParentId=a.Id
inner join Users d on d.Id=a.OwnerUserId
inner join Users e on e.Id=b.OwnerUserId
where a.PostTypeId=1 and a.Tags like '%android%' 
and a.CreationDate between '2008-03-01 00:00:00' and '2013-03-01 00:00:00'
order by a.Id

as [Post Link] gives you a URL to the question instead of just the raw ID. You can also replace d.DisplayName and e.DisplayName with d.Id as [User Link] and e.Id as [User Link], respectively, and that'll give you formatted links to the users' profiles (and, as a side-effect, their display names).

| improve this answer | |
  • If I try to stretch the query by adding this <br/> select OwnerDisplayName from Posts where Id = a.AcceptedAnswerId<br/> why do I get null entries if i'm fetching data for Android and iOS? I'm able to get the CreationDate for the "accepted answer" but not corresponding OwnerDisplayName. – r2j2 Apr 7 '13 at 18:17
  • ^^ also, i am able to pull the OwnerUserID for all those entries, but for some odd reason the names are not being fetched. – r2j2 Apr 7 '13 at 18:32
  • @r2j2 Don't rely on OwnerDisplayName. It's not populated in all cases. Use OwnerUserId as [User Link] instead. – Adam Lear Apr 7 '13 at 20:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .