1

Project Overview

3

Create Metrics Charts

4

Publish your dashboard

๐Ÿ…

Getting started with SQL


Before we proceed further, let's go over SQL basics. If you are familiar, you can skip ahead to the next section.

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.
Databases understand SQL

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.

Project example

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 expenses table.

SQL Query 1

SQL playground

SQL Query 1 Results

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 SUM, 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 amount column.

However, this is a bit tricky because the week is not directly available, only the date is.

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

That is all the SQL knowledge you will require to complete this project.

Please feel free to experiment with SQLs using the playground (link available under the above images).

๐Ÿ’ก Quiz time

Which one of the following allows you to find the total amount spent at every merchant?

Hint: group and then apply aggregate functions.