Advanced SQL

Question 1

Generally, numeric column types in various SQL databases do not support commas or currency symbols. True/False?

Answer 1

True

Question 2

How can we change data type of a column?

Answer 2

Use CAST or CONVERT to change the data type

Question 3

What is the syntax for casting data types?

Answer 3

CAST(column_name AS integer)
column_name::integer

SELECT
CAST(funding_total_usd AS varchar) AS funding_total_usd_string,
founded_at_clean::varchar AS founded_at_string
FROM tutorial.crunchbase_companies_clean_date

Question 4

When you perform arithmetic on dates (such as subtracting one date from another), what is the data type of the result?

Answer 4

The results are often stored as the interval data type—a series of integers that represent a period of time.

Question 5

What does the below code do?
SELECT companies.permalink,
companies.founded_at_clean,
companies.founded_at_clean::timestamp +
INTERVAL ‘1 week’ AS plus_one_week
FROM tutorial.crunchbase_companies_clean_date companies
WHERE founded_at_clean IS NOT NULL

Answer 5

The interval is defined using plain-English terms like ‘10 seconds’ or ‘5 months’. using the INTERVAL function.
Also note that adding or subtracting a date column and an interval column results in another date column as in the above query.
The code adds a week to each date in the column “companies.founded_at_clean”

Question 6

What does below code do?
SELECT companies.permalink,
companies.founded_at_clean,
NOW() - companies.founded_at_clean::timestamp AS founded_time_ago
FROM tutorial.crunchbase_companies_clean_date companies
WHERE founded_at_clean IS NOT NULL

Answer 6

You can add the current time (at the time you run the query) into your code using the NOW()function

Question 7

Does the below code work?

COUNT ( CASE WHEN acquisitions.acquired_at_cleaned-INTERVAL ‘3 years’ > companies.founded_at_clean::timestamp>= acquisitions.acquired_at_cleaned- INTERVAL ‘5 years’ THEN 1 ELSE NULL END ) AS between_3_to_5

(Using two interval data types for comparison)

Answer 7

No, it returns a syntax error. Using interval data types, work as below (one-sided)
COUNT (CASE WHEN acquisitions.acquired_at_cleaned <= companies.founded_at_clean::timestamp + INTERVAL ‘3 years’
THEN 1 ELSE NULL END) AS acquired_3_yrs

Question 8

What is data wrangling?

Answer 8

data wrangling (or munging) is the process of programmatically transforming data into a format that makes it easier to work with.

Question 9

Does using a particular function, in many cases, change the data to the appropriate type in SQL? Explain your answer

Answer 9

Yes. LEFT, RIGHT, and TRIM are all used to select only certain elements of strings, but using them to select elements of a number or date will treat them as strings for the purpose of the function.

Question 10

What does the left function do in SQL? how is the syntax?

Answer 10

You can use LEFT to pull a certain number of characters from the left side of a string and present them as a separate string. The syntax is LEFT (string, number of characters).

Question 11

What does the below code do?

SELECT incidnt_num,
date,
LEFT(date, 10) AS cleaned_date
FROM tutorial.sf_crime_incidents_2014_01

Answer 11

The code, extracts only the first 10 characters on the left of the entries in date column

Question 12

What does the below code do?
SELECT incidnt_num,
date,
LEFT(date, 10) AS cleaned_date,
RIGHT(date, 17) AS cleaned_time
FROM tutorial.sf_crime_incidents_2014_01

Answer 12

It extracts 10 characters from the left side and creates a new column
and then extracts 17 characters from the right side and creates another column

Question 13

What does the length function do?

Answer 13

The LENGTH function returns the length of a string.

Question 14

What does the below code do?
SELECT incidnt_num,
date,
LEFT(date, 10) AS cleaned_date,
RIGHT(date, LENGTH(date) - 11) AS cleaned_time
FROM tutorial.sf_crime_incidents_2014_01

Answer 14

it’s the same as:
SELECT incidnt_num,
date,
LEFT(date, 10) AS cleaned_date,
RIGHT(date, 17) AS cleaned_time
FROM tutorial.sf_crime_incidents_2014_01

Question 15

When using functions within other functions, it’s important to remember that the outermost functions will be evaluated first, followed by the functions encapsulated by them. True/False?

Answer 15

