PostgreSQL Flashcards
What steps do you want for cleaning string data?
Restrict capitalization in column names
Remove extra divider spaces in column names
make column names uniform
INITCAP()
SELECT INITCAPT(column)
Output: Hello Friend!
Fixes capitalization
REPLACE()
SELECT REPLACE(column, string_to_replace, replacement)
SELECT REPLACE(streets, ‘Main Street’, ‘St’);
Output: Main St
LPAD(input_string, length, fill_value)
Left-Pad a Column
SELECT LPAD(‘column’, 7, ‘X’);
OUTPUT: XXXX123
Prepending text values to a string. The fourth value has to be a string.
SELECT LPAD(‘123’, 5, ‘0’); – Output: ‘00123’
Like
Like is used for pattern matching using wildcards such as % or _
SELECT *
FROM employees
WHERE firstname LIKE ‘Ali%’ or firstname LIKE ‘_li’
%
Matches 0 or more characters
_
Matches exactly 1 character
Can use multiple _ to represent more than 1 character
REGEXP_REPLACE()
REGEXP_REPLACE(column, ‘regrex’, replacement, ‘g’)
‘g’ = global
DIFFERENCE()
DIFFERENCE is used to compare the SOUNDEX values of two strings and returns a score between 0 and 4:
DIFFERENCE(vehicle_color, ‘GRAY’) = 4;
SOUNDEX()
SOUNDEX is a phonetic algorithm used to match words or strings that sound similar in English. It converts a word to a code based on its pronunciation.
Example:
SOUNDEX(‘GRAY’) → G600
SOUNDEX(‘GREY’) → G600
EXPLAIN ANALYZE
can be used to provide insight into the execution plan of a SQL query. actually runs the query. use with caution.
EXPLAIN ANALYZE
SELECT * FROM patient WHERE age = 60;
EXPLAIN
the EXPLAIN command alone can be used to review the planned execution path without the associated overhead of query execution.
EXPLAIN
SELECT * FROM patient WHERE age = 60;
INDEX
CREATE INDEX index_title
ON table(column);
example:
CREATE INDEX idx_patient_age_cholesterol_restingbp
ON patient(age,cholesterol,restingbp);
Visualize how to query to confirm index exists
SELECT * FROM pg_indexes
WHERE tablename = ‘patient’
AND indexname = ‘idx_patient_age’;
Visualize how to extract a row data from a column, and create a new table, inserting the column data from an old table.
SELECT DISTINCT(chestpaintype) FROM patient;
CREATE TABLE IF NOT EXISTS chestpain (
id serial PRIMARY KEY,
type TEXT
);
INSERT INTO chestpain(type)
SELECT DISTINCT(chestpaintype) FROM patient;
Visualize how to alter a table with a foreign key
ALTER TABLE patient
ADD CONSTRAINT patient_chestpain_fk
FOREIGN KEY (chestpaintypeid) REFERENCES chestpain(id);
Visualize how to use INFORMATION_SCHEMA
is used to retrieve metadata about the columns of a table from the database.
Example:
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = ‘public’ #this is a default
AND table_name = ‘table’;
Visualize how to check for columns using INFORMATION_SCHEMA
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = ‘integer’;
Database transactions
A transaction is like a package of operations that are grouped together. Either all operations within the transaction succeed (committed) or none of them take effect (rolled back). It ensures ACID (Atomicity, Consistency, Isolation, Durability).
How do you create a database transaction?
Use BEGIN and COMMIT
BEGIN starts the transaction, and all statements are part until transaction is ended with COMMIT.
What is ACID?
Atomicity, Consistency, Isolation, Durability
ROLLBACK
If an error occurs or if you decide not to save the changes, you can use ROLLBACK instead of COMMIT.
ROLLBACK undoes all changes made during the transaction.
SERIAL
Create surrogate keys
CREATE TABLE table_name(
column_number SERIAL PRIMARY KEY
)
How to designate a foregin key
CREATE TABLE table_name(
column_name INT REFERENCES table(column) ON DELETE CASCADE)
When the column is deleted in the reference table, it automatically deletes when using ON DELETE CASCADE.
How do you use a foreign key explicity and set to NULL on delete?
CREATE TABLE orders (
ord_id SERIAL PRIMARY KEY,
prod_sku INT,
FOREIGN KEY (prod_sku) REFERENCES product(prod_sku) ON DELETE SET NULL
);
Break up a table to normalize it?
INSERT INTO normalized_table (col 1, col 2, col3)
SELECT DISTINCT col1,col2,col3 FROM big_table;
How do you update an existing constraint on a column?
1️⃣ Find the existing constraint name
SELECT conname FROM pg_constraint WHERE conrelid = ‘orders’::regclass AND contype = ‘c’;
2️⃣ Drop the old constraint
ALTER TABLE orders DROP CONSTRAINT chk_ord_priority;
3️⃣ Add the updated constraint
ALTER TABLE orders ADD CONSTRAINT chk_ord_priority CHECK (ord_priority IN (‘L’, ‘M’, ‘H’, ‘C’, ‘X’));
4️⃣ Fix invalid values if needed
UPDATE orders SET ord_priority = ‘L’ WHERE ord_priority NOT IN (‘L’, ‘M’, ‘H’, ‘C’, ‘X’);
What is pg_constraint?
pg_constraint is a system catalog table in PostgreSQL that stores all constraints for all tables in the database
What are the constraints stored in pg_constraint?
Primary Keys (p)
Foreign Keys (f)
Unique Constraints (u)
Check Constraints (c)
Importing data into PostgreSQL
- create the table
- Use the copy command
COPY my_table (col1, col2, col3)
FROM ‘/path/to/file.csv’
DELIMITER ‘,’
CSV HEADER;
INTEGER GENERATED ALWAY AS IDENTITY
CREATE TABLE table_name (
col1 INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 100 INCREMENT BY 10) PRIMARY KEY,
col2 VARCHAR(100)
);
What happen when you join two tables where the column names are the same?
You have to use dot notation?
ex.
– Select name fields (with alias) and region
SELECT cities.name AS city , countries.name AS country, region
FROM cities
INNER JOIN countries
ON cities.country_code = countries.code;
USING
a join operations when both tables share a column with the same name.
Instead of:
SELECT * FROM table1 JOIN table2 ON table1.col = table2.col;
you can write:
SELECT * FROM table1 JOIN table2 USING(col);
Visualize using multiple joins
SELECT A., B., C.*
FROM A
JOIN B ON A.id = B.a_id
JOIN C ON B.id = C.b_id;
T or F: You can mix different types of joins (INNER, LEFT, RIGHT, FULL) depending on your needs.
True.
SELECT A., B., C.*
FROM A
LEFT JOIN B ON A.id = B.a_id
INNER JOIN C ON B.id = C.b_id;
T or F: Order affects your result when using an INNER or LEFT/RIGHT join.
False. For inner joins, the order is generally interchangeable (thanks to the associative property of inner joins). However, for outer joins, the order can affect which rows are retained.
Visualize using on multiple joins
SELECT *
FROM A
JOIN B USING(common_column)
JOIN C USING(common_column);
Using AND for multiple joins
SELECT*
FROM left_table
INNER JOIN right_table
ON left_table.id = right_table.id
AND left_table.date = right_table.date
T or F: When converting a Left to a Right Join, you only have to change the join.
False. when converting a LEFT JOIN to a RIGHT JOIN, change both the type of join and the order of the tables to get equivalent results. You would get different results if you only changed the table order. The order of fields you are joining ON still does not matter.
What query
Removes dead tuples (deleted rows still taking up space).
Reclaims disk space.
Updates query statistics at the same time
VACUUM ANALYZE;
How do you update statistics?
ANALYZE;
Where do you place the foreign key on a table?
1-to-1 (1:1): In the table that depends on the other.
1-to-Many (1:M): In the table on the “Many” side.
Many-to-Many (M:M): Create a junction table with two foreign keys.
How do you decide if you should add indexes onto a table?
If it has low sparsity (fewer unique values, meaning more repeated values).
If it is frequently used in joins and aggregations.
UNION
UNION takes two tables as input, and returns all records from both tables, excluding duplicates
SELECT* FROM left_table
UNION
SELECT* FROM right_table;
UNION ALL
UNION ALL takes two tables and returns all records from both tables, including duplicates
SELECT* FROM left_table
UNION ALL
SELECT* FROM right_table;
Begin your query by selecting all fields from economies2015.
Create a second query that selects all fields from economies2019.
Perform a set operation to combine the two queries you just created, ensuring you do not return duplicates.
– Select all fields from economies2015
SELECT * FROM economies2015
– Set operation
UNION
– Select all fields from economies2019
SELECT * FROM economies2019
ORDER BY code, year;
INTERSECT
INTERSECT is a set operation that returns only the common rows between two queries without duplicates
SELECT col FROM table1
INTERSECT
SELECT col FROM table2;
What is the difference between JOIN and INTERSECT
INTERSECT requires the tables to have the same number of columns and matching data types. While JOIN does not require queries to have the same columns or data types.
EXCEPT
Returns only the tables in the left table that aren’t in the right table.
SELECT col FROM table1
EXCEPT
SELECT col FROM table2;
Semi Join
it only checks for existence of the columns and data in another table. Think of it as a JOIN that doesn’t include extra columns from the second table.
What would this Semi Join return?
SELECT e.name, e.department
FROM employees e
WHERE EXISTS (
SELECT 1 FROM managers m
WHERE e.manager_id = m.id
);
Selects employees who have a manager in the managers table.
Returns only data from employees (no extra columns from managers).
If a manager exists, the row is included.
Anti-Join
returns rows from the first table that have NO match in the second table.
SELECT e.name, e.department
FROM employees e
WHERE NOT EXISTS (
SELECT 1 FROM managers m
WHERE e.manager_id = m.id
);
Subquery
subquery is a query inside another query.
It is enclosed in parentheses.
It can return a single value (scalar subquery) or multiple rows (table subquery).
Visualize a subquery with IN (Semi Join)
SELECT name, department
FROM employees
WHERE department IN (
SELECT dept_name FROM departments
);
Visualize a subquery NOT IN (anti join
SELECT name, department
FROM employees
WHERE department NOT IN (
SELECT dept_name FROM departments
);
Identify the languages spoken in the Middle East. The languages table contains information about languages and countries, but not the region. Use a semi join on the languages and countries table to get the “name” for the languages spoken in the “region” Middle East
SELECT DISTINCT name
FROM languages
– Add syntax to use bracketed subquery below as a filter
WHERE code IN
(SELECT code
FROM countries
WHERE region = ‘Middle East’)
ORDER BY name;
Write a query that returns the names from the countries table where the continent is Oceania that are not in the currencies table using the country code
SELECT code, name
FROM countries
WHERE continent = ‘Oceania’
– Filter for countries not included in the bracketed subquery
AND code NOT IN
(SELECT code
FROM currencies);
Using the countries and cities tables, Return the name, country_code and urbanarea_pop for all capital cities in the countries table
– Select relevant fields from cities table
SELECT name, country_code, urbanarea_pop
FROM cities
– Filter using a subquery on the countries table
WHERE name IN
(
select capital
FROM countries
)
ORDER BY urbanarea_pop DESC;
Write a LEFT JOIN with countries on the left and the cities on the right, joining on country code.
In the SELECT statement of your join, include country names as country, and count the cities in each country, aliased as cities_num.
Sort by cities_num (descending), and country (ascending), limiting to the first nine records.
– Find top nine countries with the most cities
SELECT countries.name AS country, COUNT(*) AS cities_num
FROM countries
LEFT JOIN cities
ON countries.code = cities.country_code
GROUP BY country
– Order by count of cities as cities_num
ORDER BY cities_num DESC, country
LIMIT 9;
Use the WHERE clause to enable the correct country codes to be matched in the cities and countries columns.
SELECT countries.name AS country,
– Subquery that provides the count of cities
(SELECT COUNT(*)
FROM cities
WHERE cities.country_code = countries.code) AS cities_num
FROM countries
ORDER BY cities_num DESC, country
LIMIT 9;
T or F: This code will not produce an error
SELECT code, COUNT(*) AS local_name
FROM languages
False. , you need a GROUP BY clause to tell SQL how to group the data before applying the aggregation. Otherwise it will not know what to do with code.
T or F: Group By is always required when using aggregate functions like COUNT(), SUM(), AVG(), etc.
False. GROUP BY is required when using aggregate functions with non-aggregated columns included in a SELECT statement.
Wheren do you want to use a subquery in a FROM clause instead of WHERE?
You need to aggregate data (like COUNT(*)) before filtering.
You need to join with another table.
The subquery returns multiple columns (which is not possible in a WHERE clause).
Using the the countries and languages table, select local_name from countries, with the aliased lang_num from your subquery and use WHERE to match the code field from countries and sub.
– Select local_name and lang_num from appropriate tables
SELECT local_name, sub.lang_num
FROM countries,
(SELECT code, COUNT(*) AS lang_num
FROM languages
GROUP BY code) AS sub
– Where codes match
WHERE countries.code = sub.code
ORDER BY lang_num DESC;
Why would this code fail?
SELECT local_name
FROM countries
WHERE code IN (
SELECT code
FROM languages
GROUP BY code
HAVING COUNT(*) > 1
)
ORDER BY lang_num DESC;
WHERE can’t reference an aggregated column (COUNT(*)) directly unless inside HAVING.
You can’t order by lang_num because it isn’t in SELECT!
💡 Use a CASE statement to classify match results based on scores!
WHEN home_goal > away_goal → “Home win!”
WHEN home_goal < away_goal → “Home loss :(“
ELSE → “Tie”
SELECT
– Select the date of the match
date,
– Identify home wins, losses, or ties
CASE WHEN home_goal > away_goal THEN ‘Home win!’
WHEN home_goal < away_goal THEN ‘Home loss :(‘
ELSE ‘Tie’ END AS outcome
FROM matches_spain;
Use a CASE statement and LEFT JOIN to track Barcelona’s home game results!
LEFT JOIN teams_spain → Connects match data with team names.
CASE statement → Determines match outcome:
Win if home_goal > away_goal
Loss if home_goal < away_goal
Tie otherwise
WHERE m.hometeam_id = 8634 → Filters for Barcelona’s home matches.
SELECT
m.date,
t.team_long_name AS opponent,
– Complete the CASE statement with an alias
CASE WHEN m.home_goal > m.away_goal THEN ‘Barcelona win!’
WHEN m.home_goal < m.away_goal THEN ‘Barcelona loss :(‘
ELSE ‘Tie’ END AS outcome
FROM matches_spain AS m
LEFT JOIN teams_spain AS t
ON m.awayteam_id = t.team_api_id
– Filter for Barcelona as the home team
WHERE m.hometeam_id = 8634;
Create a CASE statement that identifies the id of matches played in the 2012/2013 season from the Country’s table.
Join the country’s table to the match table. Specify that you want ELSE values to be NULL.
Wrap the CASE statement in a COUNT function and group the query by the country alias.
SELECT
c.name AS country,
– Count games from the 2012/2013 season
COUNT(CASE WHEN m.season = ‘2012/2013’
THEN m.id ELSE NULL END) AS matches_2012_2013
FROM country AS c
LEFT JOIN match AS m
ON c.id = m.country_id
– Group by country name alias
GROUP BY country;
VISUALIZE USING A COUNT statement in CASE to get the years aliased as matches.
SELECT
c.name AS country,
– Count matches in each of the 3 seasons
COUNT(CASE WHEN m.season = ‘2012/2013’ THEN m.id END) AS matches_2012_2013,
COUNT(CASE WHEN m.season = ‘2013/2014’ THEN m.id END) AS matches_2013_2014,
COUNT(CASE WHEN m.season = ‘2014/2015’ THEN m.id END) AS matches_2014_2015
FROM country
Visualize using case statements to return ones and 0’s so you can get the sums of a true and false scenarios.
SELECT
c.name AS country,
– Sum the total records in each season where the home team won
SUM(CASE WHEN m.season = ‘2012/2013’ AND m.home_goal > m.away_goal
THEN 1 ELSE 0 END) AS matches_2012_2013,
SUM(CASE WHEN m.season = ‘2013/2014’ AND m.home_goal > m.away_goal
THEN 1 ELSE 0 END) AS matches_2013_2014,
SUM(CASE WHEN m.season = ‘2014/2015’ AND m.home_goal > m.away_goal
THEN 1 ELSE 0 END) AS matches_2014_2015
FROM country AS c
LEFT JOIN match AS m
ON c.id = m.country_id
– Group by country name alias
GROUP BY country;
How can you calculate the percentage of tied games in a season using a CASE statement?
Uses AVG + CASE to calculate the proportion of games that ended in a tie:
Tied match (home_goal = away_goal) → 1
Non-tied match (home_goal != away_goal) → 0
The average of these values gives the percentage of ties.
ROUND(AVG(…), 2) ensures the percentage is rounded to 2 decimal places
SELECT
c.name AS country,
– Round the percentage of tied games to 2 decimal points
ROUND(AVG(CASE WHEN m.season=’2013/2014’ AND m.home_goal = m.away_goal THEN 1
WHEN m.season=’2013/2014’ AND m.home_goal != m.away_goal THEN 0
END),2) AS pct_ties_2013_2014,
ROUND(AVG(CASE WHEN m.season=’2014/2015’ AND m.home_goal = m.away_goal THEN 1
WHEN m.season=’2014/2015’ AND m.home_goal != m.away_goal THEN 0
END),2) AS pct_ties_2014_2015
How do you filter a table using a subquery in the WHERE clause?
Selects team_long_name and team_short_name from the team table.
Uses a subquery to find all hometeam_IDs from the match table where the home team scored 8 or more goals.
Filters the team table to show only teams that appear in the subquery.
SELECT
– Select the team long and short names
team_long_name,
team_short_name
FROM team
– Filter for teams with 8 or more home goals
WHERE team_api_id IN
(SELECT hometeam_ID
FROM match
WHERE home_goal >= 8);
Why is EXISTS more efficient than IN in large datasets?
EXISTS stops evaluating after finding the first matching record, making it more efficient than IN in large datasets.
What does this code do?
SELECT
c.name AS country_name,
COUNT(sub.id) AS matches
FROM country AS c
INNER JOIN (SELECT country_id, id
FROM match
WHERE (home_goal + away_goal) >= 10) AS sub
ON c.id = sub.country_id
GROUP BY country_name;
It helps filter matches first before joining, which can improve readability. It selects country_id (to track which country the match belongs to). It filters only matches where the total goals scored (home + away) is 10 or more.
This joins the subquery (sub) with the country table (c): The INNER JOIN ensures that only countries with at least one high-scoring match (10+ goals) appear in the final results.This ensures we count matches per country, rather than returning one big total.
Visualize a query that:
✔ AVG() Function: Calculates the average goals scored in matches.
✔ GROUP BY: Groups by stage to calculate stage-specific averages.
✔ WHERE Clause: Filters only stages where the average goals exceed the season average.
✔ ROUND(): Rounds the output for better readability.
SELECT
s.stage,
ROUND(s.avg_goals,2) AS avg_goals
FROM
(SELECT
stage,
AVG(home_goal + away_goal) AS avg_goals
FROM match
WHERE season = ‘2012/2013’
GROUP BY stage) AS s
WHERE
s.avg_goals > (SELECT AVG(home_goal + away_goal)
FROM match WHERE season = ‘2012/2013’);
CTE
WITH cte_name AS (
SELECT column1, column2
FROM table_name
WHERE condition
)
SELECT * FROM cte_name;
What questions do you ask yourself when setting up CTE?
✅ What is the main goal of the query?
✅ What tables do I need?
✅ What filtering conditions do I need?
✅ What calculations do I need?
How Does SQL Know the Difference Between a Subquery and a Correlated Query?
Even though subqueries and correlated queries use similar syntax (nested queries inside parentheses), SQL knows the difference based on whether the subquery references the main query.
What is a subquery
A subquery is a query that is executed first, and its result is passed to the main query.
It is independent of the outer query.
Visualize a correlated query that finds the title and ratings of movies where the rating is above average.
SELECT title, rating
FROM movies m1
WHERE rating > (
SELECT AVG(rating)
FROM movies m2
WHERE m1.movie_id = m2.movie_id
);
OVER()
The OVER() function is used with window functions like RANK(), DENSE_RANK(), ROW_NUMBER(), and SUM(). It allows calculations over a specific set of rows while keeping all the rows in the result.
What are window functions?
A window partition in SQL is a way to divide rows into groups (partitions) within a result set and apply window functions (like ROW_NUMBER(), RANK(), AVG(), SUM(), etc.) to each partition separately.
How does the OVER() clause work with the WINDOW function?
The OVER() clause defines how a window function is applied to partitions.
Visualize the basic syntax for a window function used with over()
SELECT column_name,
WINDOW_FUNCTION() OVER (PARTITION BY column_name ORDER BY column_name) AS alias
What are examples of window functions?
SUM(), AVG(), RANK(), ROW_NUMBER(), DENSE_RANK(), LAG()
PARTITION BY
keep all rows, but calculations are applied within each group
Sliding Window
Focus on a small set of rows at a time.
✅ The window moves row by row, changing which rows are visible.
✅ You can calculate things like moving averages, running totals, or rankings.
Visualize using ROWS BETWEEN <start> AND <finish></finish></start>
SELECT
col1 , co2,
AVG(col3) OVER (
ORDER BY col4
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS col_avg
FROM table;
Keywords to use with ROWS BETWEEN <start> AND <finish></finish></start>
PRECEDING - Rows before the current row
FOLLOWING - Rows after the current row
UNBOUNDED PRECEDING -Starts from the very first row
UNBOUNDED FOLLOWING - Includes all future rows
CURRENT ROW - The row you’re looking at now
LAG()
a window function that retrieves data from the previous row in a specified order without collapsing rows.
Visualize the basic syntax for LAG()
LAG(column_name, offset, default_value) OVER (ORDER BY column_name)
LEAD()
A window function that looks ahead
LEAD(Column, n)
N is the number of rows to look ahead
FIRST_VALUE(), LAST_VALUE
Window functions that always check either the first value or the last value
FIRST_VALUE(column)
LAST_VALUE(column)
What must we type with FIRST_VALUE and LAST_VALUE() to get mySQL to look at the entire dataset?
UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING
LAST_VALUE(student) OVER (
ORDER BY spot ASC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
RANK() WITH partition vs. RANK() without
RANK() WITHOUT Partition 🐰 -Ranks all rows together as one big group.
Example: 🥇(1), 🥈(2), 🥈(2), 🥉(4)
RANK() WITH Partition 🍎 -Restarts ranks for each smaller group.
Example (Partition by class):
Class A: 🥇(1), 🥈(2), 🥉(3)
Class B: 🥇(1), 🥈(2), 🥈(2)
RANK() vs. DENSE_RANK()
RANK() - Skips numbers if there’s a tie (1,2,2,4…)
DENSE_RANK() - Doesn’t skip numbers if there’s a tie (1,2,2,3…)
What is paging?
If you have lots of data (like thousands of names), you split it into smaller pages to easily read and manage it.
NTILE()
To easily split data into equal groups (like finding the top 25% of students).
To create pages when showing data on websites (showing 10 items per page).
To quickly group rankings (top group, middle groups, lower groups).
Visualize using a CTE() named Events and split the column olympic_event into 111 unique groups and order by olympic_event in alphabetical order.
WITH Events AS (
SELECT DISTINCT olympic_event
FROM Summer_Medals)
SELECT
— Split up the distinct events into 111 unique groups
olympic_event,
NTILE(111) OVER (ORDER BY olympic_event ASC) AS Page # split and number your events into pages.
FROM Events
ORDER BY olympic_event ASC; # how the final results are shown on your screen
Frame
A frame tells SQL how many rows to look at when using a window function like SUM(), AVG(), or LAST_VALUE().
What is the default Frame?
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
🔹 Starts from the first row
🔹 Ends at the current row
What does this frame mean?
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
🔹 Starts at the first row
🔹 Ends at the last row
n PRECEDING
Look at n rows before current row
CURRENT ROW
Just this row
n FOLLOWING
Look at n rows after current row
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
Looks at 3 rows before + current row
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
Looks at 1 row before + 1 row after
ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING
Looks at rows 5 to 1 before current row
ROWS BETWEEN X PRECEDING AND Y FOLLOWING
we’re defining a sliding window over which calculations (like AVG()) happen.
What does ROWS BETWEEN do in a window function?
It counts exact physical rows, no matter what the values are.
Each row is treated individually — even if they have the same value.
What does RANGE BETWEEN do in a window function?
It includes all rows with the same value in the ORDER BY column.
Duplicates are grouped together as a single frame.
How does ROWS differ from RANGE in SQL window functions?
ROWS = exact row positions (more precise 🧮)
RANGE = value-based grouping (less predictable if duplicates)
What does 2 PRECEDING mean?
Includes the two years before the current row.
T or F: In moving averages, we look at past performance (trailing averages)
True. A forward-looking moving average is less typical than trailing averages.
PRECEDING vs FOLLOWING
🧭 Use PRECEDING for trailing averages (past-focused)
🧭 Use FOLLOWING for future-facing windows (less common for moving averages)
What does CROSSTAB do?
It transforms rows into columns — like a pivot table — to summarize data.
What extension must be enabled to use CROSSTAB?
CREATE EXTENSION IF NOT EXISTS tablefunc;
Use CROSSTAB on this query:
SELECT
Sport, Year, COUNT(*) AS Golds
FROM Summer_Medals
WHERE
Medal = ‘Gold’
AND Country = ‘USA’
AND Year IN (2008, 2012, 2016)
GROUP BY Sport, Year
ORDER BY Sport, Year;
CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT * FROM CROSSTAB($$
SELECT
Sport, Year, COUNT(*) AS Golds
FROM Summer_Medals
WHERE
Medal = ‘Gold’ AND Country = ‘USA’ AND Year IN (2008, 2012, 2016)
GROUP BY Sport, Year ORDER BY Sport, Year
$$) AS ct (
sport VARCHAR,
“2008” INT, “2012” INT, “2016” INT
);
what does :: mean?
is shorthand for casting a value from one type to another.
It’s the same as writing:
CAST(expression AS TYPE)
Visualize using :: in mySQL
‘5’::INTEGER → turns the string ‘5’ into the number 5
42::TEXT → turns the number 42 into the string ‘42’
visualize using CUBE()
SELECT Flavor, Day, SUM(Sales)
FROM IceCreamSales
GROUP BY CUBE(Flavor, Day)
visualize using ROLLUP()
SELECT Flavor, Day, SUM(Sales)
FROM IceCreamSales
GROUP BY ROLLUP(Flavor, Day)
Q: What does ROLLUP() do?
Adds subtotals and a grand total, going from left to right in your GROUP BY list.
Q: What does CUBE() do?
A: Adds all possible subtotals for every combo of columns, plus the grand total.
T or F: RollUP() is like building all possible combinations of totals, including side-to-side, up-and-down, and everything in between.
F. Cube() is. ROLLUP() is like totals going down a list, adding subtotals to a grand total.
Q: Can you use aggregations like COUNT() in a GROUP BY?
A: No — you must group by columns, then apply aggregations after.
Q: What if you want to group by an aggregated value?
A: Use a subquery or a CTE to calculate the aggregate first.
STRING_AGG()
STRING_AGG(column, separator) takes all the values of a column and concatenates them,with separator in between each value
INFORMATION_SCHEMA
It’s a built-in database that contains metadata — data about your data!
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = ‘your_table_name’;
What are the ways to access the information schema?
INFORMATION_SCHEMA.TABLES
INFORMATION_SCHEMA.COLUMNS
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
TIMESTAMP
Format: ‘YYYY-MM-DD HH:MM:SS’
Full combo of date and time
SELECT TIMESTAMP ‘2024-04-10 14:30:00’;
– ➝ April 10 at 2:30 PM
INTERVAL
Stores how much time, not when
Can be days, hours, minutes, etc.
SELECT INTERVAL ‘2 days 3 hours’;
– ➝ a time span of 2 days and 3 hours
TIME
Format: ‘HH:MM:SS’
Stores only the time — no date at all!
SELECT TIME ‘14:30:00’;
– ➝ 2:30 PM
DATE
1️⃣ DATE → 📅 Just the calendar date
Format: ‘YYYY-MM-DD’
Stores only the date — no time of day
SELECT DATE ‘2024-04-10’;
– ➝ April 10, 2024
How do you get the first item of an array in PostgreSQL?
array_col[1]
Visualize creating an array
INSERT INTO table VALUES
(‘val1’, ARRAY[‘A’, ‘B’, ‘C’]),
(‘val2’, ARRAY[‘B’, ‘C’, ‘A’]);
A. ‘Trailers’ = ANY(special_features) vs B. special_features[1] = ‘Trailers’
A: Checks if ‘Trailers’ exists anywhere in the array — flexible and reliable.
B: Only checks if ‘Trailers’ is in the first spot of the array — can miss matches.
Visualize using the @>
WHERE array_name @> ARRAY[‘search text’]::type[]
Returns all rows where ‘search text’ is inside the tags array.
What does @> do in PostgreSQL?
It’s the contains operator — checks if the left array contains the right array’s values.
What does ARRAY[‘x’]::type[] do?
Creates a typed array (like TEXT[], INT[], etc.) with the values you want to look for.
EXTRACT(field FROM source)
Pulls a specific part (like year, month, day, etc.) from a DATE, TIMESTAMP, or INTERVAL.
SELECT EXTRACT(YEAR FROM TIMESTAMP ‘2024-04-10 14:30:00’);
– Returns: 2024
What does AGE() do in PostgreSQL?
SELECT AGE(DATE ‘2024-04-10’, DATE ‘2020-01-01’);
– Output: 4 years 3 mons 9 days
Calculates the exact time difference between two dates (or between a date and now), and returns it as an INTERVAL (like 2 years 5 mons 3 days). Subtracting gives you just days, AGE() gives you a full breakdown (years, months, days).
Q: Can you multiply INTERVAL ‘1 day’ * DATE ‘2024-04-10’?
❌ No. You can’t multiply intervals by dates. Only by numbers
INTERVAL ‘# day’ * num
– Multiply interval by a number
SELECT INTERVAL ‘1 day’ * 5;
– Result: 5 days
Visualize how to add 7 days to April 10, 2024
SELECT DATE ‘2024-04-10’ + INTERVAL ‘7 days’;
– Result: 2024-04-17
What does CURRENT_TIMESTAMP::timestamp do?
A: It removes the time zone part, returning a TIMESTAMP instead of TIMESTAMP WITH TIME ZONE.
What does CAST() do?
A: Converts one data type to another (e.g., string to integer).
CAST(value AS type)
Convert ‘42’ to a number:
CAST(‘42’ AS INTEGER)
– or –
‘42’::INTEGER
