sql

• common table expression (CTE): a named subquery. creates a temp table that only exists for the duration of one query

WITH cheap_products AS (
			SELECT id, name FROM products WHERE price < 10
)
SELECT * FROM cheap_products # in scope!

SELECT * FROM cheap_products # out of scope

• CTE scope —> CTEs only exist for the duration of the query that follows the CTE definition

WITH adults as (
		SELECT * from people
		WHERE age >= 18
), 
adult_names as (
		 SELECT name FROM adults
)
SELECT * from adult_names; # adult_names CTE is in scope

SELECT COUNT(*) from adult_names; # adult_names CTE is NOT in scope

• pyspark: python’s interface to apache spark
  • spark.sql(…) lets you run a sql statement and returns the result as a dataframe
• sql doesn’t guarantee row order unless you ORDER BY . such behavior can lead to flakey tests

| | lifetime | stored on disk | | ------------ | ----------------- | ----------------------- | | CTE | one sql statement | no, computed on the fly | | temp table | one session | yes, temporarily | | actual table | forever | yes | | view | forever | no, computed on the fly |