False. When using functions within other functions, it’s important to remember that the 🎆 innermost 🎆 functions will be evaluated first, followed by the functions that encapsulate them.

Question 16

What does the TRIM function do? How is its syntax?

Answer 16

The TRIM function is used to remove characters from the beginning and end of a string.

SELECT location,
TRIM(both ‘()’ FROM location)
FROM tutorial.sf_crime_incidents_2014_01

The TRIM function takes 3 arguments.
First, you have to specify whether you want to remove characters from the beginning (‘leading’), the end (‘trailing’), or both (‘both’, as used above).

Next you must specify all characters to be trimmed. Any characters included in the single quotes will be removed from both beginning, end, or both sides of the string.

Finally, you must specify the text you want to trim using FROM.

Question 17

What does the function POSITION do? How is its syntax?

Answer 17

POSITION allows you to specify a substring, then returns a numerical value equal to the character number (counting from left) where that substring first appears in the target string.
For example, the following query will return the position of the character ‘A’ (case-sensitive) where it first appears in the descript field

SELECT incidnt_num,
descript,
POSITION(‘A’ IN descript) AS a_position
FROM tutorial.sf_crime_incidents_2014_01

Question 18

Is POSITION function case-sensitive?

Answer 18

Yes

Question 19

What is another function that does the same thing as POSITION? how is its syntax?

Answer 19

You can also use the STRPOS function to achieve the same results as POSITION—just replace IN with a comma and switch the order of the string and substring:

SELECT incidnt_num,
descript,
STRPOS(descript, ‘A’) AS a_position
FROM tutorial.sf_crime_incidents_2014_01

Question 20

Is STRPOS case-sensitive?

Answer 20

yes

Question 21

What do you do If you want to look for a character regardless of its case?

Answer 21

You can make your entire string a single case string by using the UPPER or LOWER functions

Question 22

What does SUBSTR do? What is its syntax?

Answer 22

