SQL and Data Visualization - The Complete Bootcamp

Set up your data workflow with Heroku as the server, PgAdmin for database storage and inserting data, and Metabase for interactive dashboards and SQL queries.

Deploy Metabase to Heroku by creating a free account, naming the app, selecting a region, and clicking deploy. View credentials for the Postgres SQL database and prepare to install pgAdmin.

Install pgadmin, the PostgreSQL tool, by downloading the latest macOS or Windows installer from pgadmin.org, run the installer, and set up PostgreSQL 12 via stack builder.

Install Heroku plugins and the CLI across macOS, Windows, and Ubuntu, log in to Heroku, install Postgres extras, and set up Metabase to connect your database and create tables.

Create and connect your first database server in pgAdmin using your Heroku PostgreSQL credentials—host, port 5432, maintenance database, username, and password—then use the advanced tab to set the database restriction.

Explore PostgreSQL table structures by examining customers, addresses, transactions, products, and transaction statuses, and learn how these tables relate through IDs and creation timestamps, with a schema preview.

Explore how table relationships connect customers, addresses, transactions, products, and transaction statuses, using primary and foreign keys to join datasets for business intelligence and data visualization.

Create and populate the customers table in pgadmin by importing customers.csv, defining id, first_name, last_name, email, created_at, country, and gender, then verify with a select query.

Finish building your database by implementing the remaining tables—addresses, transactions, products, and transaction status—and import csv data to populate them for querying and visualization.

Install Metabase, the data visualization tool, by downloading from Metabase start; sign in and drag the app into applications to connect your database and visualize data.

Sign into your PostgreSQL database and add or rename the database, then connect via the admin panel. Run a native query like select star from customers to learn SQL.

Discover the basics of the select command in SQL, fetching all data or specific columns from a table like customers, with flexible column order and case-insensitive syntax.

Explore how to use the as syntax to rename columns and tables with aliases in SQL, including client_id and client_email. Rename the customers table as c for concise joins.

Apply the where clause after the from to filter rows in SQL. Filter by gender, country, and first name to retrieve specific customers from the customers table.

Sort your data with the order by clause in SQL, choosing ascending or descending order by a column such as created date or country, for integers, text, or dates.

Learn to use the limit clause to cap result rows, optimize heavy queries, and debug by testing with fixed row counts in SQL, with examples from customers and transactions.

Identify that customers can have multiple transactions and use select distinct to deduplicate by customer id, returning unique customer ids and preventing duplicates.

Master the count function in SQL by counting all rows with count(*), counting a specific column, and using distinct to count unique values, producing a single aggregated result.

Learn to use the in operator in the where clause to filter by a set of values, such as IDs in (1,3). Filter addresses by states in ('OH','PA','FL').

Master SQL filtering with like, not like, and ilike to match text patterns using % wildcards, case sensitivity, and exclusion, demonstrated with last name examples and pattern clustering.

Learn how boolean values work in SQL, returning true, false, or null, with practical examples comparing last names and transaction amounts to illustrate true or false results.

Master the group by clause to divide rows into groups and apply aggregate functions, such as counting transactions per customer or customers per state, using group by one.

Create your first data visualization by querying the transactions table, counting transactions per status code, and rendering a pie chart in Metabase to share with stakeholders.

Learn to use SQL comparison operators in where clauses, including equal, not equal, is null, in, between, like, exists, with practical coding examples.

Explore greater than and less than operators in SQL, filtering transactions by amount and created at dates using strict inequalities and single-quote date literals.

Count number of transactions per client using count and group by customer id, then reuse result as a new table to apply greater or equal and less or equal filters.

Learn to use equal and not equal operators in SQL, build higher level queries on the transactions table, and apply booleans, numbers, and dates in filters.

Learn how to use the between operator in sql to filter transactions by amount or dates, including inclusive bounds within where clauses.

Master the distinct from operator to compare values with nulls in SQL, using an example that selects addresses not in Springfield.

Create your first data visualization dashboard in Metabase. Build a daily transactions bar chart on the gross KPI dashboard, using native queries and simple grouping.

Create an automated graph in Metabase using SQL to show transactions per product with a pie chart. Attach it to a dashboard to monitor KPIs and highlight best products.

Learn to build and apply Metabase filters, using where clauses, two square brackets, and curly braces, to filter by product ID and update graphs and dashboards.

Learn to use aggregate functions in SQL, such as mean and max, to obtain the first and last transaction dates per customer by grouping by customer ID.

Master the sum aggregate function, compare it to count, compute per-client totals with group by, visualize top five customers by dollar spent, and apply product ID filters.

Apply case when conditional expressions in SQL to categorize data, count totals, and group by status, translating status codes into text for dashboards and KPI visuals.

Learn to use the AVG function to compute the average transaction value per customer, group and order results, and visualize daily averages in a KPI dashboard.

Learn to compute median and percentiles for daily transactions with a native query, including quartiles, grouped by day, and visualize them in a line chart with product id filters.

Discover two methods to filter within aggregate functions: use case when inside the aggregate, or use count(*) with filter where.

Master date_trunc in SQL to truncate timestamps by week, month, or year, enabling grouping and counting of transactions or customers for visualizations and dashboards.

Learn to group sql results by date parts with date_trunc and date_part, counting customers per week or month. Compare the two syntaxes and sort by the period numbers.

Learn to create a full name by concatenating first and last names with a space, and calculate the date difference in days between now and created_at using date_part in SQL.

Learn to convert dates and numbers to text with the SQL to_char function, format dates as month names or day names, and generate readable strings for counting customers per month.

Learn how to join two tables in SQL by using a common column, and explore left, inner, and right joins with practical examples linking customers and transactions.

Learn how to use a left join to combine customers with their transactions, ensuring all customers appear even if they have no transactions, and prefix columns to avoid errors.

Compare inner join and left join to show how inner join returns only customers with a transaction, removing those without, and explain why inner join simplifies filtering.

Explore common table expressions (CTE) using the with keyword to build reusable, debuggable subqueries like time series and customer creation, simplifying complex SQL and enabling clean, modular queries.

Create two CTEs to simplify queries, join customer and transaction information, and include status text; then compute weekly transactions with case expressions for male and completed.

Learn to compute a daily and cumulative sum of transactions using a CTE, window function over order by day, and visualize it in a dashboard with product filters.

Explore generate_series, a set returning function to generate sample data, creating numeric sequences and date ranges from a start date to an end date with monthly increments.

Use generate_series to create a month-by-month time series and join it with customer creation data to build a sample cohort query. Employ count with case when and group by.

Learn to build a monthly cohort analysis in SQL by comparing acquisition and activation dates, using generate_series, CTE workflows, and a pivoted cohort view to measure activation rate across months.

Master cohort analysis in sql by counting clients per state from the addresses table and visualizing the results on a region or world map for a kpi dashboard.