2f: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)30:[{"id":"672ab9f359d9ffc9c53007075f522cb704208e586f217ea70eef80a9a5b6ac24621cf2fb9d813b0b69dfc054f1024439cc40586e70d626b2da30c6133b5b6842","href":"https://static.observableusercontent.com/files/672ab9f359d9ffc9c53007075f522cb704208e586f217ea70eef80a9a5b6ac24621cf2fb9d813b0b69dfc054f1024439cc40586e70d626b2da30c6133b5b6842","name":"california_stores.csv","type":"text/csv","lastModified":1686067073022.374,"size":1380},{"id":"0ab2b5a209427df5bd252d3e92b295d75da9255ee46d58c5be9dca5dbce2404a3a292680c80109285b36ef8531cf762014303cb3a6af9ce55b3aa94b1603dbb1","href":"https://static.observableusercontent.com/files/0ab2b5a209427df5bd252d3e92b295d75da9255ee46d58c5be9dca5dbce2404a3a292680c80109285b36ef8531cf762014303cb3a6af9ce55b3aa94b1603dbb1","name":"products-1.csv","type":"text/csv","lastModified":1686067073022.374,"size":3796},{"id":"d2de5ac62407741030d131dcd5e2358b697a28bd66b59c9be3ab1980805026b69e09b890e51fe860d21fe587ab303061e57501fb10bc3012758958ec94c4429c","href":"https://static.observableusercontent.com/files/d2de5ac62407741030d131dcd5e2358b697a28bd66b59c9be3ab1980805026b69e09b890e51fe860d21fe587ab303061e57501fb10bc3012758958ec94c4429c","name":"nevada_stores.csv","type":"text/csv","lastModified":1686067073022.374,"size":998},{"id":"1910f031d64b8be82c3ee121c372ca383b908b5f84b9694e90b3307b0235fb42987a306c1dc1a4892fa23877c7c77c793f283da3fcc335aeace802711c12b17c","href":"https://static.observableusercontent.com/files/1910f031d64b8be82c3ee121c372ca383b908b5f84b9694e90b3307b0235fb42987a306c1dc1a4892fa23877c7c77c793f283da3fcc335aeace802711c12b17c","name":"stores-1.csv","type":"text/csv","lastModified":1686067073022.374,"size":2464},{"id":"be89abb1e2f19efc6d7ea98d16245ad55e70f43c73222373ce5e76beb3561e9f1616a533c1dcdb8c1e6c767703264ac84491dc8a3b06cc4048a005db46ac2221","href":"https://static.observableusercontent.com/files/be89abb1e2f19efc6d7ea98d16245ad55e70f43c73222373ce5e76beb3561e9f1616a533c1dcdb8c1e6c767703264ac84491dc8a3b06cc4048a005db46ac2221","name":"nevada_daily_orders.csv","type":"text/csv","lastModified":1686067073022.374,"size":575834},{"id":"d7371630fba87e6b204fbd54ccf3e4b5d582589ee1743c6a2fdc191a829d6961b67a8e5596d63ed7a19cc7b404a2984efd8500ce85e120d636060eaedca87016","href":"https://static.observableusercontent.com/files/d7371630fba87e6b204fbd54ccf3e4b5d582589ee1743c6a2fdc191a829d6961b67a8e5596d63ed7a19cc7b404a2984efd8500ce85e120d636060eaedca87016","name":"orderItems-1.parquet","type":"application/octet-stream","lastModified":1686067073022.374,"size":6180623},{"id":"d0cad3c610647300f5e5a53d5788ce61aa256c79feee03f9c592d3465eb624fa878ad5eaf5a656354dab5d2040abbf8b7f20b320aca1c75e14bbf9722d3b9649","href":"https://static.observableusercontent.com/files/d0cad3c610647300f5e5a53d5788ce61aa256c79feee03f9c592d3465eb624fa878ad5eaf5a656354dab5d2040abbf8b7f20b320aca1c75e14bbf9722d3b9649","name":"california_daily_orders.csv","type":"text/csv","lastModified":1686067073022.374,"size":758158},{"id":"2a2cd2baa5c4eaf678f05cd13d90d4dda75008abc3d47c21179f051b8e018d10505d71fda7b212d91434803f29435e40b8cb945c4cf08f0ce536d2b12439a7a4","href":"https://static.observableusercontent.com/files/2a2cd2baa5c4eaf678f05cd13d90d4dda75008abc3d47c21179f051b8e018d10505d71fda7b212d91434803f29435e40b8cb945c4cf08f0ce536d2b12439a7a4","name":"customers-1.csv","type":"text/csv","lastModified":1686067073022.374,"size":1591967},{"id":"c653108ab176088cacbb338eaf2344c4f5781681702bd6afb55697a3f91b511c6686ff469f3e3a27c75400001a2334dbd39a4499fe46b50a8b3c278b7d2f7fb5","href":"https://static.observableusercontent.com/files/c653108ab176088cacbb338eaf2344c4f5781681702bd6afb55697a3f91b511c6686ff469f3e3a27c75400001a2334dbd39a4499fe46b50a8b3c278b7d2f7fb5","name":"orders_by_product_day.csv","type":"text/csv","lastModified":1686067073022.374,"size":2329284},{"id":"24e3ce238b4bf544c73eaf2e33aff298877266c45c7e2323f867a9f3ab9ada7da60acac1541c7dda76d0e280a90798c3507fd274de94262de7e42327bfc9f570","href":"https://static.observableusercontent.com/files/24e3ce238b4bf544c73eaf2e33aff298877266c45c7e2323f867a9f3ab9ada7da60acac1541c7dda76d0e280a90798c3507fd274de94262de7e42327bfc9f570","name":"orders-1.parquet","type":"application/octet-stream","lastModified":1686067073022.374,"size":26591479}] 2d:{"title":"SQL Course: Session 3","cells":[{"id":12,"value":"# SQL Course: Session 3","pinned":false,"hidden":false,"mode":"md"},{"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"},{"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"},{"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"},{"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"},{"id":17,"value":"---\n## Activity 1\n
","pinned":false,"hidden":false,"mode":"md"},{"id":440,"value":"### Aggregation\n- Count the number of orders by state","pinned":false,"hidden":false,"mode":"md"},{"id":446,"value":"SELECT stores.state, count(distinct orders.id)\n FROM stores\n Join orders\n ON stores.id = orders.storeId\nGROUP BY 1","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":442,"value":"- Sum the total revenue by state","pinned":false,"hidden":false,"mode":"md"},{"id":453,"value":"SELECT stores.state\n , count(distinct orders.id)\n , sum(orders.total) as revenue\n FROM stores\n Join orders\n ON stores.id = orders.storeId\nGROUP BY 1","pinned":true,"hidden":false,"mode":"sql","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"},{"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\n , mode(orders.nItems) as mode\n FROM orders\n ","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":25,"value":"---\n## Activity 2\n
","pinned":false,"hidden":false,"mode":"md"},{"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"},{"id":471,"value":"SELECT case when Category = 'Classic' then 'Classic'\n else 'Non_Classic' end as 'bucket', count(*) as num_products\n FROM products\nGROUP BY 1","pinned":true,"hidden":false,"mode":"sql","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"},{"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\"\nFROM orders\nGROUP BY 1","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":486,"value":"- Left Join to nevada_stores, and when nevada_stores is null, call it \"Non-Nevada stores\"","pinned":false,"hidden":false,"mode":"md"},{"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","output":"","database":"var:pizzeriaDB"},{"id":34,"value":"---\n## Activity 3\n
","pinned":false,"hidden":false,"mode":"md"},{"id":522,"value":"### Text functions\n- Return all products where the category is Classic or Specialty, using **in()**","pinned":false,"hidden":false,"mode":"md"},{"id":524,"value":"SELECT * \nFROM products\nWHERE category in ('Classic','Specialty')","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":506,"value":"- Return all the products where the ingredients include tomatoes.","pinned":false,"hidden":false,"mode":"md"},{"id":504,"value":"SELECT Name, ingredients\nFROM products\nWHERE ingredients like ('Tomato%')","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":532,"value":"- Do the inverse","pinned":false,"hidden":false,"mode":"md"},{"id":530,"value":"","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":536,"value":"- Concatenate the pizza name with the size","pinned":false,"hidden":false,"mode":"md"},{"id":538,"value":"SELECT concat(Name, ' ' , Size )\nFROM products","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":542,"value":"- Remove \"Pizza\" in the name","pinned":false,"hidden":false,"mode":"md"},{"id":544,"value":"SELECT replace(Name, 'Pizza', ' ') FROM products","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":39,"value":"---\n## Activity 4\n
","pinned":false,"hidden":false,"mode":"md"},{"id":555,"value":"### Date functions\n- Count the number of orders by year","pinned":false,"hidden":false,"mode":"md"},{"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","output":"","database":"var:pizzeriaDB"},{"id":562,"value":"- Count the number of orders by day of week","pinned":false,"hidden":false,"mode":"md"},{"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","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"},{"id":583,"value":"","pinned":true,"hidden":false,"mode":"sql","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"},{"id":590,"value":"","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":57,"value":"---\n## Wrap Up\nTest your knowledge from session one with these short questions.","pinned":false,"hidden":false,"mode":"md"},{"id":58,"value":"$2f","pinned":false,"hidden":false,"mode":"ojs"},{"id":59,"value":"

","pinned":false,"hidden":false,"mode":"md"},{"id":61,"value":"

","pinned":false,"hidden":false,"mode":"md"},{"id":62,"value":"---\n## Appendix","pinned":false,"hidden":false,"mode":"md"},{"id":63,"value":"import {note} from \"@observablehq/callout-boxes\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":64,"value":"import { showMe } from \"@observablehq/show-me\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":65,"value":"import {testYourKnowledge} from \"@observablehq/test-your-knowledge-cards\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":66,"value":"color = d3.schemeBlues[7]","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W30","stdlib":"1","titling":"auto"}