LEFT and RIGHT both create substrings of a specified length, but they only do so starting from the sides of an existing string. If you want to start in the middle of a string, you can use SUBSTR.
The syntax is SUBSTR(string, starting character position, # of characters)

SELECT incidnt_num,
date,
SUBSTR(date, 4, 2) AS day
FROM tutorial.sf_crime_incidents_2014_01

This returns 01/31/2014 08:00:00 AM +0000 as 31 (note that positions start from 1 not 0, and the starting position is inclusive)

Question 23

Do indices in SQL start from 1 or 0?

Answer 23

1

Question 24

What does CONCAT do? How is its syntax?

Answer 24

You can combine strings from several columns together (and with hard-coded values) using CONCAT. Simply order the values you want to concatenate and separate them with commas. If you want to hard-code values, enclose them in single quotes. Here’s an example

SELECT incidnt_num,
day_of_week,
LEFT(date, 10) AS cleaned_date,
CONCAT(day_of_week, ‘, ‘, LEFT(date, 10)) AS day_and_date
FROM tutorial.sf_crime_incidents_2014_01

Question 25

How can we use || instead of CONCAT?

Answer 25

Alternatively, you can use two pipe characters (||) to perform the same concatenation:

SELECT incidnt_num,
day_of_week,
LEFT(date, 10) AS cleaned_date,
day_of_week || ‘, ‘ || LEFT(date, 10) AS day_and_date
FROM tutorial.sf_crime_incidents_2014_01

Question 26

What is the use of LOWER, UPPER functions? How are their syntaxes?

Answer 26

They change the strings to upper or lower case.

SELECT incidnt_num,
address,
UPPER(address) AS address_upper,
LOWER(address) AS address_lower
FROM tutorial.sf_crime_incidents_2014_01

Question 27

What are 3 reasons for scrambled dates?

Answer 27

📌The data was manipulated in Excel at some point, and the dates were changed to MM/DD/YYYY format or another format that is not compliant with SQL’s strict standards.

📌The data was manually entered by someone who use whatever formatting convention he/she was most familiar with.

📌The date uses text (Jan, Feb, etc.) instead of numbers to record months.

Question 28

What is the difference between timestamp and date data types in SQL?

Answer 28

timestamp includes additional precision (hours, minutes, seconds)

Question 29

Assume we have a date or timestamp field, how can we extract different parts of it? Demonstrate with code, for a date column named cleaned_date

Answer 29

Use EXTRACT to pull the pieces apart one-by-one:

SELECT cleaned_date,
EXTRACT(‘year’ FROM cleaned_date) AS year,
EXTRACT(‘month’ FROM cleaned_date) AS month,
EXTRACT(‘day’ FROM cleaned_date) AS day,
EXTRACT(‘hour’ FROM cleaned_date) AS hour,
EXTRACT(‘minute’ FROM cleaned_date) AS minute,
EXTRACT(‘second’ FROM cleaned_date) AS second,
EXTRACT(‘decade’ FROM cleaned_date) AS decade,
EXTRACT(‘dow’ FROM cleaned_date) AS day_of_week
FROM tutorial.sf_crime_incidents_cleandate

Question 30

How can we round dates to the beginning of a certain unit of measurement? For example, we want to round dates based on year, so that every date in the table for each year becomes January 1st of that year?

Answer 30

You can also round dates to the nearest unit of measurement. This is particularly useful if you don’t care about an individual date, but do care about the week (or month, or quarter) that it occurred in. The DATE_TRUNC function rounds a date to whatever precision you specify. The value displayed is the first value in that period. So when you DATE_TRUNC by year, any value in that year will be listed as January 1st of that year:

SELECT cleaned_date,
DATE_TRUNC(‘year’ , cleaned_date) AS year,
DATE_TRUNC(‘month’ , cleaned_date) AS month,
DATE_TRUNC(‘week’ , cleaned_date) AS week,
DATE_TRUNC(‘day’ , cleaned_date) AS day,
DATE_TRUNC(‘hour’ , cleaned_date) AS hour,
DATE_TRUNC(‘minute’ , cleaned_date) AS minute,
DATE_TRUNC(‘second’ , cleaned_date) AS second,
DATE_TRUNC(‘decade’ , cleaned_date) AS decade
FROM tutorial.sf_crime_incidents_cleandate

Question 31

How can we get today’s date or time using SQL? Does it require a FROM clause?

Answer 31

You can instruct your query to pull the local date and time at the time the query is run using any number of functions. Interestingly, you can run them 📍 without 📍a FROM clause:

SELECT CURRENT_DATE AS date,
CURRENT_TIME AS time,
CURRENT_TIMESTAMP AS timestamp,
LOCALTIME AS localtime,
LOCALTIMESTAMP AS localtimestamp,
NOW() AS now

Question 32

How can we make current time appear in a different time zone in SQL?

Answer 32

You can make a time appear in a different time zone using AT TIME ZONE:

SELECT CURRENT_TIME AS time,
CURRENT_TIME AT TIME ZONE ‘PST’ AS time_pst

Question 33

How can we fill null values in SQL?

Answer 33

Occasionally, you will end up with a dataset that has some nulls that you’d prefer to contain actual values. This happens frequently in numerical data (displaying nulls as 0 is often preferable), and when performing outer joins that result in some unmatched rows. In cases like this, you can use COALESCE to replace the null values:

SELECT incidnt_num,
descript,
COALESCE(descript, ‘No Description’)
FROM tutorial.sf_crime_incidents_cleandate
ORDER BY descript DESC

Question 34

What are sub-queries?

Answer 34

Subqueries (also known as inner queries or nested queries) are a tool for performing operations in multiple steps. For example, if you wanted to take the sums of several columns, then average all of those values, you’d need to do each aggregation in a distinct step.

Subqueries can be used in several places within a query, but it’s easiest to start with the FROM statement. Here’s an example of a basic subquery:

SELECT sub.*
FROM (
SELECT *
FROM tutorial.sf_crime_incidents_2014_01
WHERE day_of_week = ‘Friday’
) sub
WHERE sub.resolution = ‘NONE’

Question 35

Once the inner query runs, the outer query will run using the results from the inner query as its underlying table. True/False

Answer 35

True

Question 36

Subqueries are not required to have names, which are added after parentheses the same way you would add an alias to a normal table. True/False?

Answer 36

False. they do need an alias

Question 37

Can we use sub-queries in conditional logic ( for example … where date=(sub-query)? If yes, how?

Answer 37

You can use subqueries in conditional logic (in conjunction with WHERE, JOIN/ON, or CASE). The following query returns all the entries from the earliest date in the dataset (theoretically—the poor formatting of the date column actually makes it return the value that sorts first alphabetically):

SELECT *
FROM tutorial.sf_crime_incidents_2014_01
WHERE Date = (SELECT MIN(date)
FROM tutorial.sf_crime_incidents_2014_01
)

Question 38

What does this code do?

SELECT *
FROM tutorial.sf_crime_incidents_2014_01
WHERE Date IN (SELECT date
FROM tutorial.sf_crime_incidents_2014_01
ORDER BY date
LIMIT 5
)

Answer 38

It chooses 5 first dates for the conditional logic
Note🧨 that you should not include an alias when you write a subquery in a conditional statement. This is because the subquery is treated as an individual value (or set of values in the IN case) rather than as a table.

Question 39

What is the difference between the below codes?

SELECT *
FROM tutorial.sf_crime_incidents_2014_01
WHERE Date IN (SELECT date
FROM tutorial.sf_crime_incidents_2014_01
ORDER BY date
LIMIT 5
)

SELECT *
FROM tutorial.sf_crime_incidents_2014_01 incidents
JOIN ( SELECT date
FROM tutorial.sf_crime_incidents_2014_01
ORDER BY date
LIMIT 5
) sub
ON incidents.date = sub.date

Answer 39

Nothing, they both do the same thing.

Question 40

What is the advantage of using sub-query joins over using them in conditional logic like where clause?

Answer 40

Sub-query joins can be particularly useful when combined with aggregations. When you join, the requirements for your sub-query output aren’t as stringent as when you use the WHERE clause. For example, your inner query can output multiple results. (multiple columns)

Question 41

What does below code do?

SELECT incidents.,
sub.count AS total_incidents_in_category
FROM tutorial.sf_crime_incidents_2014_01 incidents
JOIN (
SELECT category,
COUNT() AS count
FROM tutorial.sf_crime_incidents_2014_01
GROUP BY 1
ORDER BY 2
LIMIT 3
) sub
ON sub.category = incidents.category

Answer 41

First we start by analyzing the inner query, it returns the categories with the least count. When joined by the whole table, we can see the rows belonging to those categories. It prevents the need for writing multiple queries.

Question 42

Why is this code:

SELECT COALESCE(acquisitions.month, investments.month) AS month,
acquisitions.companies_acquired,
investments.companies_rec_investment
FROM (
SELECT acquired_month AS month,
COUNT(DISTINCT company_permalink) AS companies_acquired
FROM tutorial.crunchbase_acquisitions
GROUP BY 1
) acquisitions

FULL JOIN (
SELECT funded_month AS month,
COUNT(DISTINCT company_permalink) AS companies_rec_investment
FROM tutorial.crunchbase_investments
GROUP BY 1
)investments

ON acquisitions.month = investments.month  ORDER BY 1 DESC

Better than this code:🎭

SELECT COALESCE(acquisitions.acquired_month, investments.funded_month) AS month,
COUNT(DISTINCT acquisitions.company_permalink) AS companies_acquired,
COUNT(DISTINCT investments.company_permalink) AS investments
FROM tutorial.crunchbase_acquisitions acquisitions
FULL JOIN tutorial.crunchbase_investments investments
ON acquisitions.acquired_month = investments.funded_month
GROUP BY 1

Answer 42

Full joining two already big datasets and then following it with count(distinct) terribly slows down the process. In cases like this, it’s better to create two sub-queries and performing count(distinct) there an then joining them.

Question 43

What does the below code do?

SELECT COUNT(*) AS total_rows
FROM (
SELECT *
FROM tutorial.crunchbase_investments_part1

     UNION ALL

    SELECT *
      FROM tutorial.crunchbase_investments_part2
   ) sub

Answer 43

It’s certainly not uncommon for a dataset to come split into several parts, especially if the data passed through Excel at any point (Excel can only handle ~1M rows per spreadsheet). The two tables used above can be thought of as different parts of the same dataset—what you’d almost certainly like to do is perform operations on the entire combined dataset rather than on the individual parts. You can do this by using a sub-query

Question 44

COUNT(DISTINCT) can take a very long time to process if the dataset is big. True/False?

Answer 44

True

Question 45

It’s certainly not uncommon for a dataset to come split into several parts, especially if the data passed through Excel at any point, what you’d almost certainly like to do is perform operations on the entire combined dataset rather than on the individual parts. You can do this by using a …

Answer 45

subquery

SELECT COUNT(*) AS total_rows
FROM (
SELECT *
FROM tutorial.crunchbase_investments_part1

     UNION ALL

    SELECT *
      FROM tutorial.crunchbase_investments_part2
   ) sub

Question 46

It’s certainly not uncommon for a dataset to come split into several parts, especially if the data passed through Excel at any point, what you’d almost certainly like to do is perform operations on the entire combined dataset rather than on the individual parts. You can do this by using a …

Answer 46

subquery

SELECT COUNT(*) AS total_rows
FROM (
SELECT *
FROM tutorial.crunchbase_investments_part1

     UNION ALL

    SELECT *
      FROM tutorial.crunchbase_investments_part2
   ) sub

Question 47

What does a window function do?

Answer 47

A window function performs a calculation across a set of table rows that are somehow related to the current row.

Question 48

What is the difference between window functions and aggregation functions?

Answer 48

Unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row — the rows retain their separate identities.

Question 49

What does the below code do? and can we change the places of partition by and order by?
SELECT start_terminal,
duration_seconds,
SUM(duration_seconds) OVER
(PARTITION BY start_terminal ORDER BY start_time)
AS running_total
FROM tutorial.dc_bikeshare_q1_2012
WHERE start_time < ‘2012-01-08’

Answer 49

First question: The above query uses window functions and groups and orders the query by start_terminal. Within each value of start_terminal, it is ordered by start_time, and the running total sums across the current row and all previous rows of duration_seconds.
Second question: No

Question 50

What does the below code do?

SELECT start_terminal,
duration_seconds,
SUM(duration_seconds) OVER (PARTITION BY start_terminal) AS start_terminal_sum,
(duration_seconds/SUM(duration_seconds) OVER (PARTITION BY start_terminal))*100 AS pct_of_total_time
FROM tutorial.dc_bikeshare_q1_2012
WHERE start_time < ‘2012-01-08’
ORDER BY 1, 4 DESC

Answer 50

shows the duration of each ride as a percentage of the total time accrued by riders from each start_terminal

Question 51

When using window functions, you can apply the same aggregates that you would under normal circumstances, what are they?

Answer 51

SUM, COUNT, and AVG.

Question 52

What does ORDER BY do in window functions?

Answer 52

It simply orders by the designated column(s) the same way the ORDER BY clause would, except that it treats every partition as separate.

Question 53

Can we say we use window function when we want to expand each group and not have just one outcome?

Answer 53

Yes. Unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row — the rows retain their separate identities.

Question 54

What does the below code do?

SELECT end_terminal,
duration_seconds,
SUM(duration_seconds) OVER
(PARTITION BY end_terminal ORDER BY duration_seconds DESC)
AS running_total
FROM tutorial.dc_bikeshare_q1_2012
WHERE start_time < ‘2012-01-08’

Answer 54

Shows a running total of the duration of bike rides grouped by end_terminal, and with ride duration sorted in descending order.

Question 55

What does ROW_NUMBER() do?

Answer 55

ROW_NUMBER() does just what it sounds like—displays the number of a given row. It starts are 1 and numbers the rows according to the ORDER BY part of the window statement. ROW_NUMBER() does not require you to specify a variable within the parentheses

Question 56

What is the difference between rank() and row_number()?

Answer 56

RANK() is slightly different from ROW_NUMBER(). If you order by start_time, for example, it might be the case that some terminals have rides with two identical start times. In this case, they are given the same rank, whereas ROW_NUMBER() gives them different numbers.

Question 57

What’s the difference between rank() and rank_dense()?

Answer 57

RANK() would give the identical rows a rank of 2, then skip ranks 3 and 4, so the next result would be 5
DENSE_RANK() would still give all the identical rows a rank of 2, but the following row would be 3—no ranks would be skipped.

Question 58

What does the below code do?

SELECT *
FROM (
SELECT start_terminal,
start_time,
duration_seconds AS trip_time,
RANK() OVER (PARTITION BY start_terminal ORDER BY duration_seconds DESC) AS rank
FROM tutorial.dc_bikeshare_q1_2012
WHERE start_time < ‘2012-01-08’
) sub
WHERE sub.rank <= 5

Answer 58

Shows the 5 longest rides from each starting terminal, ordered by terminal, and longest to shortest rides within each terminal. Limit to rides that occurred before Jan. 8, 2012.

Question 59

What does the below code do?

SELECT start_terminal,
duration_seconds,
NTILE(4) OVER
(PARTITION BY start_terminal ORDER BY duration_seconds)
AS quartile,
NTILE(5) OVER
(PARTITION BY start_terminal ORDER BY duration_seconds)
AS quintile,
NTILE(100) OVER
(PARTITION BY start_terminal ORDER BY duration_seconds)
AS percentile
FROM tutorial.dc_bikeshare_q1_2012
WHERE start_time < ‘2012-01-08’
ORDER BY start_terminal, duration_seconds

Answer 59

Computes in which quantile each duration second is. NTILE(n) is for calculating quantiles

Question 60

What happens if we use NTILE(100) on a group with 20 members?

Answer 60

It won’t work properly, it just counts each number from 1 to 20, the n in NTILE(n) should be smaller than the number of members in a group.
If you’re working with very small windows, keep this in mind and consider using quartiles or similarly small bands.

Question 61

What does the below code do?
SELECT
duration_seconds,
NTILE(100) OVER
(ORDER BY duration_seconds)
AS percentile
FROM tutorial.dc_bikeshare_q1_2012
WHERE start_time < ‘2012-01-08’

Answer 61

Shows only the duration of the trip and the percentile into which that duration falls (across the entire dataset—not partitioned by terminal).

Question 62

Considering the below code:
SELECT duration_seconds,
SUM(duration_seconds) OVER (ORDER BY duration_seconds) AS running_total
FROM tutorial.dc_bikeshare_q1_2012
Is the output going to show the same number for all the rows with the same duration_seconds? If yes, what is this number showing?

Answer 62

Yes, it shows the sum of the rows with the same number for duration second.
It’ll be something like:
…
0 0
0 0
1 2
1 2
2 126
2 126
2 126
2 126
2 126
2 126
2 126
…

Question 63

What do LAG and LEAD window functions do?

Answer 63

LAG pulls from previous rows and LEAD pulls from following rows.
LAG shifts one down
LEAD shifts one up

Question 64

What does the below code do?
SELECT start_terminal,
duration_seconds,
LAG(duration_seconds, 1) OVER
(PARTITION BY start_terminal ORDER BY duration_seconds) AS lag,
LEAD(duration_seconds, 1) OVER
(PARTITION BY start_terminal ORDER BY duration_seconds) AS lead
FROM tutorial.dc_bikeshare_q1_2012
WHERE start_time < ‘2012-01-08’
ORDER BY start_terminal, duration_seconds

Answer 64

LAG shifts the duration_seconds in each start_terminal group one cell down
LEAD shifts the duration_seconds in each start_terminal group one cell up
returns something like this:

31000 74 277
31000 277 74 291

Question 65

After using LAG or LEAD window functions, we end up with null values in some rows, how can we get rid of them?

Answer 65

If you’d like to make the results a bit cleaner, you can wrap it in an outer query to remove nulls.

SELECT *
FROM (
SELECT start_terminal,
duration_seconds,
duration_seconds -LAG(duration_seconds, 1) OVER
(PARTITION BY start_terminal ORDER BY duration_seconds)
AS difference
FROM tutorial.dc_bikeshare_q1_2012
WHERE start_time < ‘2012-01-08’
ORDER BY start_terminal, duration_seconds
) sub
WHERE sub.difference IS NOT NULL

Question 66

What is window alias?

Answer 66

If you’re planning to write several window functions in to the same query, using the same window, you can create an alias.

Question 67

How can we rewrite the below code using window alias?

SELECT start_terminal,
duration_seconds,
NTILE(4) OVER
(PARTITION BY start_terminal ORDER BY duration_seconds)
AS quartile,
NTILE(5) OVER
(PARTITION BY start_terminal ORDER BY duration_seconds)
AS quintile,
NTILE(100) OVER
(PARTITION BY start_terminal ORDER BY duration_seconds)
AS percentile
FROM tutorial.dc_bikeshare_q1_2012
WHERE start_time < ‘2012-01-08’
ORDER BY start_terminal, duration_seconds

Answer 67

SELECT start_terminal,
duration_seconds,
NTILE(4) OVER ntile_window AS quartile,
NTILE(5) OVER ntile_window AS quintile,
NTILE(100) OVER ntile_window AS percentile
FROM tutorial.dc_bikeshare_q1_2012
WHERE start_time < ‘2012-01-08’
WINDOW ntile_window AS
(PARTITION BY start_terminal ORDER BY duration_seconds)
ORDER BY start_terminal, duration_seconds

Question 68

The WINDOW clause, if included, should always come after the WHERE clause. True/False?

Answer 68

True

Question 69

What are the window functions introduced in Mode?

Answer 69

SUM, COUNT, and AVG
ROW_NUMBER()
RANK() and DENSE_RANK()
NTILE
LAG and LEAD
Defining a window alias

Question 70

What are 3 of the controllable and 2 of the uncontrollable high-level things that will affect the number of calculations you need to make, and therefore your queries’ runtime?

Answer 70

Table size ✨: If your query hits one or more tables with millions of rows or more, it could affect performance.

Joins ✨: If your query joins two tables in a way that substantially increases the row count of the result set, your query is likely to be slow.

Aggregations ✨: Combining multiple rows to produce a result requires more computation than simply retrieving those rows.

Query runtime is also dependent on some things that you can’t really control related to the database itself:

Other users running queries ✨: The more queries running concurrently on a database, the more the database must process at a given time and the slower everything will run. It can be especially bad if others are running particularly resource-intensive queries that fulfill some of the above criteria.

Database software and optimization ✨: This is something you probably can’t control, but if you know the system you’re using, you can work within its bounds to make your queries more efficient.

Question 71

Filtering the data to include only the observations you need can dramatically improve query speed. True/False

Answer 71

True

Question 72

Why is it better to do exploratory analysis on a subset of data?

Answer 72

Keep in mind that you can always perform exploratory analysis on a subset of data, refine your work into a final query, then remove the limitation and run your work across the entire dataset. Mahsa: This helps with saving time and resources. This is why Mode enforces a LIMIT clause by default

Question 73

What should we do if we want to limit the dataset before performing the count?
(Since limiting happens after aggregation functions normally)

Answer 73

If you want to limit the dataset before performing the count (to speed things up), try doing it in a subquery:
Note 🧨: Using LIMIT this will dramatically alter your results, so you should use it to TEST query logic, but not to get actual results.

SELECT COUNT(*)
FROM (
SELECT *
FROM benn.sample_event_table
LIMIT 100
) sub

Question 74

What should you try, when your query joins two tables in a way that substantially increases the row count of the result set, hence it runs slowly?

Answer 74

It’s better to reduce table sizes before joining them. Meaning we can use sub-queries to filter tables, then join them, like below:

SELECT teams.conference,
sub.*
FROM (
SELECT players.school_name,
COUNT(*) AS players
FROM benn.college_football_players players
GROUP BY 1
) sub
JOIN benn.college_football_teams teams
ON teams.school_name = sub.school_name

Instead of

SELECT teams.conference AS conference,
players.school_name,
COUNT(1) AS players
FROM benn.college_football_players players
JOIN benn.college_football_teams teams
ON teams.school_name = players.school_name
GROUP BY 1,2

Question 75

What does EXPLAIN clause do?

Answer 75

You can add EXPLAIN at the beginning of any (working) query to get a sense of how long it will take. It’s not perfectly accurate, but it’s a useful tool. Try running this:

EXPLAIN
SELECT *
FROM benn.sample_event_table
WHERE event_date >= ‘2014-03-01’
AND event_date < ‘2014-04-01’
LIMIT 100

To clarify, this is most useful if you run EXPLAIN on a query, modify the steps that are expensive, then run EXPLAIN again to see if the cost is reduced. Finally, the LIMIT clause is executed last and is really cheap to run (24.65 vs 147.87 for the WHERE clause).

Question 76

Why do we pivot data?

Answer 76

presentation or charting

Question 77

How do we start making pivot tables in SQL?

Answer 77

It can be helpful to create the sub-query and select all columns from it before starting to make transformations, then use CASE statement to create new columns

Question 78

What does the below code do?

SELECT years.*,
earthquakes.magnitude,
CASE year
WHEN 2000 THEN year_2000
WHEN 2001 THEN year_2001
WHEN 2002 THEN year_2002
WHEN 2003 THEN year_2003
WHEN 2004 THEN year_2004
WHEN 2005 THEN year_2005
WHEN 2006 THEN year_2006
WHEN 2007 THEN year_2007
WHEN 2008 THEN year_2008
WHEN 2009 THEN year_2009
WHEN 2010 THEN year_2010
WHEN 2011 THEN year_2011
WHEN 2012 THEN year_2012
ELSE NULL END
AS number_of_earthquakes
FROM tutorial.worldwide_earthquakes earthquakes
CROSS JOIN (
SELECT year
FROM (VALUES (2000),(2001),(2002),(2003),(2004),(2005),(2006),
(2007),(2008),(2009),(2010),(2011),(2012)) v(year)
) years

Answer 78

The first thing to do here is to create a table that lists all the columns from the original table as rows in a new table.

SELECT year
FROM (VALUES (2000),(2001),(2002),(2003),(2004),(2005),(2006),
(2007),(2008),(2009),(2010),(2011),(2012)) v(year)
) years

Once you’ve got this, you can cross join it with the worldwide_earthquakes table to create an expanded view:

SELECT years.,
earthquakes.
FROM tutorial.worldwide_earthquakes earthquakes
CROSS JOIN (
SELECT year
FROM (VALUES (2000),(2001),(2002),(2003),(2004),(2005),(2006),
(2007),(2008),(2009),(2010),(2011),(2012)) v(year)
) years

Notice that each row in the worldwide_earthquakes is replicated 13 times. The last thing to do is to fix this using a CASE statement that pulls data from the correct column in the worldwide_earthquakes table given the value in the year column.

SELECT years.*,
earthquakes.magnitude,
CASE year
WHEN 2000 THEN year_2000
WHEN 2001 THEN year_2001
WHEN 2002 THEN year_2002
WHEN 2003 THEN year_2003
WHEN 2004 THEN year_2004
WHEN 2005 THEN year_2005
WHEN 2006 THEN year_2006
WHEN 2007 THEN year_2007
WHEN 2008 THEN year_2008
WHEN 2009 THEN year_2009
WHEN 2010 THEN year_2010
WHEN 2011 THEN year_2011
WHEN 2012 THEN year_2012
ELSE NULL END
AS number_of_earthquakes
FROM tutorial.worldwide_earthquakes earthquakes
CROSS JOIN (
SELECT year
FROM (VALUES (2000),(2001),(2002),(2003),(2004),(2005),(2006),
(2007),(2008),(2009),(2010),(2011),(2012)) v(year)
) years

Question 79

What does the below code do?

SELECT years.*,
earthquakes.magnitude,
CASE year
WHEN 2000 THEN year_2000
WHEN 2001 THEN year_2001
WHEN 2002 THEN year_2002
WHEN 2003 THEN year_2003
WHEN 2004 THEN year_2004
WHEN 2005 THEN year_2005
WHEN 2006 THEN year_2006
WHEN 2007 THEN year_2007
WHEN 2008 THEN year_2008
WHEN 2009 THEN year_2009
WHEN 2010 THEN year_2010
WHEN 2011 THEN year_2011
WHEN 2012 THEN year_2012
ELSE NULL END
AS number_of_earthquakes

Answer 79

“CASE year” goes down the column “year” and where it has value of 2000, it’s replaced with the corresponding value of that row in “year_2000” column (which is the number of earthquakes in year_2000) and so on till the end, then it’s given a column name and a new column is created that for each year and each magnitude shows the number of earthquakes. It’s like below and it’s for Pivoting columns to rows in the dataset:
year magnitude # of earthquakes
2000 8.0 to 9.9 1
2001 8.0 to 9.9 1
2002 8.0 to 9.9 0
2003 8.0 to 9.9 1
2004 8.0 to 9.9 2

Question 80

What does below code do? what does EXISTS clause do?
SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);

Answer 80

The SQL statement returns TRUE and lists the suppliers with a product price less than 20.

The EXISTS operator is used to test for the existence of any record in a subquery.

The EXISTS operator returns TRUE if the subquery returns one or more records.