Oh my goodness: How many selfies have I taken?

Question

The title is a joke on how some people take a lot of selfies pictures

Recently, in The 2nd Monitor, there was a brief discussion on selfie answers, starting with this post.

At one point, a user said this:

I don't know how many selfies I have :s

I immediately thought: this is the perfect opportunity for an SEDE query!

Try it out!

DECLARE @QuestionId INT = (SELECT Id FROM PostTypes WHERE Name = 'Question');
DECLARE @AnswerId INT = (SELECT Id FROM PostTypes WHERE Name = 'Answer');

DECLARE @UserAnswers TABLE (AnswerId INT, ParentId INT);
DECLARE @UserQuestions TABLE (QuestionId INT);

INSERT INTO @UserAnswers
SELECT Id, ParentId
FROM Posts
WHERE OwnerUserId = ##UserId## AND PostTypeId = @AnswerId;

INSERT INTO @UserQuestions
SELECT Id
FROM Posts
WHERE OwnerUserId = ##UserId## And PostTypeId = @QuestionId;

SELECT AnswerId
FROM @UserAnswers
JOIN @UserQuestions
ON ParentId=[@UserQuestions].QuestionId;

The above query takes the user's ID and returns a list of all their selfie answers that they have posted.

This is my first time ever writing any form of SQL and I'm still trying to get used to the overall flow of it, so I'm really open to any recommendations.

However, here are a few things I had in mind:

• Is it good that I stored the posts in tables, or is it unnecessary?

• Am I following good SQL practices/conventions?

• I was really confused on how I should break up the SQL into multiple lines, so, after much trial and error, it seemed to me having one instruction per line was the cleanest. However, if this is not recommended, I am open to change.

• Am I missing indentation anywhere?

• To me, reading SQL is just like reading English. Therefore, writing documentation seemed redundant. However, I am not familiar with good documentation and SQL, so is there anything documentation-wise that I should add?

Answer 1

One by one, then:

Is it good that I stored the posts in tables, or is it unnecessary?

It is unnecessary, in this case. What your table variables are doing for you here really is simulating a self-referencing join. You could instead use aliases on the actual table(s), for instance:

FROM Posts AS "Answers"
  JOIN Posts AS "Questions"
    ON "Answers".OwnerUserId = "Questions".OwnerUserId
    AND "Answers".ParentId = "Questions".Id
    AND "Answers".PostTypeId = @AnswerId
    AND "Questions".PostTypeId = @QuestionId
WHERE "Answers".OwnerUserId = ##UserId## 
  AND "Questions".OwnerUserId = ##UserId## 

(Note that double-quotes are not needed for aliases, but I personally like using them as a visual reference that something is an alias instead of an actual database object)

---

Am I following good SQL practices/conventions?

Yes, I think that looks good (besides what I just mentioned).

---

I was really confused on how I should break up the SQL into multiple lines, so, after much trial and error, it seemed to me having one instruction per line was the cleanest. However, if this is not recommended, I am open to change.

And...

Am I missing indentation anywhere?

In SQL there are as many indentation/formatting styles as there are people who write SQL. One way that seems to work good for many is to have all your primary keywords at minimum indent, and the rest indented 2, 4 or more spaces, whatever is called for.

The primary keywords are (for SELECT statements):

WITH
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

Of course many times you won't use all of those, but this works pretty good as a thumb rule. For the rest, as long as you keep it readable and use some of your common sense you would apply for other languages, you should do fine.

---

Update: Addressing additional question.

To me, reading SQL is just like reading English. Therefore, writing documentation seemed redundant. However, I am not familiar with good documentation and SQL, so is there anything documentation-wise that I should add?

The answer as usual is it depends. In simple queries like yours, documentation would probably just be clutter. In SQL as in every other language, if the code is written well (especially good naming, aliasing, etc.) you often don't need documentation.

There is one style of documentation which I have come up with that can be useful for queries where others might have to look at after you. Here's an example (imagine you were querying data over a certain period of time, which you could also do with SEDE by asking the user to input a period):

DECLARE /* The interval in Days how far back we want to query for values. */
    @DaysInterval INT = 90;
IF @DaysInterval > 0 /* meaning we would go into the future instead of the past... */
    SET @DaysInterval = @DaysInterval * -1;
DECLARE /* Variables for which dates to query from and up to, based on @DaysInterval. */
      @FromDate DATETIME = DATEADD(DAY, @DaysInterval, GETDATE())
    , @ToDate DATETIME = GETDATE(); 
DECLARE /** Filters for which tags to query.  */
    @TagsFilter TABLE (
        Tag VARCHAR(20)
    );
INSERT INTO @TagsFilter
    SELECT 'sql' UNION
    SELECT 'python' UNION
-- etc.

I take advantage of the fact SQL often reads like English to add in documentation that flows with it like that.

There are also cases when you would want to add documentation that explains why you are doing something a certain way, as it is now always obvious... For example:

/**
 * Temp table to hold results from the posts tables from each respective SE site database.
 * This will be used in conjunction with @TagsFilter in order to match records. 
 */
IF OBJECT_ID('tempdb..#SESites') IS NOT NULL
    DROP TABLE #SESites;

CREATE TABLE #SESites (
-- etc.

Hope this helps :)

Answer 2

This can be solved with a much simpler join. The process of loading data in to table variables actually makes the process a lot more complicated.

Consider the following:

select Q.OwnerUserID as [User Link], Q.Id as [Post Link]
from Posts Q
inner join Posts A
   on A.parentId = Q.Id
  and A.OwnerUserID = Q.OwnerUserID
  and A.PostTypeId = 2
where Q.OwnerUserID = ##UserId##
  and Q.PostTypeId = 1

See query here: My Selfies

With SQL, staying in a Set-based mindset/framework, is almost always better.

Answer 3

Is it good that I stored the posts in tables, or is it unnecessary?

I would say that it is unnecessary.

Instead of using those temporary tables, you can run a SELECT with a JOIN directly on the Posts table.

Additionally, using the ParentId condition, your @QuestionId and @AnswerId is not necessary. (those would be better named as Type instead of Id IMO BTW). Only answers have ParentId set, and only to a question.

You should also use the SEDE-specific trick of naming a column Post Link, to make it - you guessed it - a post link.

In addition, you could add a few more interesting columns, such as Answer Score, Question Score, and Creation Date, to the query.

Your SEDE query can be rewritten as follows:

SELECT Answers.Id AS [Post Link],
  Answers.Score AS AnswerScore,
  Questions.Score AS QuestionScore,
  Questions.CreationDate
FROM Posts AS Answers
  JOIN Posts AS Questions ON (Answers.ParentId = Questions.Id)
WHERE Answers.OwnerUserId = ##UserId##
  AND Questions.OwnerUserId = ##UserId##;

Test it here: https://data.stackexchange.com/codereview/query/357962/selfies?UserId=31562