📊 Intermediate SQL for Data Analytics - Full Course

Data Nerds! This repo contains all the files needed to follow along my free course: Intermediate SQL for Data Analytics

Team Members 👥

🙋🏼‍♂️ Course Leader: Luke Barousse
🎬 Course Producer: Kelly Adams
📺 Video Editor: Brannon Linder

Table of Contents

0. Intro

• About Course - (Open in Colab)
• Colab Notebooks - (Open in Colab)
• Database Overview - (Open in Colab)

1. Pivot With Case Statements

• Basic Aggregation - (Open in Colab)
• Statistical Aggregations - (Open in Colab)
• Advanced Segmentation - (Open in Colab)

2. Date Time

• Date Format - (Open in Colab)
• Date Filtering - (Open in Colab)
• Date Differences - (Open in Colab)

3. Windows Functions

• Syntax - (Open in Colab)
• Aggregation - (Open in Colab)
• Ranking - (Open in Colab)
• Lag Lead - (Open in Colab)
• Frame Clause - (Open in Colab)

4. Local DB Setup

• Install PostgreSQL - (Open in Colab)
• Install DBeaver - (Open in Colab)

5. Views

• View Intro - (Open in Colab)
• Project Cohort Revenue - (Open in Colab)
• Install VSCode - (Open in Colab)

6. Data Cleaning

• Conditional Handle Nulls - (Open in Colab)
• String Formatting - (Open in Colab)
• Project Customer Segmentation - (Open in Colab)

7. Query Optimization

• Explain Intro - (Open in Colab)
• Optimization Techniques - (Open in Colab)
• Project Customer Retention - (Open in Colab)

How to Run SQL Files

Method 1️⃣: Run in Google Colab

Recommended to start the course.

Prerequisites:

• Google Account

Steps:

1. Click the "Open in Colab" button at top of any notebook.

2. Run all cells in the notebook.

Troubleshooting:

• No module named 'psycopg' error? Your copy of the notebook is out of date (copies saved to Google Drive don't update). In the setup cell, change %sql postgresql:// to %sql postgresql+psycopg2://, then click Runtime > Restart session and run all. Or re-open the notebook with its "Open in Colab" button.

---

Method 2️⃣: Run Database Locally w/ PGAdmin

Second half of course uses this method.

Prerequisites:

• PostgreSQL Installed
• pgAdmin Installed

Steps:

1. Download the Contoso database.
2. Open pgAdmin 4.
3. In Object Explorer, connect to your PostgreSQL server.
4. Right-click on "Databases" > "Create" > "Database...".

5. Enter contoso_100kfor "Database" and click "Save".
6. In Object Explorer, right-click on the contoso_100k database > "PSQL Tool".
7. In the PSQL Tools Window, enter \i [path to contoso_100k.sql] and press enter.

\i '/Users/lukebarousse/Desktop/contoso_100k.sql'

8. If necessary, in the Query Tool, set default password for the postgres user to password.
   ⚠ If you have sensitive information in your server DO NOT do this step; also not required if this is already your password ⚠️

ALTER USER postgres WITH PASSWORD 'password';

---

Method 3️⃣: Run SQL Locally in Jupyter Notebook

How Kelly & I built the course; not recommended for beginners.

Prerequisites:

• PostgreSQL Installed
• Anaconda Installed
• Database Running Locally

Steps:

1. Create a new conda environment with ipykernel, pandas, and matplotlib:

conda create -n sql_course python=3.11 ipykernel pandas matplotlib

2. Activate the environment:

conda activate sql_course

3. Install the jupysql and psycopg2 packages:

conda install -c conda-forge jupysql psycopg2

4. Upgrade jupysql, necessary due to this issue:

pip install --upgrade jupysql

5. Activate the sql_course environment in the notebook.
6. Connect to the database with the psycopg2 driver named in the URL (the setup cell of each notebook already does this). Without +psycopg2, SQLAlchemy 2.1+ looks for the psycopg driver and fails with No module named 'psycopg'.

%sql postgresql+psycopg2://postgres:password@localhost:5432/contoso_100k

Found a Typo? Want to Contribute?

• If you find an error in this repo, please feel free to make a pull request by:
  • Forking the repo
  • Making any changes
  • Submitting a pull request