/ -*- coding: utf-8 -*-
/ ---
/ jupyter:
/   jupytext:
/     text_representation:
/       extension: .q
/       format_name: light
/       format_version: '1.5'
/       jupytext_version: 1.14.4
/   kernelspec:
/     display_name: SQL
/     language: sql
/     name: SQL
/ ---

/ + [markdown] azdata_cell_guid="5106b424-4160-4375-9cbb-3828c940ab34"
/ # Training: SQL (Medium-users) 🕵️‍♀️
/ Welcome to the training notebook on using SQL.
/
/ This notebook is pitched at medium-users who perform more advanced querying operations to retrieve heavily-wrangled data from SQL.
/
/ They will have **READ-ONLY** or higher access to the database.
/
/ ![SQL meme image](https://live.staticflickr.com/65535/49163279341_1d1c114c1f.jpg "SQL meme doge image")
/
/ # What will this session cover? 👁
/ This session will show you how to do the following things in SQL:
/
/ 1. Differences between Tables and Views
/ 1. Three types of temporary tables
/      - Local temporary table
/      - Common-Table-Expression (CTE)
/      - Global temporary table
/ 1. Subquerying the data
/ 1. Ranking groups of variables by a counter
/ 1. Pivoting data from long to wide shape
/ 1. Unpivoting data from wide to long shape
/ 1. Data matrix/tidy data principles
/

/ + azdata_cell_guid="8521f549-cd1e-4403-b5a2-ade42bbde72a"
-- Set database to use
USE [AdventureWorks];

/ + [markdown] azdata_cell_guid="eaca3e75-7812-426b-913c-148078b97791"
/ # 1. Difference between Table and View 🐵🙉🙊🙈
/ **Tables** are 'physical' storages of data that you can query from, whereas **Views** are a representation of **Tables**, typically in a different format.
/
/ Crucially, **Views** do no contain data, but **Tables** do. Instead, they are always querying a **Table** in the background to show you the data.
/
/ To create a **View**, just prefix a humble `SELECT` statement with `CREATE VIEW [<schema_name>].[vw_<view_name>] AS (...)`.
/
/ > **TIP:** If you are changing the shape of your data in a **Table** and want to capture this permanently, then you should create a **View**. You should not create a **Table** as this will duplicate your data unecessarily and take up more memory in your database. 

/ + azdata_cell_guid="f803291e-532b-4412-89ba-e935ea364e87"
-- template: create a view
CREATE VIEW [HumanResources].[vw_EmployeeDepartment] AS 
(
    SELECT 
        table_employee.[BusinessEntityID] 
        ,table_person.[Title] 
        ,table_person.[FirstName] 
        ,table_person.[MiddleName] 
        ,table_person.[LastName] 
        ,table_person.[Suffix] 
        ,table_employee.[JobTitle]
        ,[Department] = table_department.[Name]
        ,table_department.[GroupName] 
        ,table_edudepthist.[StartDate] 
    FROM [HumanResources].[Employee] AS table_employee
    INNER JOIN [Person].[Person] AS table_person
        ON table_person.[BusinessEntityID] = table_employee.[BusinessEntityID]
    INNER JOIN [HumanResources].[EmployeeDepartmentHistory] AS table_edudepthist 
        ON table_employee.[BusinessEntityID] = table_edudepthist.[BusinessEntityID] 
    INNER JOIN [HumanResources].[Department] AS table_department 
        ON table_edudepthist.[DepartmentID] = table_department .[DepartmentID] 
    WHERE table_edudepthist.[EndDate] IS NULL
)

/ + [markdown] azdata_cell_guid="0c5cb5ec-173c-492b-bf26-8ff14c8c879e"
/ # 2. Three types of temporary tables 🍺🍷🍹
/ **Temporary tables** are useful for more complicated querying where you have to store the results of a table transformation at an intermediate stage to perform another transformation on top.
/
/ There are three types of temporary tables in SQL which you can create:
/ 1. Local temporary table
/ 1. Common Table Expression (CTE)
/ 1. Global temporary table
/
/ We will outline below the subtle differences between each, and provide advice on when you would want to use each one.
/
/

/ + [markdown] azdata_cell_guid="4d729086-f22f-4691-8c4d-be6b64017d84"
/ ## 2.i. Local temporary table 🍺
/ When you create a **local temporary table**, you are storing it temporarily in your current SQL session (so the temporary table resides on the window pane where you wrote the code to create the temporary table). This means you can run the chunk of your code that creates the **local temporary table**, then retrieve the results later so you can perform another transformation on it in the *same* window pane where you wrote the code to create the **local temporary table**.
/
/ It does not mean you can retrieve the results of a **local temporary table** outside of the window pane that has the code which creates the table.
/
/ To create a **local temporary table**, use the `INTO #<temp_table_name>` statement right before you write the `FROM <table_name>...` part.
/
/ > **TIP:** Sometimes, you would want to run a query again after making a tweak to it. A **local temporary table** cannot be overwritten so running the query again will lead to an error. To overcome this, you will need to delete the table first, then run the query again to create it. This quickly gets annoying so one way to succintly avoid creating **local temporary tables** and then ad-hoc-ly deleting them is to include the following line at the top of your code: `IF OBJECT_ID('tempdb..#<temp_table_name>') IS NOT NULL THEN DROP TABLE #<temp_table_name>`

/ + azdata_cell_guid="4d258992-9c87-4e16-b10e-7eb8b6b7984b"
-- store into a local temporary table
SELECT [ProductID]
    ,[Name]
    ,[ProductNumber]
    ,[Color]
    ,[StandardCost]
    ,[ListPrice]
    ,[DaysToManufacture]
    ,[DailyCostOfManufacture] = CASE 
        WHEN [DaysToManufacture] = 0 THEN 0
        ELSE [StandardCost]/[DaysToManufacture]
        END
    ,[SellStartDate]
    ,[SellEndDate]
    ,[DailyUnitRevenue] = CASE
        WHEN [SellStartDate] = [SellEndDate] THEN 0
        ELSE [ListPrice]/CAST(([SellEndDate] - [SellStartDate]) AS FLOAT)
        END
    ,[DiscontinuedDate]
INTO #table_templocal
FROM [Production].[Product];

/ + azdata_cell_guid="664babc8-a022-473f-ac3c-9f3d201173ce"
-- call results of local temporary table separately and transform
SELECT [ProductID]
    ,[Name]
    ,[ProductNumber]
    ,[Color]
    ,[StandardCost]
    ,[ListPrice]
    ,[DaysToManufacture]
    ,[DailyCostOfManufacture]
    ,[DailyUnitProfit] = [DailyUnitRevenue] - [DailyCostOfManufacture]
    ,[SellStartDate]
    ,[SellEndDate]
    ,[DiscontinuedDate]
FROM #table_templocal
WHERE [DailyCostOfManufacture] > 0
    AND [DailyUnitRevenue] > 0;

/ + [markdown] azdata_cell_guid="2d320a9a-152f-497c-a000-d9df838707e7"
/ ## 2.ii. Common-Table-Expression (CTE) 🍷
/ When you are creating a **CTE**, you are storing it temporarily in your current query. This means that the results of your query cannot be retrieved in a later part of your code unless you explicitly run it *alongside* the code that creates the **CTE**
/
/ As the **CTE** does not exist outside your current query, then there is no need to delete it explicitly when you want to create it again. You only need to run the code to create it again.
/
/ To create a **CTE**, wrap your `SELECT...` statement within the round brackets of the following code, `;WITH <cte_name> AS ()...`
/
/ > **TIP:** At initial glance, **CTE**s may appear to be an inferior cousin to **local temporary tables**, but they are in fact, very powerful. Due to the nature in which they only exist within the query execution itself, then they can be used in defining **Views**, to insert into a created table, and as part of the creation of other SQL objects. **Local temporary tables** typically cannot.

/ + azdata_cell_guid="c538e293-53fa-4155-a1ab-5df58c518130"
-- need to execute CTE and accompanying queries in one go
WITH table_cte AS
(
    SELECT [ProductID]
        ,[Name]
        ,[ProductNumber]
        ,[Color]
        ,[StandardCost]
        ,[ListPrice]
        ,[DaysToManufacture]
        ,[DailyCostOfManufacture] = CASE 
            WHEN [DaysToManufacture] = 0 THEN 0
            ELSE [StandardCost]/[DaysToManufacture]
            END
        ,[SellStartDate]
        ,[SellEndDate]
        ,[DailyUnitRevenue] = CASE
            WHEN [SellStartDate] = [SellEndDate] THEN 0
            ELSE [ListPrice]/CAST(([SellEndDate] - [SellStartDate]) AS FLOAT)
            END
        ,[DiscontinuedDate]
    FROM [Production].[Product]
)

SELECT [ProductID]
    ,[Name]
    ,[ProductNumber]
    ,[Color]
    ,[StandardCost]
    ,[ListPrice]
    ,[DaysToManufacture]
    ,[DailyCostOfManufacture]
    ,[DailyUnitProfit] = [DailyUnitRevenue] - [DailyCostOfManufacture]
    ,[SellStartDate]
    ,[SellEndDate]
    ,[DiscontinuedDate]
FROM table_cte
WHERE [DailyCostOfManufacture] > 0
    AND [DailyUnitRevenue] > 0;

/ + [markdown] azdata_cell_guid="77b6b8bd-7d29-4ac5-9c73-e78ae19361e2"
/ ## 2.iii. Global temporary table 🍹
/ When you are creating a **global temporary table**, you are storing it *permanently* on a database, `[tempdb]`, so that others using the same server can access. 
/
/ In contrast to the other two temporary tables, **local temporary tables** and **CTEs**, you need to explicitly delete these tables.
/
/ Creating a **global temporary table** is like creating a **local temporary table**, just use the `INTO <##temp_table_name>` statement right before you write the `FROM <##table_name>...` part. Notice the emphasis on two hash symbols, `##`, instead of just one which is used for **local temporary tables**.
/
/ > **WARNING:** The `[tempdb]` database stores these **global temporary tables**, and as this database is somewhat invisible since it is hidden in the *Object Explorer* on SSMS, one can often forget to delete the table afterwards to save space. This is why it is seldom advised to create a **global temporary table**. 

/ + azdata_cell_guid="f3486f36-eaea-4f14-b6bb-a8bb32bc203c"
-- store into a global temporary table
SELECT [ProductID]
    ,[Name]
    ,[ProductNumber]
    ,[Color]
    ,[StandardCost]
    ,[ListPrice]
    ,[DaysToManufacture]
    ,[DailyCostOfManufacture] = CASE 
        WHEN [DaysToManufacture] = 0 THEN 0
        ELSE [StandardCost]/[DaysToManufacture]
        END
    ,[SellStartDate]
    ,[SellEndDate]
    ,[DailyUnitRevenue] = CASE
        WHEN [SellStartDate] = [SellEndDate] THEN 0
        ELSE [ListPrice]/CAST(([SellEndDate] - [SellStartDate]) AS FLOAT)
        END
    ,[DiscontinuedDate]
INTO ##table_tempglobal
FROM [Production].[Product];

/ + azdata_cell_guid="ac84dbb1-83e9-4f05-9895-21516773c2cc"
-- call results of local temporary table separately and transform
SELECT [ProductID]
    ,[Name]
    ,[ProductNumber]
    ,[Color]
    ,[StandardCost]
    ,[ListPrice]
    ,[DaysToManufacture]
    ,[DailyCostOfManufacture]
    ,[DailyUnitProfit] = [DailyUnitRevenue] - [DailyCostOfManufacture]
    ,[SellStartDate]
    ,[SellEndDate]
    ,[DiscontinuedDate]
FROM ##table_tempglobal
WHERE [DailyCostOfManufacture] > 0
    AND [DailyUnitRevenue] > 0;

/ + azdata_cell_guid="2c413e72-198e-4f76-8a4a-df5ef4ada46a"
-- delete global temporary table as good practice
DROP TABLE ##table_tempglobal

/ + [markdown] azdata_cell_guid="182942f6-4b79-4cb5-a104-bb78f6f18939"
/ # 3. Subquerying the data 🤹‍♀️
/ Akin to **temporary tables**, **subquerying** your data is an alternative way to store temporary results of your queries to query on top of.
/
/ It is of closer relation to **CTEs** because of the way the results are stored within the instance in which the query is run. Once the query finishes running, the table created from the **subquery** no longer exists.
/
/ To **subquery** your data, wrap the primary part of your query inside the round braces of the secondaty part, `...FROM (...) ...`.
/
/ > **TIP:** **Subqueries** are sometimes harder to read and understand than using **temporary tables**. Using **temporary tables** allows you separate out your logic to create the table in an intuitively ordered format, whereas with **subquerying**, it is not obvious that the query hidden within the `...FROM(...)...` is ran first then followed by the outer part of the query. Only in certain scenarios are **subqueries** necessary such as (5.) and (6.) when we **pivot** and **unpivot** data. 

/ + azdata_cell_guid="8479654c-9b86-4513-a25b-f74f2dcf7849"
-- subquery to succintly find high salaries paid
SELECT [BusinessEntityID]
	,[RateChangeDate]
	,[Rate]
    ,[PayFrequency]
    ,[TotalPaid]
FROM
(
	SELECT [BusinessEntityID]
		  ,[RateChangeDate]
		  ,[Rate]
		  ,[PayFrequency]
		  ,[TotalPaid] = [Rate] * [PayFrequency]
	FROM [HumanResources].[EmployeePayHistory]
) AS table_intermediate
WHERE [TotalPaid] > 100;

/ + [markdown] azdata_cell_guid="fd8d1b79-b1b6-40a2-a55a-bc005da39391"
/ ## EXERCISE: Subquerying vs temporary table
/ **Question:** Can you rewrite the query in (2.) to use **subquerying** instead?

/ + azdata_cell_guid="e5119885-3e36-4378-b641-e6bb67ce21bc"
-- Please write your answer below

/ + [markdown] azdata_cell_guid="82d50f5f-e1d2-4bb4-9bec-257f831485c8"
/ # 4. Ranking groups of variables by a counter 🎾
/ There are times when you want to **rank groups of your variables** in some order so you can perform manipulations on top. These situations could be:
/ 1. When you have annual data being updated at least two times in a year, and you want to identify which rows of your data are the latest based on a column that identifies when the data was imported.
/ 1. When you want to remove 'duplicate' rows in your table based on a ordered column.
/
/ In other words, you can think of this like **ordering** your table, but rather than order on the entire table, which is what we covered in the *Light-user* session, we are ranking/ordering groups and subgroups within the table.
/
/ To **rank** groups of variables by a counter, you will need to do the following:
/ 1. Create a new column, `[ColumnName_New]`
/ 2. Assign the following to it: 
/ `[ColumnName_New] = [ROW_NUMBER() OVER (PARTITION BY [ColumnName_1] , ... [ColumnName_n]) ORDER BY [ColumnName_i], ..., [ColumnName_k] ASC/DESC)`  
/
/ > **USER STORY:** *As a bedroom hacker seeking an exciting life working as an embedded operative for a disruptive communication company who wrap their tentacles secretly around phone, internet, instant messaging and other channels, I want to be able to retrive everyone's latest addresses so I can plan a larges-cale DDoS attack on the biggest websites used by the most users.*
/

/ + azdata_cell_guid="00cf2f68-d620-47a0-b5d6-75551e4fddba"
-- remove 'duplicate' values of [AddressLine1], [City] and [StateProvinceID]
SELECT [AddressID]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvinceID]
	  ,[PostalCode]
