2b:null 2c:["$","title",null,{"children":"SQL Course: Session 3 | guypporter | Observable"}] 31:T6b1,testYourKnowledge([ { title: 'Aggregation', backText: [`There are many stastical functions in most SQL dialects! You will need to check for the version of SQL you're using, but in DuckDB, most summary statistic functions are available.`, `Many newer SQL dialects offer more statistical functions, and many allow you to write your own custom functions as well.`], question: "SQL is great for counting, but offers no statistical functions.", answer: false }, { title: 'Concat', backText: ['Concat is short for concatenate, and is a common function you will see in many programming languages or in tools like Excel.', 'In formal language theory and computer programming, string concatenation is the operation of joining character strings end-to-end.'], question: 'Concat is short for concatenate.', answer: true }, { title: 'Conditionals', backText: [`Conditionals are common in SQL, and often used as way to handle missing values or bucketing results by custom logic.`, `There are many conditional functions in SQL, and some of the most commonly used ones are CASE statements, Coalesce(), and IsNull()`], question: `While it's possible to do a conditional in SQL, it's rarely used.`, answer: false }, { title: 'Dates', backText: [`Date_trunc() and date_part() both take a date and transforms it to round or extract the desired information, like the month name or year.`, `Date_trunc() will return a valid truncated date, while date_part() returns the part of the date, usually as a number.`], question: `Date_trunc() and date_part() do essentially the same thing, though one returns a valid date and the other does not.`, answer: true }, ], 300, 300)32:[{"id":"d7371630fba87e6b204fbd54ccf3e4b5d582589ee1743c6a2fdc191a829d6961b67a8e5596d63ed7a19cc7b404a2984efd8500ce85e120d636060eaedca87016","href":"https://static.observableusercontent.com/files/d7371630fba87e6b204fbd54ccf3e4b5d582589ee1743c6a2fdc191a829d6961b67a8e5596d63ed7a19cc7b404a2984efd8500ce85e120d636060eaedca87016","name":"orderItems-1.parquet","type":"application/octet-stream","lastModified":1689346317169.541,"size":6180623},{"id":"d0cad3c610647300f5e5a53d5788ce61aa256c79feee03f9c592d3465eb624fa878ad5eaf5a656354dab5d2040abbf8b7f20b320aca1c75e14bbf9722d3b9649","href":"https://static.observableusercontent.com/files/d0cad3c610647300f5e5a53d5788ce61aa256c79feee03f9c592d3465eb624fa878ad5eaf5a656354dab5d2040abbf8b7f20b320aca1c75e14bbf9722d3b9649","name":"california_daily_orders.csv","type":"text/csv","lastModified":1689346317169.541,"size":758158},{"id":"672ab9f359d9ffc9c53007075f522cb704208e586f217ea70eef80a9a5b6ac24621cf2fb9d813b0b69dfc054f1024439cc40586e70d626b2da30c6133b5b6842","href":"https://static.observableusercontent.com/files/672ab9f359d9ffc9c53007075f522cb704208e586f217ea70eef80a9a5b6ac24621cf2fb9d813b0b69dfc054f1024439cc40586e70d626b2da30c6133b5b6842","name":"california_stores.csv","type":"text/csv","lastModified":1689346317169.541,"size":1380},{"id":"0ab2b5a209427df5bd252d3e92b295d75da9255ee46d58c5be9dca5dbce2404a3a292680c80109285b36ef8531cf762014303cb3a6af9ce55b3aa94b1603dbb1","href":"https://static.observableusercontent.com/files/0ab2b5a209427df5bd252d3e92b295d75da9255ee46d58c5be9dca5dbce2404a3a292680c80109285b36ef8531cf762014303cb3a6af9ce55b3aa94b1603dbb1","name":"products-1.csv","type":"text/csv","lastModified":1689346317169.541,"size":3796},{"id":"d2de5ac62407741030d131dcd5e2358b697a28bd66b59c9be3ab1980805026b69e09b890e51fe860d21fe587ab303061e57501fb10bc3012758958ec94c4429c","href":"https://static.observableusercontent.com/files/d2de5ac62407741030d131dcd5e2358b697a28bd66b59c9be3ab1980805026b69e09b890e51fe860d21fe587ab303061e57501fb10bc3012758958ec94c4429c","name":"nevada_stores.csv","type":"text/csv","lastModified":1689346317169.541,"size":998},{"id":"1910f031d64b8be82c3ee121c372ca383b908b5f84b9694e90b3307b0235fb42987a306c1dc1a4892fa23877c7c77c793f283da3fcc335aeace802711c12b17c","href":"https://static.observableusercontent.com/files/1910f031d64b8be82c3ee121c372ca383b908b5f84b9694e90b3307b0235fb42987a306c1dc1a4892fa23877c7c77c793f283da3fcc335aeace802711c12b17c","name":"stores-1.csv","type":"text/csv","lastModified":1689346317169.541,"size":2464},{"id":"be89abb1e2f19efc6d7ea98d16245ad55e70f43c73222373ce5e76beb3561e9f1616a533c1dcdb8c1e6c767703264ac84491dc8a3b06cc4048a005db46ac2221","href":"https://static.observableusercontent.com/files/be89abb1e2f19efc6d7ea98d16245ad55e70f43c73222373ce5e76beb3561e9f1616a533c1dcdb8c1e6c767703264ac84491dc8a3b06cc4048a005db46ac2221","name":"nevada_daily_orders.csv","type":"text/csv","lastModified":1689346317169.541,"size":575834},{"id":"2a2cd2baa5c4eaf678f05cd13d90d4dda75008abc3d47c21179f051b8e018d10505d71fda7b212d91434803f29435e40b8cb945c4cf08f0ce536d2b12439a7a4","href":"https://static.observableusercontent.com/files/2a2cd2baa5c4eaf678f05cd13d90d4dda75008abc3d47c21179f051b8e018d10505d71fda7b212d91434803f29435e40b8cb945c4cf08f0ce536d2b12439a7a4","name":"customers-1.csv","type":"text/csv","lastModified":1689346317169.541,"size":1591967},{"id":"c653108ab176088cacbb338eaf2344c4f5781681702bd6afb55697a3f91b511c6686ff469f3e3a27c75400001a2334dbd39a4499fe46b50a8b3c278b7d2f7fb5","href":"https://static.observableusercontent.com/files/c653108ab176088cacbb338eaf2344c4f5781681702bd6afb55697a3f91b511c6686ff469f3e3a27c75400001a2334dbd39a4499fe46b50a8b3c278b7d2f7fb5","name":"orders_by_product_day.csv","type":"text/csv","lastModified":1689346317169.541,"size":2329284},{"id":"24e3ce238b4bf544c73eaf2e33aff298877266c45c7e2323f867a9f3ab9ada7da60acac1541c7dda76d0e280a90798c3507fd274de94262de7e42327bfc9f570","href":"https://static.observableusercontent.com/files/24e3ce238b4bf544c73eaf2e33aff298877266c45c7e2323f867a9f3ab9ada7da60acac1541c7dda76d0e280a90798c3507fd274de94262de7e42327bfc9f570","name":"orders-1.parquet","type":"application/octet-stream","lastModified":1689346317169.541,"size":26591479}] 2f:{"id":"36aa39c7de0a041d","slug":null,"title":"SQL Course: Session 3","thumbnail":null,"access_level":"public","create_time":"$D2023-07-14T14:51:57.169Z","update_time":"$D2023-07-14T14:51:57.169Z","view_time":null,"first_public_time":"$D2023-07-14T14:51:57.169Z","has_unpublished_edits":null,"fork":{"id":"5067c9bc9875ffb1","slug":"sql-course-session-3","title":"SQL Course: Session 3","owner":{"id":"f35c755083683fe5","login":"observablehq","name":"Observable"}},"stars":0,"starred":false,"owner":{"type":"team","id":"cd0f0db407c448c2","login":"guypporter","name":"","avatar_url":"https://avatars-next.observableusercontent.com/9ab18f6b4d711577698d0a5cd44fe390fa4b4b41f5c9cbf800ff943831c8e048","tier":"starter_2024"},"version":689,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"d91a185d33d8f315","login":"guypporter","name":"","avatar_url":"https://avatars-next.observableusercontent.com/9ab18f6b4d711577698d0a5cd44fe390fa4b4b41f5c9cbf800ff943831c8e048","tier":null}],"members":[{"type":"individual","id":"d91a185d33d8f315","login":"guypporter","name":"","avatar_url":"https://avatars-next.observableusercontent.com/9ab18f6b4d711577698d0a5cd44fe390fa4b4b41f5c9cbf800ff943831c8e048","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":0,"collection":null,"type":"notebook","body":{"titling":"auto","title":"SQL Course: Session 3","cells":[{"id":12,"value":"# SQL Course: Session 3","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":13,"value":"- [Session 3 slide deck](https://docs.google.com/presentation/d/1X-Aur0GcEbJxismHjQO_uIyhJcJIGoNDAergp2edTVY/edit?usp=sharing)","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":14,"value":"For the hands-on portion of this course, we'll use the [Pizza Paradise](https://observablehq.com/@observablehq/pizza-paradise-data) dataset. To improve performance, I've recreated the database in this notebook.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":15,"value":"pizzeriaDB = DuckDBClient.of({\n orders: FileAttachment(\"orders-1.parquet\"),\n stores: FileAttachment(\"stores-1.csv\"),\n customers: FileAttachment(\"customers-1.csv\"),\n products: FileAttachment(\"products-1.csv\"),\n order_items: FileAttachment(\"orderItems-1.parquet\"),\n // Views\n orders_by_product_day: FileAttachment(\"orders_by_product_day.csv\"),\n california_stores: FileAttachment(\"california_stores.csv\"),\n nevada_stores: FileAttachment(\"nevada_stores.csv\"),\n california_daily_orders: FileAttachment(\"california_daily_orders.csv\"),\n nevada_daily_orders: FileAttachment(\"nevada_daily_orders.csv\")\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":16,"value":"note(md`__Note:__ This is a large, realistic mock dataset, so it may take a few seconds for it to load in your notebook.`)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":17,"value":"---\n## Activity 1\n
","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":440,"value":"### Aggregation\n- Count the number of orders by state","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":446,"value":"SELECT stores.state\n , count(distinct orders.id)\n FROM stores\n JOIN orders\n ON stores.id = orders.storeId\n GROUP BY 1","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":442,"value":"- Sum the total revenue by state","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":453,"value":"SELECT stores.state\n , count(distinct orders.id) as num_orders\n , sum(orders.total) as revenue\n FROM stores\n JOIN orders\n ON stores.id = orders.storeId\n GROUP BY 1","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":444,"value":"- Find the min, max, avg, mode, and median number of items for each order","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":458,"value":"SELECT min(orders.nItems) as min_items\n , max(orders.nItems) as max_items\n , avg(orders.nItems) as avg_items\n , median(orders.nItems) as median_items\n , mode(orders.nItems) as mode_items\n FROM orders","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":25,"value":"---\n## Activity 2\n
","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":436,"value":"### Conditionals\n- Write a case statement that counts products into the buckets of 'Classic' and 'Non-Classic'","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":471,"value":"SELECT case when Category = 'Classic' then 'Classic'\n else 'Non-Classic' end as \"My Bucket\"\n , count(*) as num_products\n FROM products\n GROUP BY 1","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":478,"value":"- Bucket orders by the number of items into the following buckets and count their revenue.\n - 1 item\n - 2 & 3 items\n - 4+ items\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":481,"value":"SELECT case when nItems = 1 then '1 Item' \n when nItems = 2 OR nItems = 3 then '2-3 Items' \n when nItems > 3 then '4+ Items' \n end as \"bucket\"\n , sum(total) as \"revenue\"\n FROM orders\n GROUP BY 1","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":486,"value":"- Left Join to nevada_stores, count the number of orders, and when nevada_stores is null, call it \"Non-Nevada stores\"","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":488,"value":"SELECT coalesce(nevada_stores.state, 'Non-Nevada') as bucket\n , case when nevada_stores.state is null then 'Non-Nevada' \n else 'Nevada' end as \"case bucket\"\n , count(distinct orders.id) as num_orders\n FROM orders\n LEFT\n JOIN nevada_stores\n ON orders.storeId = nevada_stores.id\n GROUP BY 1, 2","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":34,"value":"---\n## Activity 3\n
","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":522,"value":"### Text functions\n- Return all products where the category is Classic or Specialty, using **in()**","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":524,"value":"SELECT * \n FROM products\n WHERE Category in ('Classic', 'Specialty')","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":506,"value":"- Return all the products where the ingredients include tomatoes.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":504,"value":"SELECT *\n FROM products\n WHERE Ingredients like '%omato%'","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":532,"value":"- Do the inverse","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":530,"value":"SELECT *\n FROM products\n WHERE Ingredients not like '%omato%'","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":536,"value":"- Concatenate the pizza name with the size","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":538,"value":"SELECT concat(Name, ' ', Size, ' is really tasty') as my_first_concat\n , Name || ' ' || Size as my_second_concat\n FROM products","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":542,"value":"- Remove \"Pizza\" in the name","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":544,"value":"SELECT replace(Name, 'Pizza', '') as replace_name\n , Name\n FROM products","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":39,"value":"---\n## Activity 4\n
","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":555,"value":"### Date functions\n- Count the number of orders by year","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":570,"value":"SELECT date_trunc('year', orderDate::date) as year_name\n , count(distinct orders.id) as num_orders\n FROM orders\n GROUP BY 1\n ORDER BY 1 ","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":562,"value":"- Count the number of orders by day of week","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":577,"value":"SELECT date_part('dayofweek', orderDate::date) as \"Day of Week\"\n , count(distinct orders.id) as num_orders\n FROM orders\n GROUP BY 1\n ORDER BY 1 ","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":580,"value":"- Convert that to a more human-readable format using a case statement, order by most popular days","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":583,"value":"SELECT case when date_part('dayofweek', orderDate::date) == 0 then 'Sunday'\n when date_part('dayofweek', orderDate::date) == 1 then 'Monday'\n when date_part('dayofweek', orderDate::date) == 2 then 'Tuesday'\n when date_part('dayofweek', orderDate::date) == 3 then 'Wednesday'\n when date_part('dayofweek', orderDate::date) == 4 then 'Thursday'\n when date_part('dayofweek', orderDate::date) == 5 then 'Friday'\n when date_part('dayofweek', orderDate::date) == 6 then 'Saturday' end as year_name\n , count(distinct id) as num_orders\n FROM orders \n GROUP BY 1\n ORDER BY 2 DESC","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":564,"value":"- Figure out the median number of days between customers first and last order","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":590,"value":"SELECT median(day_diff)\n FROM (\n SELECT customerId\n , date_diff('day', min(orderDate::date), max(orderDate::date)) as day_diff\n FROM orders\n GROUP BY 1\n )\n","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":57,"value":"---\n## Wrap Up\nTest your knowledge with these short questions.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":58,"value":"$31","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":59,"value":"

","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":61,"value":"

","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":62,"value":"---\n## Appendix","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":63,"value":"import {note} from \"@observablehq/callout-boxes\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":64,"value":"import { showMe } from \"@observablehq/show-me\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":65,"value":"import {testYourKnowledge} from \"@observablehq/test-your-knowledge-cards\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":66,"value":"color = d3.schemeBlues[7]","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W32","stdlib":"1"}} 30:"$2f:body"