0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for pro webmasters

/* GENERAL NOTES ON STYLE:
Developing a style while coding in SQL is something that sort of naturally 
occurs.  When I started out, Dave encouraged me to follow his style as everyone
on our teams' is similar and it makes it easier to share code with one another.
Everyone has their quirks, but there are some common attributes (like a vertical
progression) that we all adhere to. I've highlighted some things that are not 
consistent amongst the team, but are things I've adopted because I think it 
makes the code look cleaner.
*/



DROP TABLE IF EXISTS #got
/* NEW SKILL 1b:
When creating a new table it will be important to include a DROP TABLE IF EXISTS 
statement to your code.  This ensures that when the query is re-run, you won’t get 
an error telling you your created table already exists.
*/
/* KATE STYLE NOTE 1:
Some people list all their drop table statements at the top of their code -
I did this too at the beginning, but eventually found that embedding them made 
it easier to trouble-shoot things as I was working on a query 
*/

SELECT
 Post_Id = p.Id
/* KATE SYTLE NOTE 2:
When it comes to labeling columns I always list an adjusted name in front - I
noticed you did not and that's totally ok.  For me I prefer it in front as it's
easier for me to find a datapoint if I have to reference past queries for something
*/
,Tag_Id = t.Id
/* KATE STYLE NOTE 3:
I also do my best, sometimes it is unavoidable, to title columns in a way that
avoids having to use the brackets [].  It doesn't really matter one way or another,
but I find having to type out the brackets is annoying and I'm sort of lazy, LOL.
An easy option to avoid the brackets, but not have everything run together is to
add an underscore between words
*/
,p.Score
,p.Body
,p.Title
,p.Tags
INTO #got
/* NEW SKILL 1a:
To create a table from query results use an INTO statement.  This will save the results
of the query you’ve run to a table so that the information can be joined with another 
table or manipulated in some way.  The most important thing to remember when using an INTO 
statement is that no two columns can have the same name.
*/
/* CODING TIP 1:
Temporary tables will be your greatest pal.  When I got started I always tried to
get everything I could into one query, but soon found that it's sometimes easier 
to build as you go.  Inputting results into a temporary table - achieved by using
an INTO statement with a #[name] - is a lifesaver.  If you ever need to reference 
a temp table outside the immediate query you're working on you have create a 'global'
temp table by using two hastags instead of one.
*/

FROM tags t
JOIN PostTags pt ON pt.TagId = t.Id
/* NEW SKILL 2:
Joining tables is an essential skill and one that I found tricky at first.  Here I am using 
a standard JOIN (also called an INNER JOIN).  INNER JOINs combine the contents of two tables 
where they match on a common key.  There are many types of joins, but the two we tend to use 
most frequently are JOIN and LEFT JOIN.  LEFT JOINs (also called LEFT OUTER JOIN) combine the 
contents of two tables even when the common key does not match - these are helpful to use in 
situations where only some results include a particular parameter.  For example, looking at 
the Posts table you’ll note that not all posts have a tag.  If I wanted to join the Posts table
with the Tags table, but I didn’t want to lose posts that didn’t have a tag, I would use LEFT 
JOIN.
*/
JOIN Posts p ON p.Id = pt.PostId
WHERE 1=1
/*KATE STYLE NOTE 4:
I always use 1=1 after a WHERE clause.  It's something Dave does and I just picked
up from him.  I like it because it creates a nice flow in the look of the code.
It's not necessary though.
*/
  AND   (
        t.TagName LIKE '%game%of%thrones%'
        OR
        t.TagName LIKE '%a%song%of%ice%and%fire%'
        )
/* CODING TIP 2:
Using parenthesis in a WHERE clause can be tricky.  You executed this very well! 
I generally try to avoid having more than two arguments when I use them since I've
been burned in the past by one to few parenthesis.  When I have a number of 
arguments to include I will usually use a CASE statement (see below)
*/

 

DROP TABLE IF EXISTS #ff 

SELECT
 * 
/* NEW SKILL 3:
Using an asterisk (*) after a SELECT statement will return all columns from the 
requested table.  This is handy when you want to see what columns exist in a table 
or when you need everything and don’t feel like typing it all out.
*/
INTO #ff
FROM #got g
WHERE 1=1
AND CASE    WHEN g.Title LIKE '%fan%fic%' THEN 1
/* NEW SKILL 4:
CASE statements are super handy - they can be used in SELECT statements and WHERE clauses.  
Here we’re using a CASE statement in the WHERE clause to isolate different ‘populations’ to 
include in the final result.  This is great when you want to include results that meet 
varying criteria. 
*/
            WHEN g.Tags LIKE '%fan%fic%' THEN 1
            WHEN g.Body LIKE '%fan%fic%' THEN 1 
              --210113 ksrosch added, I hadn't initially included a review of the Post's 
              --body, but it's essentially an elaboration of the title so I think we 
              --should include it
/* KATE STYLE NOTE 5:
I add alot of notes to my code as I work.  Sort of like breadcrumbs to help me
remember why I made a specific change or if I had to look something up to 
confirm my understanding.  Most people don't do this, but I find it's helpful
since my memory is getting shotty in my old age ;)
*/
            ELSE 0
            END = 1
/* CODING TIP 3:
When searching through a string I like to use to lowest common denominator
(does that work here?) in terms of phrasing. For example, in this scenario, 
searching %fan%fiction% would exclude any posts where the more colloquial term
fanfic is used.  Strings are tricky since they can really include just about
anything - in real life I'd generally try to avoid using them as a sole means
of identification since there's no guarantee that people all use the same terms
or can spell...HAH
*/



SELECT
 CountofPosts = COUNT(*)
/* NEW SKILL 5b:
For the COUNT function you can count the results of a particular column or the 
results of the rows in your overall data.  Here I’m counting the rows in my overall 
data so I’ve used COUNT(*) instead of referencing a specific column.
*/
,AverageScore = AVG(f.Score)
/* NEW SKILL 5a:
Functions work in SQL much the same way they would in other applications like Excel.  
The biggest difference is that when you have a query that has both columns and functions, 
you’ll need to include a GROUP BY statement, but we can get into that later.
Here we’re using the AVG function and since we’re hoping to get the cumulative average for 
every post in the #ff table I’ve not included any columns.  For the AVG functions you 
should select a particular column to average that includes numerical values.
*/
FROM #ff f

fork query

edited jan 16

created jan 13 jon.doe35017