FROM
(
	SELECT [AddressID]
		,[AddressLine1]
		,[AddressLine2]
		,[City]
		,[StateProvinceID]
		,[RowNumber] = ROW_NUMBER() OVER 
		(
			PARTITION BY [AddressLine1], [City], [StateProvinceID] 
			ORDER BY [ModifiedDate] DESC
		)
		,[PostalCode]
	FROM [Person].[Address]
) AS table_intermediate
-- remove duplicates
WHERE [RowNumber] = 1;

/ + [markdown] azdata_cell_guid="e02ce52e-3317-4c7e-893c-173b5fad1c41"
/ ## EXERCISE: Ranking groups of variables by a counter with subquerying
/ **Question:** Can you rewrite the above query in (4.) using either a **local temporary table** or **CTE** instead of subquerying?

/ + azdata_cell_guid="f9b04ae2-30e3-47fe-a1b2-8b327d4ace60"
-- Please write your answer below

/ + [markdown] azdata_cell_guid="2ffd3868-3902-4cb0-a139-e789dcc2c380"
/ # 5. Pivoting data from long to wide shape 💃
/ **Pivoting data** so that you have rows within a column being transposed into columns is useful for various purposes like *one-hot encoding* your categorical data before applying machine-learning models on it in Python, or for presentation purposes.
/
/ A good way of thinking about **pivoting** is that you are going from a thinner and longer table to a wider and shorter table.
/
/ When **pivoting** your data, it is useful to know what you are **pivoting** across, so typically, you need to understand the unique enties in the column that's being pivoted across.
/
/ To **pivot** your data, it is quite involved to describe at a high-level so the example below will provide a better description of how it works.

