Data analysis - Aggregate functions

Preparation:

Data Literacy Fundamentals - Kapitel 5

Aggregation

MySQL Max

MySQL Average

MySQL Count

Exercise 1: Individual - Write the query

• Q1: what is the average speed of all pokemon?

  • A: 68.9338

• Q2: How many pokemon are in the dataset?

  • A: 151

• Q3: What is the maximum primary type?

  • A: Water

• Q4: What is the minimum primary type?

  • A: Bug

• Q5: Why is water the maximum and bug the minimum? Make an informed guess

  • A: ?

• Q6: What is the max speed of all pokemon?

  • A: 140

• Q7: What is the average speed of all pokemon with 'Ground' as primary type?

  • A: 58.1250

• Q8: Display the average speed of each primary type of pokémon

Project: Taxi Analysis

Dataset

Description

• Example: When is the busiest time of the day?

  Remember: Hours can be interpreted as a nominal scale - as a category with the categories 0-24

  To extract the hour from a DATETIME data type use:

  select HOUR(tpep_pickup_datetime)
  FROM trips

• What payment type has the highest fare amount on average?

• At what time of day (by the hour) are the longest trips on average?

  • Remember: Hours can be interpreted as a nominal scale - as a category with the categories 0-24

• What are the average tip amount for each passenger count?

  • Does more passengers mean more tips?

(Advanced - optional)

• Do longer trip time mean more passengers?

  • https://sql-bits.com/group-rows-by-a-range-of-values/

• Do longer trip distance mean more passengers?

How to import .csv to MySQL

Create Database for the data

image-20230906154346980

Right click > Table Data Import Wizard

image-20230906154416037

Find File

image-20230906154443900

Rename table (otherwise the name is very long)

image-20230906154503723

Change tpep_pickup_datetime & tpep_dropoff_datetime to datetime datatype > next > next

image-20230908093104096

image-20230906154650078

???

image-20230906154705577

Profit