SQL Guide for Platform Data

🫴 SELECT Queries

All SQL queries include a SELECT statement and a FROM statement.

• 'SELECT' is a special term in SQL that means "get me this data." After writing SELECT, you write out the names of whatever columns you want to retrieve. If you want to retrieve all data in columns named username and follower_count, you simply spell out those column names.
• 'FROM' is another special SQL term that means "from this table." After writing FROM, you write the name of the table where the data live. In this case, the table is named users.

SELECT username, follower_count
FROM users

If you were to translate the above code into plain English, it would read "Get me the username and follower count data from the users table."

🔗 JOIN Operations

Often, you'll want data from more than one table. To connect data from multiple tables, you'll use the JOIN and ON statements.

• 'JOIN' is a special term in SQL that means "combine data from these two tables." After writing JOIN, you write the name of the second table you want to connect to the first table.
• 'ON' is another special SQL term that specifies some variable that exists in both tables that you want to use to connect the data. If we have a table containing data on users and another table containing data on posts, both of those tables would contain a variable like user_id identifying the user (in the users table) and the author (in the posts table). So, you'll need to say what the variable is that you want to join the data on.

SELECT u.username, p.content
FROM posts p
JOIN users u ON p.user_id = u.user_id;

If you were to translate the above code into plain English, it would read "Get the username data from the users table and the content data from the posts table, and then give me each user's content."

Note: In the example above, I used table aliases (the single-letter names after the table names) to make the query easier to read and write. This is optional, but recommended when working with multiple tables because it is possible that multiples tables contain variables with the same names, even if they refer to different things. If you try to SELECT variables with the same names from multiple tables without using aliases, SQL will return an error. By using aliases (like "u" for users and "p" for posts), you can specify exactly which table each variable comes from and avoid getting an error I encountered too many times when I first started learning SQL!

🤏 Filtering & Sorting

When working with large data sets, you'll typically want (and potentially need) to reduce the total volume of data you're requesting. The main command for filtering data is the WHERE statement. You might also want to sort the data in a particular order, which you can do with the ORDER BY statement.

• 'WHERE' is a special term in SQL that means "only give me data meeeting this condition." After writing WHERE, you write the name of the variable you want to filter along with what condition the variable values must meet to be included.
• 'ORDER BY' is another special SQL term that reorders the output of your request on some variable. After writing ORDER BY, you write the name of the variable you want to sort by, along with whether you want the data sorted in ascending (ASC) or descending (DESC) order.

SELECT content, likes_count FROM posts
WHERE likes_count > 1000
ORDER BY post_date DESC;

If you were to translate the above code into plain English, it would read "Get me the content and likes count data from the posts table, but only for posts that have more than 1000 likes, and sort the results by post date from most recent to oldest."

Note: In the example above, I sorted the data using a variable I didn't include in the SELECT statement (i.e., post_date) to illustrate that you can filter on variables even if you aren't requesting them for the output the post_date variable. This is useful when you want to filter or sort on variables that aren't necessary for your analysis.

🧮 Aggregations

You might also want to calculate summary statistics across your data, such as counts, averages, sums, minimums, and maximums. SQL has built-in functions for these common calculations, and you can use them directly in your SELECT statements to get those statistics quickly.

You can do more complex statistics, too, like comparing groups of data with the GROUP BY statement, but the examples below illustrate the basic idea. Personally, I would recommend doing more complex statistics (like regressions or sentiment modeling) outside of SQL in a statistical programming language like R or Python, but simple aggregations are fine.

SELECT COUNT(*) as total_posts,
       AVG(likes_count) as avg_likes
FROM posts;

If you were to translate the above code into plain English, it would read "Get me the total number of posts and the average likes count across all posts from the posts table."

Note: In the example above, the asterisk (*) in COUNT(*) means "count all rows." I used the COUNT() and AVG() functions to calculate the total number of posts and the average likes count, respectively.