datawithdanny / sql-masterclass Goto Github PK

sql-masterclass's Introduction

Welcome to the SQL Masterclass Free GitHub Course!!!

You can find all of the content and slides for Danny Ma's SQL Masterclass held at the ODSC Asia Pacific 2021 virtual conference!

📚 Course Content
📊 Accessing the Data
🙏 Conclusion
😎 About the Author

👋 Introduction

This free GitHub course is sorted into multiple tutorials which were actually delivered using O'Reilly Katacoda during the live training (which is totally ah-mazing 🤩 )

To avoid any legal issues - all of the SQL live training material is available directly here on GitHub as a companion course which you can learn from at your leisure 👌

You can also see the presentation slides for the live training here!

📚 Course Content

Click the navigation badge below to get started - all of the course tutorials can be found in the /course-content folder!

CLICK ON THE BADGE BELOW TO GET STARTED!!!

📊 Accessing the Data

Although all of the code examples show the outputs directly inside the training materials for this masterclass, you can also play with the data locally or on a few different hosted services provided below!

My recommendations are to use any one of the Docker solutions to best replicate the SQLPad environment used for the actual live training session.

You can also access the free DB-Fiddle instances below or you can also access the raw data here!

Docker Solutions

This is the preferred setup to access all of the course data in the desired SQLPad environment!

This same Docker SQLPad interface is featured in the Data With Danny Serious SQL course but with a lot more data included - you should check it out! 😉

We even include instructions on how to install Docker if you haven't used it before inside the course :)

Docker Compose

If you have Docker installed on your machine with Docker-Compose, you can directly use the docker-compose.yml file included in the root of this repo to spin up the required SQLPad infrastructure.

docker-compose up

Once the initialisation is complete - you can visit localhost:3000 in your favourite browser to access the SQL interface with all the data ready to go.

Note that you may want to save all of your code in a separate text editor and copy & paste it directly into the SQLPad so you don't lose all of your code should something go wrong! 🥵

Free Play With Docker Instance

You can use this live Docker PostgreSQL environment to copy and paste SQL code from the code/ folder directly here to see the outputs generated for the session.

Click on the buttons below to launch a Play-With-Docker stack - DockerHub login/signup is required but all batteries should be included.

It's totally free to create your own DockerHub account and highly recommended too if you want to create your own Docker images in the future!

👇👇👇 Make sure you right click and open the link in a new tab if you're viewing this on GitHub!

Once the initialisation steps are complete and you see Your session is ready at the bottom of the screen - there is a chance you might need to refresh your browser or click just outside the initial popout window and press your esc key to close this window as the Close button is sometimes not working!

Next you can either hit port 3000 to use a SQLPad GUI or you can run the following to enter a psql terminal instance if you are so inclined!

docker exec -it `docker ps -aqf "expose=5432"` psql -U postgres

Local Docker Commands

If you are happy to use the psql shell instead of the SQLPad GUI because you love the terminal - you can simply run the following commands to access the data directly too. By default, port 5432 will be used for the PostgreSQL database.

Feel free to change the name argument to anything you like!

docker run -d --rm --name="psql-masterclass" dannyma/psql-crypto:latest
docker exec -it psql-masterclass psql -U postgres

DB Fiddle

You can also access the raw data directly in a live browser interface called DB Fiddle.

Click on the badge below to get access to this free service - it takes a little bit longer to run each query when compared to the Docker solutions, so please keep this in mind!

Raw Data

You can also find all of the raw data inside the /data folder

Update: There are now SQL scripts for both PostgreSQL and MySQL - however all of the code inside the course contents is for PostgreSQL only.

The majority of the code snippets should still work across SQL flavours but you may need to tweak some of the queries slightly!

Postgres/init-postgres.sql and MySQL/init-mysql.sql can be ran with your favourite SQL IDE like PgAdmin4 or MySQL Workbench directly to create the required database tables
Postgres/schema-postgres.sql and MySQL/schema-mysql.sql contain the table definitions in case you want to use them also and load in the CSV files
.csv files containing the raw data for each table if you want to import the data to another tool