/ + azdata_cell_guid="572d9d4e-7885-4583-aa01-b91c2a70f4db"
-- check unique values for [Gender] as pivoting across this
SELECT DISTINCT [Gender] FROM [HumanResources].[Employee];

-- pivot across [Gender] on [VacationHours]
SELECT [BusinessEntityID]
      ,[NationalIDNumber]
      ,[LoginID]
      ,[JobTitle]
      ,[BirthDate]
      ,[MaritalStatus]
      ,[VacationHours_Female] = [F]
      ,[VacationHours_Male] = [M]
FROM
(
	SELECT [BusinessEntityID]
            ,[NationalIDNumber]
            ,[LoginID]
            ,[JobTitle]
            ,[BirthDate]
            ,[MaritalStatus]
            ,[Gender]
            ,[VacationHours]
	FROM [HumanResources].[Employee]
) AS table_intermediate
PIVOT
(
	AVG([VacationHours]) 
	FOR [Gender] IN ([F], [M])
) AS table_end;

/ + [markdown] azdata_cell_guid="bb404c27-0bc1-4003-9917-a761ea5ebdf0"
/ ## EXERCISE: Pivoting
/ **QUESTION:** In the above query (5.), pivot across `[MaritalStatus]` on `[VacationHours]`.

/ + azdata_cell_guid="01af1223-0c24-4c1f-bbdc-883adef6339a"
-- Please write your answer below

