Please enable JavaScript.

Coggle requires JavaScript to display documents.

SQL, WITH common Table Express, CTE 通用表表达式在多次执行子查询时特别有用，允许我们在查询中通过它的名称（…

- - - - Error
        SELECT count(*)
        FROM evanston311
        WHERE date_created = '2018-01-02';
        returns 0, even though there were 49 requests on January 2, 2018.
        This is because dates are automatically converted to timestamps when compared to a timestamp. The time fields are all set to zero:
        SHOULD IT BE
        SELECT '2018-01-02'::timestamp;
        Where cast (date_created AS date)= '2017-01-31'
        Where date_created >= '2016-02-29'
        AND date_created < '2016-03-01';
        Add 100 days to the current timestamp.>>
        SELECT now() + '100 days' :: interval;
      - THREE types
        Date:YYYY-MM-DD
        Timestamp: YYYY-MM:DD HH:MM:SS
        Intervals(time duration) 6days 01:48:08
        ISO: International Organization for Standards
        YMD-HMS
        UTC: Coordinated Universal Time
        YMD-HMS+2
      - Practice
        Compare with "> < =" e.g. Select '2017-01-01'>'2016-01-01'
        Substract now- '2018-01-01' (return interval)
        NOW() current timestamp
        ISO 8601 format (small-large padded with 0
        SELECT now() > '2017-12-31';
        SELECT '2010-01-01':: date +1 >> 2010-01-02
        SELECT '2018-12-10':: date + '1 year' :: interval;
        
        2019-12-10 00:00:00
        SELECT '2018-12-10' + '1 year 2 days 3 minutes'::interval; >> 2019-12-12 00:03:00
    - - Fields
        century:2019-01-01=century 21
        decade:2019-01-01=decade 201
        year, month, day, hour, min, sec
        week
        dow(day of week) starting from Sun(0)-Sat(6)
        PRACTICE
        DATE_PART('century', TIMESTAMP '2017-01-01')
        DATE_PART('field', timestamp)
        EXTRACT(FIELD FROM timestamp)
        SELECT date_part('month', now())
        EXTRACT(MONTH FROM now())
        AVG(EXTRACT(epoch FROM date - date_created)) AS avg_completion
        DATE_TRUNC('field', timestamp) 'month',now()
        2018-12-01 00:00:00-06
        TO_CHAR(date_created, 'day') >>get the name of the day of the week by converting a timestamp to character data
        EXTRACT(DOW FROM date_created)
        
        Aggregating with date/time series
        SELECT GENERATE_SERIES('2018-01-01', '2018-01-01', '2 days':: interval)
        --per month/ ('2018-02-01','2019-01-01', '1 month'::interval) - '1 day':: interval;
        -- Create the series as a table called hour_series
        WITH hour_series AS (
        SELECT generate_series('2018-04-23 09:00:00',
        '2018-04-23 14:00:00', '1 hour'::interval) AS hours
        -- Hours from series, count date (NOT *) to count non-NULL
        SELECT hours, count(date)
        FROM hour_series
        LEFT JOIN sales
        ON hours=DATE_TRUNC('hour', date)
        GROUP BY hours
        ORDER BY hours;
        Aggregation with bins
        WITH bins AS (
        SELECT generate_series('2018-04-23 09:00:00', ‘2018-04-23 15:00:00’， ‘3 hours‘ :: interval) AS lower,
        generate_series('2018-04-23 12:00:00', '2018-04-23 18:00:00', '3 hours':: interval) AS upper)
        SELECT lower, upper, count(date)
        FROM bins
        LEFT join sales
        ON date>= lower AND date< upper
        GROUP BY lower, upper
        ORDER BY lower
        -- percentile_disc(0.5) WITHIN GROUP(ORDER BY__)
        avg(coalesce(count,0))
        WINDOW FUNCTION
        SELECT date,
        lag(date)OVER (ORDER BY date), lead(date)ORDER BY date)
        FROM sales,
        SELECT avg(gap)
        FROM (SELECT dale-lag(date)OVER(ORDER BY date) AS gap
        FROM sales) AS gaps;
        CORR(avg_completion, count)
  - - - Retrieving data from a table
        
        Functions
        Count(*) count(DISTINCT country)
        Count(rating)
        SELECT DISTINCT country (NULL included as well)
        
        GROUP BY
        SELECT genre, AVG(renting_price) AS avg_price,
        COUNT() AS number_movies
        FROM movies_selected
        GROUP BY genre (计数genre组里的元素数)
        HAVING COUNT()>2;
        HAVING VS WHERE
        聚合语句(sum, min, max, avg, count)要比having子句优先执行，且having后可以使用聚合函数，
        而where子句在查询过程中优先级别优于聚合语句，且不可与他们连用
        
        Operators in WHERE
        
        Comparison operators比较运算符
        Where genre <> 'Drama';
        
        BETWEEN
        WHERE date_account_start BETWEEN '2018-01-01' AND '2018-09-31' >>[,]
        (SIngle quotes normally)
        
        3.IN (compare with multiple values)
        WHERE nationality IN ('USA', 'China')
        
        IS NULL/IS NOT NULL
        WHERE rating IS (not) NULL
        
        Boolean operators AND, OR,
        WHERE country ='Italy'
        AND ... BETWEEN '2018-01-01' AND ' '
        
        ORDER BY (DESC)
      - UNION & JOIN
        Vertical & Horizontal
        
        Inner Join 两表同时存在的数据，没有多余行
        SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P. sale_price
        FROM shopProduct AS SP
        INNER JOIN Product AS P
        ON SO.product_id=P.product_id; (联结键） [ FROM>ON>WHERE]
        WHERE SP.shop_id = '000A';
        Self Join
        
        Outer join
        
        LEFT JOIN 左表为主表
        Keep all rows of the left table, match with rows in the right table
        Use identifier to define which rows of two tables can be matches
        
        Table name (FROM customers AS c)
        SELECT *
        FROM renting_selected AS r
        LEFT JOIN customers_selected AS c
        ON r.customer_id =c.customer_id
        LEFT JOIN customers AS c
        ON r.customer_id = c.customer_id
        
        RIGHT JOIN 右表为主表
        
        Union 加法 项目必须一样
        *UNION ALL 保留重复行
        INTERSECT 交集*
        
        SELECT product_id, product_name
        FROM Product
        WHERE product type ='1'
        UNION
        SELECT product_id, product_name
        FROM Product2;
        WHERE product type ='1'
        ORDER BY product_id;
        
        EXCEPT 减法(从1减去2 位置有差)
        SELECT product_id, product_name
        FROM Product
        EXCEPT
        SELECT product_id, product_name
        FROM product2
        ORDER BY product_id;
      - Subquery
        强制有运算
        
        TO START WITH
        1.SELECT *
        FROM actors
        WHERE gender = 'female'
        SELECT af.nationality, MIN(af.year_of_birth), MAX(af.year_of_birth) FROM (SELECT * FROM actors WHERE gender = 'female') AS af GROUP BY af.nationality SELECT rm.customer_id, SUM(rm.renting_price) FROM (SELECT r.customer_id, m.renting_price FROM renting AS r LEFT JOIN movies AS m ON r.movie_id=m.movie_id) AS rm GROUP BY rm.customer_id
        Combining SQL statement in one query
        LEFT JOIN WHERE c.gender ='male' GROUP BY a.name (HAVING AVG(r.rating) IS NOT NULL ORDER BY avg_rating DESC, number_views DESC;)
      - Nested query
        SELECT chunk in WHERE and HAVING clause
        SELECT name
        FROM customers
        WHERE customer_id IN
        (SELECT DISTINCT customer_id
        FROM renting
        WHERE rating <= 3);
        
        Correlated nested queries
        Condition in the WHERE clause of the inner query
        References some column of a table in the outer query
        Looping
        SELECT * FROM movies as m WHER 5< (SELECT count(*) FROM renting as r WHERE r.movie_id=m.movie_id);
        EXSITS()>> return boolean vale, TRUE or FALSE
        TRUE = not empty> row of the outer query is selected
        FALSE = empty
        匹配 select *
        SELECT * FROM movies AS m WHERE EXISTS (SELECT * FROM renting AS a WHERE rating IS NOT NULL AND r.movie_id =m.movie_id); SELECT* FROM movies as m WHERE NOT EXISTS (SELECT * FROM renting AS r WHERE rating IS NOT NULL AND r.movie_id = m.movie_id);
      - OLAP: CUBE operator 显示所选列中值的所有组合的聚合
        ABC, A, AB, AC, BC, A, B, C
        AB, A,B
        Business intelligence
        On-line analytical processing>> interactively analyze the data and to summarize and visualize it.
        -Aggregate data for a better overview
        Count number of rentings for each customer
        Average rating of movies for each genre and each country
        -Produce pivot tables to present aggregation results
        SELECT country, genre, count(*) FROM renting_extended GROUP BY CUBE (country, genre)
        ROLLUP 显示所选中列中值的某一层次结构的聚合
        ABC, AB, A,
        SELECT country, genre, count(*) FROM renting_extended GROUP BY ROLLUP(country, genre);
        --Levels of aggregation
        Aggregation of each combination of country and genre
        Aggregation of country code
        Total aggregation
        --Include more than 1 aggregation
        SELECT country, genry, count(*) AS n_rentals, count(rating) AS n_ratings FROM renting_extended GROUP BY ROLLUP( genre,country)
        GROUP BY GROUPING SETs
        SELECT country, genre, count(*)
        FROM rentings_extended
        GROUP BY GROUPING SETS ((country,genre), (country),(genre),());
        Columns names surrounded by parentheses represent one level of aggregation.
        returns a UNION over several GROUP BY queries
  - - - Transforming data
        String
        UPPER(city) AS upper_city &LOWER ()
        Numerics Operators: +-*/
        replacement_cost +2 AS updated_cost
        Dates and time
        EXTRACT(<part> FROM <date_columnn>)
        YEAR/HOUR/DAY/DOW
        https://www.w3schools.com/sql/func_mysql_extract.asp
        Aggregate function
        Numeric- AVG-COUNT-CUM
        String STRING_AGG(<column>, 'separator')
        SELECT rating, STRING_AGG(title, ',') as films
    - - Find the tight table
        columns, content
        SELECT*
        FROM payment
        LIMIT 5;
        Tables
        PostgreSQL
        SELECT
        *FROM pg_catalog.pg_tables
        WHERE schema_name = 'public';
        SQL server - TSQL
        FROM INFORMATION_SCHEMA.TABLES;
        MySQL
        SHOW TABLES;
        Join the correct tables
        1. All tables&columns
        PG/ SQL Server/ MySQL
        SELECT* FROM information_schema.columns
        WHERE table_schema = 'public';
        
        Aggregate the columns
        CREATE VIEW name_of_view AS SELECT table_name, STRING_AGG(colume_name, ',') AS columns; FROM information_schema .columns WHERE table_schema = 'public' GROUP BY table_name; SELECT * from name_of_view;
        
        VIEW>> virtual table
    - - Store your data
        
        Create a table using new data
        (1) CREATE TABLE zip_distance(postal_code INT, distance FLOAT);
        (2) INSERT INTO zip_distance(postal_code,distance) VALUES
        (53182, 3.4),
        (15540, 10.2),
        (67912,1.9);
        
        <2. Create a table using existing data
        CREATE TABLE family_films AS
        SELECT film_id, title
        FROM film
        WHERE rating = 'G';
        3.Create a VIEW using existing data
        CREATE VIEW family_films AS
        SELECT film_id, title
        FROM film
        WHERE rating = 'G';
        TABLE VIEW Data is stored(static) Query is stored(dynamic) D can be modified directly Underlying data must be modified in original tables
      - UPDATE table_name
        SET column1 =value1, columna 2=...
        UPDATE customer
        SET email = LOWER(email)
        WHERE active = TRUE;
        WHERE address_id IN
        (SELECT address_id
        FROM address
        WHERE city = 'Woodrudge');
        !!! BE CAREFUL WHEN MODIFYING TABLES
        'with great power comes great responsibility'
        Making modifications to tables can impact both users and downstream processes that rely on these tables
        *Ensure you CAN modify the table
        Ensure you know how this table is used and how your changes will impact those who use it.
        Test a modification by using a SELECT statement first.
      - Delete data
        Remove a table >> DROP TABLE table_name
        Clear table of all records>> TRUNCATE TABLE table_name
        Clear table of some records>> DELETE FROM table_name WHERE condition(without WHERE condition remove all
        DELETE FROM customer
        WHERE address_id IN
        (SELECT address_id
        FROM address
        WHERE city = 'Woodridge')
        WHERE rating IN ('R', 'NC-17')
    - - CONVEY THE INTENT
        Always use AS
        What kind of join
        Good use of aliases
        Use comments / **/ multi-line --single-line
        Which of these approaches can you leverage to clarify the intent of your SQL script?
      - WRITE READABLE CODE
        Capitalize commands and leave the rest of your code lower case
        Use new lines& indentation
        Use snake_case expensivelongtitle >> expensive_long-title
        Use IN instead of many OR statements
        Use BETWEEN when possible
      - AVOID COMMON MISTAKES
        Don't misuse comments
        Leave old comments in finished code
        Make comments redundant with code
        Don't SELECT everything
        Don't use SQL for programming
  - - - KPIs
        Registrations KPI counts of reg over time
        
        good at measuring a company's success inattracting new users
        Active users DAU daily active users/ MAU monthly
        month-on-month (MoM) MAU
        counts the active users over time
        Stickiness(DAU/MAU), measures how oftern users engage with an app on average
        Growth
        Retention
        
        Benefits
        Measure performance well in B2Cs
        Used by investors to assess pre-revenue and -profit startups
        Window functions>>Perform an operation across a set of rows related to the current row
        Running total A cumulative sum of a variable's previous values
        With reg_dates AS(
        ......
        ),
        registration AS(
        ...)
        SELECT delivr_month,
        regs, SUM(regs) OVER (ORDER BY devr_month) AS ...
        FROM registration
        ORDER BY delivr_month ASC
        LIMIT 3;
        //WITH
        SELECT delivr_month,
        mau,
        COALESCE (LAG(mau) OVER (ORDER BY delivrmonth ASC), 1 AS last,au
        
        窗口函数
        Working with aggregate values
        Requires you to use GROUP BY with ALL non-aggregate columns
        
        Perform calculations on an already generated result set (a window)
        
        Aggregate calculations
        Similar to subqueries in SELECT
        Running totals, rankings, moving averages
        !!Processed after every part of query except ORDER BY
        !! Uses information in result set rather than data base
        !! Available in Pg, Oracle, MySQL, SQL Server
        
        SELECT
        country_id,
        season,
        date,AVG(home_goal) AS avg_home
        FROM match
        GROUP BY country_id;
        !! ERROR: column "match.season" must appear in the GROUP BY clause or be used in an aggregate function
        you have to group results when using aggregate functions. If you try to retrieve additional information without grouping by every single non-aggregate value, your query will return an error. Thus, you can't compare aggregate values to non-aggregate data.
        AVG(home_goal + away_goal) OVER() AS overll_avg
        
        Generate a RANK
        SELECT
        date,
        (home_goal + away_goal) AS goals,
        RANK() OVER (ORDER BY home_goal + away_goal) AS goals_rank
        
        聚合（Aggregate）：AVG(), COUNT(), MIN(), MAX(), SUM()...
        
        取值（Value）：FIRST_VALUE(), LAST_VALUE(), LEAD(), LAG()...
        
        排序（Ranking）：RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()...
      - Growth(shrinking or growing)
        
        Delta - Difference between two values
        &Growth rate
        Deltas-pitfalls (decreasing<0)
        
        Growth rate: A percentage that show the change in a variable over time relative to variable's value
        =(current value- Previous value)/ Previous value
        
        ROUND ((mau- last_mau) :: numeric/ last_mau, 2) [Numerator to numeric)
      - Retention rate
        MAU-pitfalls> Doesn't show the breakdown of active users by tenure/ Doesn't distinguish between different patterns of user activity
        
        A percentage measuring how many users who were active in a previous month are still active in the current month = Uc (distinct users active in both current and previous months)/Up(distinct users who were active in the previous period)
        WIth....AS(
        ....
        )
        SELECT
        previous.delivr_month,
        ROUND(COUNT(DISTINCT current.user_id):: NUMERIC/ GREATEST(COUNT(DISTINCT previous.user_id),1), 2) AS retention
        FROM...
        LEFT JOIN
        ON
        AND previous.delivr_month = (current.delivr_month - INTERNAL '1 month')
        
        MAU breakdown
        -New users joined this month
        -Retained users were active in the previous month, and stayed active this month
        -Resurrected users weren't active in the previous month, but returned to activity this month
    - - Histograms
        Visualizes the frequencies of each value in a data set
        Frecuency table
        WITH user_orders AS (
        SELECT
        user_id,
        COUNT( DISTINCT order_id) AS orders
        FROM meals
        JOIN orders ON id= id
        GROUP BY user_id)
        SELECT orders, COUNT( DISTINCT user_id) AS users
        FROM user_orders
        GROUP BY orders
        ORDER BY orders ASC;
        WITH user_revenues AS (
        SELECT
        user_id,
        SUM(meal_price* order_quantity) AS revenue
        FROM meals
        JOIN orders ON id= id
        GROUP BY userid)
        SELECT
        ROUND (revenue :: NUMERIC, -2) AS revenue!00,
        COUNT( DISTINCT user_id) AS users
        FROM user_revenues
        GROUP BY revenue_100
        ORDER BY revenue_100 ASC;
        Plotting histograms- Excel/ Google Sheets/matpltlib/ggplots
        
        U-shaped histo
        Many users who generate low and high levels of revenue; few who generate median level of revenue
        
        Normal histo
        Many uses who generate median level; few who...
        
        Bucketing
        CASE
        SELECT
        CASE
        WHEN meal_price < 4 THEN 'low-price meal'
        WHEN meal_price < 6 THEN ' mid-price meal'
        ELSE 'High-price meal'
        END AS price_category,
        COUNT( DISTINCT meal_id)
        FROM meal
        GROUP BY price_category;
        WITH user_revenues AS (
        SELECT
        user_id,
        SUM(meal_price* order_quantity) AS revenue
        FROM meals
        JOIN orders on id=id
        GROUP BY user_id
        SELECT
        CASE
        WHEN revenue <150 THEN ' low'
        WHEN revenue< 300 THEN
        ELSE ' High'
        END AS revenue_group,
        COUNT(DISTINCT user_id) AS users
        FROM user_revenues
        GROUP BY revenue_group;
        Histo(each) vs Bar graphs(summarized)
        
        Percentile
        nth percentile is the value for which n% of a data set's values values are beneath this value [0,99]
        Quartiles> 1st quartile-> 25th percentile/ 3rd quartile-> 75th percentile/ 2nd quartile -> 50th percentile, median
        Interquartile range(IQR): All data between the first and third quartiles
        WITH user_orders AS (
        SELECT
        user_id,
        COUNT( DISTINCT order_id) AS orders
        FROM orders
        GROUP BY user_id
        SELECT
        ROUND(
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY orders ):: NUMERIC, 2 AS orders_p25,
        ROUND(
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY orders):: NUMERIC, 2 AS orders_p50,
        ROUND(
        PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY orders):: NUMERIC, 2) AS orders_p75,
        ROUND(AVG(orders)::NUMERIC, 2) AS avg_orders
        FROM user_orders;
    - - Profits= Revenue-cost
        Revenue: income
        SELECT order_id, SUM(meal_price* order_quantity) AS revenue
        FROM meals
        INNER JOIN orders on ...
        GROUP BY ...;
        Dates>> DATE_TRUNC('week', '2018-06-12') ::DATE
        //SELECT DATE_TRUNC('week', order_date):: DATE AS delivr_week,
        SUM(meal_price*order_quantity) AS revenue
        FROM... JOIN ... OM
        WHERE order_date BETWEEN '2018-06-01' AND '2018-06-30'
        where 条件和week不相同//
        Cost and Common Table Expressions(CTEs)
        CTE> Store a query's results in a temporary table
        Reference the temporary table in a following query
        WITH table_1 AS
        (SELECT...
        FROM...),
        tables AS
        (SELECT...
        FROM...)
        SELECT...
        FROM table!
        JOIN table_2 ON...
        PROFIT>> KPI-Profit per user-Profit per meal- Profit per month
        Order_quantitymeal_price - stock_quantitymeal_cost
    - - Survey of useful functions
        
        Dealing with dates
        DATE_TRUNC('quarter', '2018-08-13)? '2018-07-01 00:00:00+00:00'
        
        Dates in reports
        -Human-readable dates are in importan
        -the default date format, '2018-08-13' isn't very readable
        TO_CHAR(DATE,TEXT) TEXT(the formatted date string)
        TO_CHAR('2018-08-13', ' FMDay DD, FMMonth YYYY)
        Friday 13, August 2018**
        TO_CHAR('2018-06-01', ' Dy') 'Fri'
        TO_CHAR('2018-06-02', ,Dy - DD') 'Sat - 02'
        FMDay-> Full day name
        MM ->Month of year 01-12
        Mon ->Abbreviated month name(Jan, Feb, etc.)
        FMMonth-> Full month name
        YY -> last 2 digits of year
        YYYY -> 4 digits
        SELECT DISTINCT
        order_date,
        TO_CHAR(order_date, 'FMDay DD, FMMonth YYYY') AS format_1,
        TO_CHAR(order_date, ' FMDay DD Mon/YYYY') AS format_2
        FROM orders
        ORDER BY...
        LIMIT 3;
        SUM(registrations)OVER(ORDER BY registration_month)
        LAG(mau)OVER(ORDER BY active_month)
        RANK() OVER(ORDER BY revenue DESC)
        
        Pivoting
        Rotating a table around a pivot columna; transposing a column's values into columns
        ->converts a "long" table into a "wide" one
        Benefits
        
        Control a table's shape while preserving its data
        
        Unstacked data viewed horizontally is often easier to read than stacked data viewed vertically
        
        CREATE EXTENSION IF NOT EXISTS tablefunc;
        SELECT * FROM CROSSTAB($$ TEXT source_sql $$)
        AS ct(column_1 DATA_TYPE_1,
        column2 DATA TYPE_2,
        .......);
        
        SELECT FROM CROSSTAB($$
        SELECT
        meal_id,
        DATE_TRUNC('month', order_date):: DATE AS delivr_month,
        COUNT(DISTINCT order_id):: INT AS orders
        FROM orders
        WHERE meal_id IN(0,1)
        AND order_date< '2018-08-01'
        GROUP BY meal_id, delivr_month
        ORDER BY meal_id, delivr_month $$)
        AS ct (meal_id INT,
        "2018-06-01" INT, "2018-07-01" INT)
        ORDER BY meal_id;
        TO_CHAR(order_date, '"Q"Q YYYY)> Q2 2018
        
        Producing executive reports
        
        Readability
        Dates: August 2018 not 2018-08-01
        Numbers: Round numbers to the second decimal at most (98.76, not 98.761234)
        Table shape: Reshape long tables into wide ines, pivoting by date when possible
        Order
        
        WITH eatery_orders AS(
        SELECT
        eatery, TO_CHAR(order_date, 'MM-Mon YYYY) AS..
        COUNT(DISTINCT order_id) AS...
        FROM meals
        JOIN ... on ....
        WHERE order_date >= '2018-10-01'
        GROUP BY
        ORDER BY ...,...)
        SELECT
        eatery, ..., RANK() OVER(PARTITION BY delivr_month ORDER BY count_orders DESC):: INT AS...
        FROM
        ORDER BY....
      - Planning the query
        
        Questions to ask
        What tables do we need to pull from?
        How should we combine the tables?
        What fields do we need to create?
        What filters need to be included?
        Any ordering or limiting needed?
        SELECT
        athlete_id,
        gender, age, gold
        FROM summer_games AS sg
        JOIN athletes AS a
        ON sg.athlete_id = a.id
        UNION ALL
        SELECT
        athlete_id, gender, age, gold
        FROM winther_games AS wg
        JOIN athletes AS a
        ON wg.athlete_id = a.id
        
        SELECT athlete_id, gender, age, gold
        FROM
        (SELECT athlete_id, gold
        FROM summer_games AS sg
        UNION ALL
        SELECT athlete_id, gold
        FROM winter_games AS wg) AS g
        JOIN athletes AS a
        ON g.athlete_id = a.id
        Several ways to create the same report
        Step-by-step= easier to troubleshoot
        
        UNION 对两个结果集进行并集操作，同时进行默认规则的排序
        UNION ALL 对两个结果集进行并集操作，包括重复行，不进行排序
        SELECT
        'summer' AS season, country, COUNT(DISTINCT event) AS events
        FROM summer_games AS s
        JOIN countries AS c
        ON s.country_id=c.id
        GROUP BY country
        UNION ALL
        SELECT
        'winter' AS season, country, COUNT(DISTINCT event)
        AS events
        FROM winter_games AS w
        JOIN countries AS c
        ON w.country_id = c.id
        GROUP BY country
        ORDER events DESC
        
        Creating custom fields
        Step 1: comment out bottom half
        Step 2: Add new field placeholders
        --CASE WHEN{condition_1} THEN {}
        WHEN {C2} THEN{}
        (ELSE{output})
        END
        
        Filtering and finishing touches
        Step 1: Setup subquery
        Select id
        FROM countries
        WHERE region = 'WESTERN EUROPE";
        Step 2: Setup WHERE statement
        WHERE country_id IN
        (SELECT id
        FROM countries
        WHERE region = 'WESTERN EUROPE')
        Remaining qq
        LIMIT? ORDER BY?
        ORDER of operations
        Two joins
        Add logic(CASE)
        UNION
        ORDER BY
        CASE WHEN --like '%0' THEN
  - - - Cleaning strings
        Messy strings
        String functions1. Remove characters/ Replace substrings REPLACE(string, old_string, new_string)**
        
        SELECT REPLACE(country, '.',' ') AS country_cleaned, SUM( points) as points
        FROM...
        GROUP BY country_cleaned;
        
        2. Parse string LEFT() RIGHT(), SUBSTRING(str,pos,len)
        
        SELECT LEFT(country, 2) AS country_cleaned,
        
        3. Change case UPPER(), LOWER(), INITCAP()
        
        SELECT UPPER(country) AS country_cleaned
        
        4. Remove spaces TRIM() LTRIM() RTRIM() BTRIM
        
        SELECT TRIM(country) AS country_cleaned
        
        Nesting functions
        UPPER(LEFT(TRIM(REPLACE(country, '.', ' ')),2))
        
        Dealing with nulls
        Filtering nulls
        WHERE * IS NOT NULL COALESCE(field, null_replacement)**
        COALESCE(region, 'Independent Athletes') AS region
        COALESCE(home,0)
        Nulls as a result of a query
        LEFT JOIN does not match all rows
        No CASE statement conditional is satisfied
        etc.
        Measuring the impact of nulls
        rows null
        SELECT SUM(CASE when country IS NULL THEN 1 else 0 end)/ SUM(1.00)
        FROM others;
        AVG(coalesce(data,0))
        
        Report duplication
        Ways to fix duplication
        
        Remove aggregations
        
        Add field to JOIN statement
        JOIN * AS m ON p.id= m.id AND p.year = m.year
        
        Rollup using subquery
        
        SELECT p.id, points, match_won
        FROM points AS p
        JOIN
        (SELECT id, SUM(matches_won) AS matches_won
        FROM matches
        GROUP BY id) AS m
        ON p.id = m.id；
        SUM(coalesce(gold,0)+coalesce(silver,0)+coalesce(bronze,0))
        SUM(coalesce(gold,0)+coalesce(silver,0)+coalesce(bronze,0))/CAST(p AS numeric)
    - - Building complex calculations
        1. Window functions
        No partition
        Partition
        SUM(value)OVER(PARTITION BY field ORDER BY field)
        --PARTITION BY= range of calculation
        ORDER BY = order of rows when running calculation
        VS SUM(bronze) OVER () AS total_bronze
        LAG() LEAD()
        ROW_NUMBER() RANK()
        2.Layered calculations
        Aggregate an existing aggregation
        Leverages a subquery
        INITIAL table> Intermediate table?> Final report
        Ordering for window function?
        Two aggregations with a layered calculation?
        ROM_NUMBER()OVER(PARTITION BY region ORDER BY SUM(gold) DESC) AS row_num
        WHRE row_num =1
        
        Comparing groups
        Volume metrics
        Scale with size> Revenue, Population
        Percent of total calculation
        1. Calculate total
        SELECT
        team_id, SUM(points) AS points, SUM(points)OVER() AS total points
        2. Calculate percent of total
        SELECT team_id, SUM(points) AS points
        SUM(points)/SUM(points)OVER() AS perc_of_total
        Percent of points scored per player for each team
        SUM(points) /SUM(points) OVER(PARTITION BY team_id) AS perc_of_team
        Efficiency metrics(效率指标）
        Does not scale with size
        Typically a ratio> Profit Margin, Revenue per customer
        Performance index
        Compare performance to a benchmark
        Benchmark typically an average or median
        
        Points per game for each team
        points/games AS team_ppg
        
        Points per game for entire league
        SUM(points)OVER()/SUM(games)OVER() AS league_ppg (2 window functions)
        
        Performance index
        SUM(points)OVER/SUM(games)OVER() AS league_ppg,
        (points/games)/(SUM(points)OVER()/SUM(games) OVER()) AS perf_index
        
        ?? Global_gdp
        SELECT region, country, SUM(gdp) AS country_gdp, SUM(SUM(gdp))OVER
        SUM(gdp)/SUM(pop-in_millions)/ (SUM(SUM(gdp))OVER()/SUM(SUM(pop_in_millions)) OVER()) AS performance_index
        
        Comparing dates
        Month-over-month comparison
        LAG(value,offset) outputs a value from an offset number of rows previous to the current row.
        LEAD(value, offset)outputs a value from an offset number of rows after the current row DEFAULT 1
        Step 1 Show revenue by month
        DATE_PART('month',date) AS month,
        SUM(revenue) as current_rev
        Step 2: Previous month's revenue
        SELECT
        DATE_part('month', date) AS month,
        SUM(revenue) as current_rev,
        LAG(SUM(revenue) OVER(ORDER BY date_PART('month',date)) AS prev_rev
        Step 3: Percent change calculation
        DATE_PART('month', date) AS month,
        SUM(revenue) as current_rev,
        LAG(SUM(revenue))OVER (PARTITION BY country_id ORDER BY DATE_PART('month',date)) AS perv_rev,
        SUM(revenue)/LAG(SUM(revenue)) OVER (ORDER BY DATE_PART('month',date))-1 AS perc_change
        SUM(view)/LAG(SUM(view))OVER(PARTITION BY country_id ORDER BY DATE_PART('month',date))-1 AS perc_change
        Rolling calculations
        Only take into account 7 rows> ROWS BETWEEN
        SUM(value)OVER(ORDER BY value ROWS BRTWEEN N PRECEDING AND CURRENT ROW)
        SUM(SUM(revenue))OVER(ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)AS weekly_revenue
        LAG(weekly_avg,7) OVER(ORDER BY date) AS weekly_avg_precious
        /SUM(SUM(revenue))OVER(ORDER BY date ROWS 7 preceding)*AS weekly_revenue
        
        FROM original_table
        GROUP BY date;
        
        SUM(gdp)/SUM(SUM(gdp))OVER() AS
- - - - Update your database as the structure changes
        INSERT INTO organizations
        SELECT DISTINCT organisation, organizationg_sector
        FROM university_professors;
        INSERT INTO table_name (column_a, column_b)
        VALUES ("value_a", "value_b");
        INSERT INTO transactions (transaction_date, amount, fee)
        VALUES ('2018-24-09', 5454, '30');
        
        改名ALTER TABLE table_name
        RENAME COLUMN old_name TO new_name;
        删表ALTER TABLE table_name
        DROP COLUMN column_name;
        e.g. ALTER TABLE affiliations
        RENAME COLUMN organisation TO organization;
        ALTER TABLE affiliations
        DROP COLUMN university_shortname;
        DROP TABLE university_professors;
        INSERT INTO professors
        SELECT DISTINCT firstname, lastname, university_shortname
        FROM university_professors;
        SELECT *
        FROM professors;
  - - - Working with data types
        Enforced on columns
        Define the so-called "domain of a column
        Define what operations are possible
        Enforce consistent storage of values
        The most common types
        text: character strings of any length
        varchar[(x)]: a max of n characters
        char[(x)]: a fixed-length
        boolean: can only take three states, e.g. TRUE, FALSE and NULL(unknown)
        date, time and timestamp: various formats for date and time calculations
        numeric: arbitrary precision numbers, e.g. 3.1457
        integer: whole number in the range of -2147483648 and + 2147483648
        CREATE TABLE student (
        ssn integer,
        name archer(64),
        dob date, average_grade numeric(3,2)-- e.g. 5.54 -- 3 digits and 2 of them after the fractional point
        tuition_paid boolean
        );
        改类别
        ALTER TABLE students
        ALTER COLUMN name
        TYPE varcher(128)
        ALTER TABLE students
        ALTER COLUMN average_grade
        TYPE integer
        -- Turns 5.54 into 6 not 5
        USING ROUNG(average_grade);
        ALTER TABLE table_name
        ALTER COLUMN column_name
        TYPE varcher(x)
        USING SUBSTRING(column_name FROM 1 for x)
        --x the first x characters of it
        
        The not-null and unique constraints
        Disallow NULL values in a certain column
        Must hold true for the current state
        Must hold true for any future state
        NULL? unknown, does not exist, does not apply
        
        When creating a table
        CREATE TABLE students (
        ssn integer not null,
        lastname varchar (64) not null,
        home_phone integer,
        office_phone integer);
        After the table has been created.
        ALTER TABLE students
        ALTER column home_phone
        SET NOT NULL;
        OR DROP
        ALTER TABLE students
        ALTER COLUMN ssn
        DROP NOT NULL;
        The unique constraint>
        Disallow duplicate values in a column
        Must hold true both for now and for future
        CREATE TABLE table_nale( column_name UNIQUE);
        OR
        ALTER TABEL table_name
        ADD CONSTRAINT some_name UNIQUE(column_name);
  - - - Primary key
        One primary key per data base table, chosen from candidate keys
        Uniquely identifies records, e.g. for referencing in other tables
        Unique and bot-null constraints both apply
        Primary keys are time-invariant: choose columns wisely
        e.g. CREATE TABLE products(
        product_no integer UNIQUE NOT NULL,
        name, text,
        price numeric);
        CREATE TABLE products(
        product_no integer PRIMARY KEY,
        name text,
        price numeric);
        CREATE TABLE example(
        a integer,
        b integer,
        c integer,
        PRIMARY KEY (a, c));
        ALTER TABLE table_name
        ADD CONSTRAINT some_name PRIMARY KEY (column_name)
        
        ALTER TABLE universities
        RENAME university_shortname TO id;
        ALTER TABLE universities
        ADD CONSTRAINT university_pk PRIMARY KEY (id);
        
        Surrogate key
        Primary keys should be built from as few columns as possible
        Primary keys should never change over time
        Adding serial number
        ALTER TABLE cars
        ADD COLUMN id serial PRIMARY KEY;
        ALTER TABLE cars
        ADD COLUMN id serial PRIMARY KEY;
        INSERT INTO cars
        VALUES ('Volkswagen', ,Blitz', 'black');
        Another type of surrogate key
        ALTER TABLE table_name
        ADD COLUMN column_c VARCHAR(256);
        UPDATE table_name
        SET column_c = CONCAT(column_a, column_b);
        ALTER TABLE table_nme
        ADD CONSTRAINT pk PRIMARY KEY (column_c);
        
        ALTER TABLE professors
        ADD COLUMN id serial;
        SELECT COUNT(DISTINCT CONCAT(make,model))
        FROM cars
        
        SELECT COUNT(DISTINCT (make, model))
        FROM cars;
        ALTER TABLE cars
        ADD COLUMN id varchar(128)
        UPDATE cars
        SET id=CONCAT(make, model)
        CREATE TABLE students(
        last_name VARCHAR(128) NOT NULL,
        ssn integer PRIMARY KEY,
        phone_no char(12));
  - - - Model more complex relations
        Create a table
        Add foreign keys for every connecter table
        attributes
        CREATE TABLE affiliations (
        professor_id integer REFERENCES professors(id),
        organisation_id varchar(256) REFERENCES organisations (id),
        function varchar(256));
        Here no primary key
        UPDATE columns of a table based on values in another table
        UPDATE table_a
        SET column_to_date= table_b.column_to_update_from
        FROM table_b
        WHERE condition
        
        Referential integrity
        refer to an existing record
        Enfored through FK
        
        Violations
        CREATE TABLE a (
        id integer PRIMARY KEY,
        column_a varchar(64),
        ...,
        b_id integer REFERENCES b(id) ON DELETE NO ACTION);
        CREATE TABLE a(
        id integer PRIMARY KEY,
        column_a varchar(64),
        ...
        b_id integer REFERENCES b(id) ON DELETE CASCASE);
        DELETE all referencing records
        NO ACTION / RESTRICT > Throw an effort
        SET NULL: Set the referencing column to NULL
        SET DEFAULT: Set the referencing column to its default value
        ALTER TABLE affiliations
        DROP constraint affiliations_organizations_id_key;
        ALTER TABLE affilations
        ADD CONSTRAINT affiliations_organization_id_fk FOREIGN KEY(organization_id) REFERENCES organisations(id) ON DELETE CASCADE;
        DELETE FROM organization
        WHERE id = 'CUREM';
        SELECT * FROM affilations
        WHERE organization_id = 'CUREM'
        
        ROUNDUP
        Column data types
        key constraints
        Relationships between tables

 SELECT *
 FROM customers as c
WHERE EXISTS 
(SELECT * 
 FROM renting AS r 
 WHERE rating IS NOT NULL
 AND r.customer_id = c.customer_id