Note that the two flavour init scripts are essentially the same - but MySQL has backticks instead of double quotes for table creation steps!

Thank You & Next Steps

Thank you for your taking this free SQL Masterclass GitHub course! If you've enjoyed this - please feel free to share this with your friends and leave a review! ⭐

Here are some ways you can support the author and the Data With Danny team below 🙏

Data With Danny Virtual Data Apprenticeship

If you're interested in learning valuable data science skills with Danny directly - you can checkout the Data With Danny website for more details!

Join our private student community with over 1,000 data professionals, join a local study group, get help from our team of 12+ data mentors and learn more about our personalized mentorship initiatives!

The 1st part of the Data With Danny program is Serious SQL which is like this SQL course but on an entirely differently level. If you're serious about learning SQL you won't want to miss this course!

Our first live SQL training cohort begins in November 2021 - you can gain all access to course content, recorded videos, our private Discord and more for a one off payment of only $49 - find out more here!

8 Week SQL Challenge Case Studies

Want to test your SQL skills and tackle 8 realistic SQL case studies and get access to a collection of free SQL learning resources?

Join the Data With Danny 8 Week SQL Challenge for free today!

Solve all 8 realistic SQL case studies designed to simulate real work scenarios and interview questions across multiple analytics domains including customer analytics, digital, banking, retail and subscriptions!

About the Author: Danny Ma

Danny is the Chief Data Mentor at Data With Danny and the Founder & CEO of Sydney Data Science, a boutique data consultancy based out of Sydney, Australia 🇦🇺

After spending the last 10 years working in almost every single role in the data ecosystem, Danny is now focused on solving difficult problems at scale re-imagining data education and recruitment, and mentoring the next generation of data professionals.

He provides specialist data consultancy services:

Digital customer analytics and experimentation
Data and machine learning strategy
Data engineering and systems design
Team building for analytics and data science functions
Technical training for practitioners and management

Danny is a regular speaker at global data conferences, meetups and podcasts where he shares the importance of mentorship for all data professionals. He is also a technical author and instructor for O'Reilly.

Danny believes that he is living proof that dispels the myth that you need higher level education to be successful in the data science space, and he wants to share his experiences with others so they can do the same.

sql-masterclass's People

Contributors

Stargazers

Watchers

Forkers

chiahsuy mattburnham aba0405 robiriu harshithbejjam williamtindan bhascarp sunjoshi1991 rashkam01 ayoub-berdeddouch phagoroyebabs nmangera olayinkaf cyyyp4erpnk saketh-12 gyanachand1 shreyachatterjee-blip ritikkumarjain nidhig631 sarmoho shreyas-05 anefuiii mmak3208 mehmeterkangunal yaseen221 muditnandwana ifyfrances mattcon70 vijayalakshmisivaraman vijay6568 da-vansh sfliew lalithh24 chethangr maxcodextc gabriela-machado ud-anurag nikkitha92 revanth-sridhar sgatea bounasrnour singhshanu suchayan01 kesthav domnicads anubhav-pandey1 mtahir19 bhavik-ksvss shek-attack chetnakhanna16 nooea justjooney sgpohlj87 anikait1 99701a0554 motamedw anirban6393 karthikvcp1995 clickio mes4849 ibraabukaff simran-barnwal harshasanthapur harry-data-g desfados gpozzi jeanvitola harshitasing skybe077 carlosbtech visrun chuktuk mekongdelta-mind quantumdemon1 agsigmoid khurramkhalidiqbal machein cybernetics suryatmodulus occultism ryukanjit yoleitza regulus1492 smt100 sebidelamata prachie6157 telixia shahidshakil shivakoreddi basavaraj-hadapad bijornxavier romanmaltcev mainakmaitra fjramadhn datalearns zhry600 jmiguelfernandes jasonkasuma cleebot gunjanjk