/ + [markdown] azdata_cell_guid="a647a96c-49e8-4388-9d68-f3e3c0d6a2b5"
/ # 6. Unpivoting data from wide to long shape 🐎
/ **Unpivoting** data so that you have columns being transposed to rows is useful when trying to structure/format your data consistently for different analytical software programs or for presentation purposes.
/
/ A good way to think about **unpivoting** is that you are going from your table being wider and shorter to it being thinner and longer.
/
/ To **unpivot** your data, it is quite involved to describe at a high-level so the example below will provide a better description of how it works.

/ + azdata_cell_guid="d7cfe9af-3cf8-42c4-b65e-0b459807d49c"
-- unpivot on [StartDate] and [EndDate]
SELECT [BusinessEntityID]
      ,[DepartmentID]
      ,[ShiftID]
      ,[DateType]
      ,[DateValue]
FROM
(
	SELECT [BusinessEntityID]
		  ,[DepartmentID]
		  ,[ShiftID]
		  ,[StartDate]
		  ,[EndDate]
	FROM [HumanResources].[EmployeeDepartmentHistory]
) AS table_intermediate
UNPIVOT
(
	[DateValue]
	FOR [DateType] IN ([StartDate], [EndDate])
) AS table_end;

/ + [markdown] azdata_cell_guid="9e1a5d0f-7604-44a0-a952-87256d35af86"
/ # 7. Data matrix/tidy data principles 📋
/ It is best practice from an analyst's perspective for tables to be formatted in data matrix/tidy data format. For a table to be formatted in this way, it must adhere to two things:
/ - Each variable is a column
/ - Each observation is a row
/
/ It is best practice in the way that it standardises the way data is organised so the data cleaning process is easier and faster.
/
/ Whereas for messy, datasets, you can think of them like this:
/ > *Happy families are all alike; every unhappy family is unhappy in its own way* - <NAME>
/
/ From this persepctive, you can imagine that a messy dataset requires some initial upfront cost to understand how it is structured before you can clean it.
/
/ For a more thorough and example-laden discussion of tidy data principles, see this paper [here](https://cran.r-project.org/web/packages/tidyr/vignettes/tidy-data.html)
/
/ > **TIP:** This principle applies more generally outside of SQL.
/
/ **Note:** The query below is quite difficult but think of it in the way it incorporates several things we learnt today, can you spot them?

/ + azdata_cell_guid="6f8719c9-a233-42fd-81da-dd40fa4c0c85"
-- create first table in messy format!
WITH table_messy_a AS 
(
    SELECT * 
    FROM 
    (
        VALUES
            ('<NAME>', NULL, 18)
            ,('<NAME>', 4, 1)
            ,('Park Min Woo', 6, 6)
    ) AS table_sub ([PersonName], [Treatment_a], [Treatment_b])
)
SELECT * 
FROM table_messy_a;

-- create second table in messy format!
WITH table_messy_b AS
(
    SELECT *
    FROM 
    (
        VALUES
            ('a', NULL, 4, 6)
            ,('b', 18, 1, 6)
    ) AS table_sub ([Treatment], [JaneSmith], [XiTang], [ParkMinWoo])
)
SELECT * 
FROM table_messy_b;

/ + azdata_cell_guid="2e77bb75-df22-4138-aeca-96777119a3e2"
-- create above table in tidy format
WITH table_tidy AS
(
    SELECT *
    FROM
    (
        VALUES
            ('<NAME>', 'a', NULL)
            ,('<NAME>', 'b', 18)
            ,('Xi Tang', 'a', 4)
            ,('Xi Tang', 'b', 1)
            ,('Park Min Woo', 'a', 6)
            ,('Park Min Woo', 'b', 6)
    ) AS table_sub ([PersonName], [TreatmentType], [TreatmentValue])
)
SELECT *
FROM table_tidy;

/ + [markdown] azdata_cell_guid="f2e59ace-da4c-4b66-81dd-af8e75fd54f9"
/ *Aside: Whilst **data matrix/tidy data principles** are best practice for analysts, it is not for data architects working in SQL. Best practice for them would be to have tables in **long format**. Such a format enables total flexibility over table structure. This means when a new column needs to be added to a table, the table does not need to be deleted and created again with the new table (including the additional time required to import the data). Instead, such a format enables the additional column to be included as an extra row.* 
/
/ Further discussion of this is outside the scope of this training session, but if you want to find out more about this, then please read up on **snowflake schema** and **star schema**.

/ + [markdown] azdata_cell_guid="0a9068cb-0aff-4099-a47d-8438985aad81"
/ ## EXERCISE: Tidy data principles
/ **QUESTION:** Is the `[Sales].[SpecialOffer]` table in a tidy data format? If it is not in tidy data format, how can you manipulate the dataset so that it is?

/ + azdata_cell_guid="61762bad-0577-4178-818a-1bfc63c266bc"
-- Please write your answer below

StarcoderdataJupyterScriptsDedupFiltered