Getting started with SQL
Most of us organize our data using spreadsheets. It works well in most simple cases, but not in complex systems. Many organizations rely on relational databases to store and retrieve large amounts of data.
A relational database stores data in the form of tables. Tables organizes information into rows and columns. Tables can have relationships with one another.
If you have used a spreadsheet, you can assume that
- spreadsheet ~ database.
- tabs ~ tables.
SQL is a language to communicate with database.
We can express our requirements in SQL, and the database engine will retrieve the data in the requested format.
Consider a simple tracker that records all expenses. Assume that a relational database is used to store the data.
Sample data for the below exercise.
Let us write some simple SQL queries to analyze the data.
Query 1 : Basic Syntax
Now on to the basic syntax to retrieve all the data from the
Results from the database based on the above SQL
Query 2 : WHERE clause
Here's how to add a condition to filter only required rows.
Query 3 : AND statement
Suppose that we are interested only in coffee expenses. Since there is no
coffee column, we can look for rows where the food expense is less than $5(assuming it's coffee).
Query 4 : DISTINCT statement
If we want to list out coffee shops the user has been to, we can use
DISTINCT to remove duplicates and show unique ones.
Query 5 : GROUP BY clause
If we need to aggregate data based on specific columns (for example, CoffeeShop), we can use
GROUP BY to group the data into different buckets and then apply aggregate functions (such as
COUNT) to those buckets.
Query 5 : DATE_TRUNC function
Let's say we want to look at weekly expenses. We can group by
week and sum up the
However, this is a bit tricky because the
week is not directly available, only the
DATE_TRUNC is a function that accepts dates as input and outputs them in the specified format, such as week, month, etc.
DATE_TRUNC('week', DATE '2022-05-10') → 2022-05-09
DATE_TRUNC('week', DATE '2022-05-12') → 2022-05-09
Please feel free to experiment with SQLs using the playground (link available under the above images).
If you're interested in learning more about blockchain analytics, sign up to receive notifications about upcoming articles and projects.