sql-masterclass's Issues

Step 4. Question 3. There are two Question 3.

There are two Question 3, asking for different questions.

Extra Brackets

For lesson 3, there is no need for an extra bracket in the last wrong code on the page.

Query on platform as at now

SELECT
  ticker,
  SUM(CASE WHEN price > open THEN 1 ELSE 0 END) / COUNT(*)) AS breakout_percentage,
  SUM(CASE WHEN price < open THEN 1 ELSE 0 END) / COUNT(*)) AS non_breakout_percentage
FROM trading.prices
WHERE market_date >= '2019-01-01' AND market_date <= '2019-12-31'
GROUP BY ticker;

Correct Query

SELECT
  ticker,
  SUM(CASE WHEN price > open THEN 1 ELSE 0 END) / COUNT(*) AS breakout_percentage,
  SUM(CASE WHEN price < open THEN 1 ELSE 0 END) / COUNT(*) AS non_breakout_percentage
FROM trading.prices
WHERE market_date >= '2019-01-01' AND market_date <= '2019-12-31'
GROUP BY ticker;

Wrong Answer for Step-2 (Question-6)

For that question, the answer should be 11. Instead, there is a list of the different countries which was the answer for the previous question. I guess it's just a small mistake however, I just want to point out for future improvement.

Thank You for this course

Filter for ETH ticker missing in step 4, Q4

Question 3,4, and 5 Errors

Question 3 and 4 are missing a WHERE clause to specify BTC/ETH.
Question 5 eth_sell_qty is actually the BTC sell qty.

Filter for ETH ticker missing in Step 3 - Daily Prices Question 6

What is the monthly average of the price column for Ethereum in 2020? Sort the output in chronological order and also round the average price value to 2 decimal places

Question 6 asks to calculate monthly average price for Ethereum in 2020, however the solution is missing a where clause for the same.

Current Solution

SELECT
  DATE_TRUNC('MON', market_date) AS month_start,
  -- need to cast approx. floats to exact numeric types for round!
  ROUND(AVG(price)::NUMERIC, 2) AS average_eth_price
FROM trading.prices
WHERE EXTRACT(YEAR FROM market_date) = 2020
GROUP BY month_start
ORDER BY month_start;

Correct Solution

SELECT
  DATE_TRUNC('MON', market_date) AS month_start,
  -- need to cast approx. floats to exact numeric types for round!
  ROUND(AVG(price)::NUMERIC, 2) AS average_eth_price
FROM trading.prices
WHERE EXTRACT(YEAR FROM market_date) = 2020 AND ticker = 'ETH'
GROUP BY month_start
ORDER BY month_start;

Sql course

Step 3. Question 6. Answer is not considering 'ETH' filter

Question specifies "average of the price column for Ethereum", but in answer code and results table the 'ETH' filter is not considered in WHERE clausule.

SELECT
DATE_TRUNC('MON', market_date) AS month_start,
-- need to cast approx. floats to exact numeric types for round!
ROUND(AVG(price)::NUMERIC, 2) AS average_eth_price
FROM trading.prices

WHERE ticker = 'ETH' 
      AND EXTRACT(YEAR FROM market_date) = 2020

GROUP BY month_start
ORDER BY month_start;

</details>
<br>
|      month_start       | average_eth_price |
| ---------------------- | ----------------- |
| 2020-01-01             |            156.65 |
| 2020-02-01             |            238.76 |
| 2020-03-01             |            160.18 |
| 2020-04-01             |            171.29 |
| 2020-05-01             |            207.45 |
| 2020-06-01             |            235.92 |
| 2020-07-01             |            259.57 |
| 2020-08-01             |            401.73 |
| 2020-09-01             |            367.77 |
| 2020-10-01             |            375.79 |
| 2020-11-01             |            486.73 |
| 2020-12-01             |            622.35 |
<br>

Step 4 Question 3 wrong answer

The question is For each year, calculate the following buy and sell metrics for Bitcoin:

The SQL answer is :
SELECT
EXTRACT(YEAR FROM txn_date) AS txn_year,
txn_type,
COUNT(*) AS transaction_count,
ROUND(SUM(quantity)::NUMERIC, 2) AS total_quantity,
ROUND(AVG(quantity)::NUMERIC, 2) AS average_quantity
FROM trading.transactions
GROUP BY txn_year, txn_type
ORDER BY txn_year, txn_type;

Missing Where ticker='BTC'

The answer table is wrong as well.

GUI is not opening

Hi @datawithdanny

I have run docker -compose up and it builds successfully. When I open localhost : 3000 , it's not working

here are some ref screenshots :-

How to fix this issue?
Thanks

Transactions table missing a column

My transactions table is missing the txn_date column, I am using the schema provided

Wrong solution for Question 6 in step4.md

The solution for Question 6 in step4.md is wrong.

Question - Summarise all buy and sell transactions for each member_id by generating 1 row for each member with the following additional columns:

Bitcoin buy quantity
Bitcoin sell quantity
Ethereum buy quantity
Ethereum sell quantity

Given Solution -

SELECT
  member_id,
  SUM(
    CASE
      WHEN ticker = 'BTC' AND txn_type = 'BUY' THEN quantity
      ELSE 0
    END
  ) AS btc_buy_qty,
  SUM(
    CASE
      WHEN ticker = 'BTC' AND txn_type = 'SELL' THEN quantity
      ELSE 0
    END
  ) AS btc_sell_qty,
  SUM(
    CASE
      WHEN ticker = 'ETH' AND txn_type = 'BUY' THEN quantity
      ELSE 0
    END
  ) AS eth_buy_qty,
  SUM(
    CASE
      WHEN ticker = 'BTC' AND txn_type = 'SELL' THEN quantity
      ELSE 0
    END
  ) AS eth_sell_qty
FROM trading.transactions
GROUP BY member_id;

Issue - In the last case, the ticker is filtered with BTC, it should be ETH.

Correct solution -

SELECT
  member_id,
  SUM(
    CASE
      WHEN ticker = 'BTC' AND txn_type = 'BUY' THEN quantity
      ELSE 0
    END
  ) AS btc_buy_qty,
  SUM(
    CASE
      WHEN ticker = 'BTC' AND txn_type = 'SELL' THEN quantity
      ELSE 0
    END
  ) AS btc_sell_qty,
  SUM(
    CASE
      WHEN ticker = 'ETH' AND txn_type = 'BUY' THEN quantity
      ELSE 0
    END
  ) AS eth_buy_qty,
  SUM(
    CASE
      WHEN ticker = 'ETH' AND txn_type = 'SELL' THEN quantity
      ELSE 0
    END
  ) AS eth_sell_qty
FROM trading.transactions
GROUP BY member_id;

Error While Loading the data set to MY SQL Workbench

Hello @datawithdanny ,
I am currently facing an issue while running SQL script on SQL workbench. The issue occurs when I am trying to load the data in the transaction table. It says there is an incorrect date-time value for id 774.

Please let me know what can be done in this case.

Recommend Projects

React

A declarative, efficient, and flexible JavaScript library for building user interfaces.
Vue.js

🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
Typescript

TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
TensorFlow

An Open Source Machine Learning Framework for Everyone
Django

The Web framework for perfectionists with deadlines.
Laravel

A PHP framework for web artisans
D3

Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

javascript

JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
web

Some thing interesting about web. New door for the world.
server

A server is a program made to process requests and deliver data to clients.
Machine learning

Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Visualization

Some thing interesting about visualization, use data art
Game

Some thing interesting about game, make everyone happy.

Recommend Org

Facebook

We are working to build community through open source technology. NB: members must have two-factor auth.
Microsoft

Open source projects and samples from Microsoft.
Google

Google ❤️ Open Source for everyone.
Alibaba

Alibaba Open Source for everyone
D3

Data-Driven Documents codes.
Tencent

China tencent open source team.