22. Data Analytics - Analyze Data to Answer Questions - Week 3

 

Definition:

Aggregation // collecting or gathering many separate pieces into a whole

Subquery // a query within another query. A nested query.

        - Outer query is the main/top query. Inner query is the sub queries under main/top queries.

        - Inner queries execute first than outer queries.

        SYNTAX:

                SELECT

                    (SELECT, write a query normally and close with parenthesis) // can place under WHERE 

                    and FROM also

                FROM

Remove duplicates // tool that automatically searches for and eliminates duplicate entries from a spreadsheet

Absolute reference // reference that is locked so that rows and columns won't change when copied

Primary Keys // reference columns in which each value is unique to that table

Foreign Keys // are primary keys in other tables

Aliasing // when you temporarily name a table or column in your query to make it easier to read and write

Data aggregation // process of gathering data from multiple sources in order to combine it into a single summarized collection.

        - Helps data analyst identify trends, make comparisons, and gain insights

        Example:

                - Puzzle pieces = data

                - Organization = aggregation

                - Pile of pieces = summary

                - Putting the pieces together = gaining insights


Common data cleaning steps before using VLOOKUP:

        - Check for formatting issues

        - Check for whitespaces

        - Check for duplicates

Using VLOOKUP in Spreadsheet to aggregate data.

Using JOINS in SQL to aggregate data.


Troubleshooting VLOOPUP Errors:

        - How should I prioritize these issues?

        - In a single sentence, what's the issue I'm facing?

        - What resource can help me solve the problem?

        - How can I stop this problem from happening in the future?

        - Vlookup cant search to the left.

        - Vlookup only searches once and returns, so it does not check further for more matches.

        * TRUE tells VLOOKUP to look for approximate matches

        * FALSE tells VLOOKUP to look for exact matches

Spreadsheet Functions:

        - VLOOKUP (VERTICAL LOOKUP) // function that searches for a certain value in a column to

            return a corresponding piece of information

        - VALUE // function that converts a text string that represents a number to a numerical value

        - TRIM // deletes any extra spaces added to a cell

        - MATCH // function used to locate position of a specific lookup value

        - IFNA // if something, replace with something

        - COUNT // used to count the total number of numerical values within a specific range in 

                            spreadsheets

SQL Clause/Functions:

        - HAVING // allows you to add a filter to your query instead of the underlying table that can

                                only be used with aggregate functions. SYNTAX is like WHERE, but place botm.

        - CASE // returns records with your conditions by allowing you to include if/then statements

                                in your query

        - ALIASES // used in SQL queries to create temporary names for columns or tables. Use AS

                SYNTAX: columnA AS newcolumnA // can omit the AS

        - COUNT // a query that returns the number of rows in a specified range

        - COUNT DISTINCT // query that only returns the distinct values in a specified range

                - Doesn't count repeating values

        - JOIN // used to combine rows from two or more tables based on a related column by comparing

                        primary and foreign keys.

                SYNTAX:

                        Place LEFT JOIN under FROM.

                        LEFT JOIN

                                seconddatasetname ON

                                firsttableKEY = secondtableKEY

                Common Joins:

                        * Table mentioned first is left table, table mentioned second is right.

                        - Inner // returns records with matching values in both tables. Records only merge if

                                        both are matching in both tables. THIS IS DEFAULT "JOIN" IN SQL

                        - Left // returns all records from the left table and only the matching records from the

                                        right table

                        - Right // returns all records from the right table and only the matching records from

                                        the left table

                        - Outer // combines right and left join to return all matching records in both tables.

                                        If one table has no match for columns, the other table creates a column with

                                        no values. SYNTAX = FULL OUTER

                        



Data can also be aggregated over a given time period to provide statistics such as:

        - Averages, Minimums, Maximums, Sums


Additional Resources:

https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1

https://exceljet.net/things-you-should-know-about-vlookup

https://www.w3schools.com/sql/sql_alias.asp

https://www.sqltutorial.org/sql-alias/

https://www.w3schools.com/sql/sql_join.asp

https://www.essentialsql.com/introduction-database-joins/

https://dataschool.com/how-to-teach-people-sql/sql-join-types-explained-visually/

https://towardsdatascience.com/sql-join-8212e3eb9fde

https://www.dofactory.com/sql/join

https://www.w3resource.com/sql/subqueries/understanding-sql-subqueries.php

https://mode.com/sql-tutorial/sql-sub-queries/

https://www.w3schools.com/sql/func_mysql_if.asp

http://www-db.deis.unibo.it/courses/TW/DOCS/w3schools/sql/sql_func_count.asp.html

https://www.w3schools.com/sql/sql_case.asp

http://www-db.deis.unibo.it/courses/TW/DOCS/w3schools/sql/sql_having.asp.html

Comments

Post a Comment

Popular posts from this blog

2. FreeCodeCamp - Dynamic Programming - Learn to Solve Algorithmic Problems & Coding Challenges

Dynamic Programming - Parts      PART 1 - Memoization     P ART 2 - Tabulation Dynamic Programming with Fibonacci Example Recursion: Slow because time complexity is O(2^n) func fibR (x int ) int {     if x <= 2 {         return 1     }     return fibR (x- 1 ) + fibR (x- 2 ) } Dynamic Way Example Recursion with Stored Value:  // Fast because it removes duplicate processes by checking map. // Tail recursion-ish // Results are hitchhiking each recursive call func fibR2 (x int , y map [ int ] int ) int {     if _ , ok := y[x]; ok {         return y[x]     }     if x <= 2 {         return 1     }     y[x] = fibR2 (x- 1 , y) + fibR2 (x- 2 , y)     return y[x] }
Read more

20. Data Analytics - Analyze Data to Answer Questions - Week 1

Goal of analysis is to identify trends and relationships within data so you can accurately answer the questions you're asking. Most data is organized in tables, always have data in the right format. Definition : Analysis // process used to make sense of the data collected Sorting // involves arranging data into a meaningful order to make it easier to understand, analyze, visualize Outliers // data points that are very different from similarly collected data and might not be reliable values Filtering // extracting specific data that meet certain criteria while hiding the rest Customized sort order // when you sort data in a spreadsheet using multiple conditions The 4 phases of analysis:          1. Organize data // getting the right data in the right size and order                     - Sorting : arrange data in meaningful order                     - Filtering : can help find errors or outliers          2. Format and adjust data // filtering or sorting or compiling data in a better
Read more

3. Algorithms - Selection Sort

Selection sort sorts an array by finding the lowest value element and appending it to an empty array. The algorithm will shrink the unsorted array and repeat the process recursively until all unsorted elements are used up. package main func selectionSort (list *[] int ) {     sorted := make ([] int , 0 , len (*list))     unSorted := *list     for i := 0 ; i < len (*list); i++ {         min := unSorted[ 0 ]         index := 0         for i , e := range unSorted {             if e <= min {                 min = e                 index = i             }         }         sorted = append (sorted, (unSorted)[index])         if index == 0 {             unSorted = unSorted[ 1 :]         } else if index == ( len (*list) - 1 ) {             unSorted = unSorted[:index]         } else {             unSorted = append (unSorted[ 0 :index], unSorted[index+ 1 :]...)         }     }     * list = sorted } /*func main() {     x := []int{4, 6, 3, 2, 9, 7, 3, 5}     fmt.Print
Read more