20
\$\begingroup\$

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?

\$\endgroup\$
  • 1
    \$\begingroup\$ This query is awesome. I just browser all my selfies and found that they not only convey a very decent history of my work and hobby projects, but also are some of the most interesting and puzzling questions ever faced by me. \$\endgroup\$ – F.P Sep 1 '15 at 11:31
  • 2
    \$\begingroup\$ SQL is just like reading English tell that to the 4K LOC sproc that I have to maintain. XD \$\endgroup\$ – MikeTheLiar Sep 1 '15 at 17:47
14
\$\begingroup\$

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

\$\endgroup\$
  • 2
    \$\begingroup\$ Very nice point about "I take advantage of the fact SQL often reads like English to add in documentation that flows with it." I think I tried to do something like this with JavaScript a long time ago. \$\endgroup\$ – SirPython Aug 31 '15 at 22:13
  • 2
    \$\begingroup\$ Not sure how easily that would work with JS, but we have JSDoc for something after all ;) \$\endgroup\$ – Phrancis Aug 31 '15 at 22:49
  • \$\begingroup\$ Why do you have both ON "Answers".OwnerUserId = "Questions".OwnerUserId and WHERE "Answers".OwnerUserId = ##UserId## AND "Questions".OwnerUserId = ##UserId##? If the redundancy is intended to help the query optimiser then it would be good to explain that, and ideally link to a resource which confirms that it makes a difference. \$\endgroup\$ – Peter Taylor Sep 1 '15 at 9:41
  • \$\begingroup\$ @PeterTaylor that was actually a result of copy-pasting the original query. I'm not sure how the query optimizer would go about it one way or the other, in that small of a case if may not make any difference. \$\endgroup\$ – Phrancis Sep 1 '15 at 14:31
  • \$\begingroup\$ Logically, the JOIN is over Answer.ParentId == Question.Id only and already Answer.PostTypeId == @AnswerType etc. are rather part of the WHERE clause. Is that also just a copy-paste problem or is there a technical advantage? \$\endgroup\$ – Hagen von Eitzen Sep 1 '15 at 16:10
12
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ select Q.OwnerUserID as [User Link] and where Q.OwnerUserID = ##UserId##... because the user might have forgotten what id was entered...? \$\endgroup\$ – Simon Forsberg Aug 31 '15 at 21:24
  • 5
    \$\begingroup\$ @Simon - Because Users like self-validation, and it's good for the ego to see your name as a link! \$\endgroup\$ – rolfl Aug 31 '15 at 21:25
  • \$\begingroup\$ Where did the Q come from? \$\endgroup\$ – SirPython Aug 31 '15 at 21:42
  • 4
    \$\begingroup\$ OK, confession, I like Q and A aliases for queries related to Questions and Answers in SEDE. The Q is created as an Aliase for Posts that are PostTypeId = 1 - in other words, Questions. This alias is created here: from Posts Q. \$\endgroup\$ – rolfl Aug 31 '15 at 21:43
12
\$\begingroup\$

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

\$\endgroup\$
  • \$\begingroup\$ Why one of my selfie is not fetched by above query? \$\endgroup\$ – Bhushan Sep 1 '15 at 7:45
  • 2
    \$\begingroup\$ @Bhushan because it's on Stack Overflow not Code Review \$\endgroup\$ – Johnbot Sep 1 '15 at 8:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.