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":1686068139338.571,"size":1380},{"id":"0ab2b5a209427df5bd252d3e92b295d75da9255ee46d58c5be9dca5dbce2404a3a292680c80109285b36ef8531cf762014303cb3a6af9ce55b3aa94b1603dbb1","href":"https://static.observableusercontent.com/files/0ab2b5a209427df5bd252d3e92b295d75da9255ee46d58c5be9dca5dbce2404a3a292680c80109285b36ef8531cf762014303cb3a6af9ce55b3aa94b1603dbb1","name":"products-1.csv","type":"text/csv","lastModified":1686068139338.571,"size":3796},{"id":"d2de5ac62407741030d131dcd5e2358b697a28bd66b59c9be3ab1980805026b69e09b890e51fe860d21fe587ab303061e57501fb10bc3012758958ec94c4429c","href":"https://static.observableusercontent.com/files/d2de5ac62407741030d131dcd5e2358b697a28bd66b59c9be3ab1980805026b69e09b890e51fe860d21fe587ab303061e57501fb10bc3012758958ec94c4429c","name":"nevada_stores.csv","type":"text/csv","lastModified":1686068139338.571,"size":998},{"id":"1910f031d64b8be82c3ee121c372ca383b908b5f84b9694e90b3307b0235fb42987a306c1dc1a4892fa23877c7c77c793f283da3fcc335aeace802711c12b17c","href":"https://static.observableusercontent.com/files/1910f031d64b8be82c3ee121c372ca383b908b5f84b9694e90b3307b0235fb42987a306c1dc1a4892fa23877c7c77c793f283da3fcc335aeace802711c12b17c","name":"stores-1.csv","type":"text/csv","lastModified":1686068139338.571,"size":2464},{"id":"be89abb1e2f19efc6d7ea98d16245ad55e70f43c73222373ce5e76beb3561e9f1616a533c1dcdb8c1e6c767703264ac84491dc8a3b06cc4048a005db46ac2221","href":"https://static.observableusercontent.com/files/be89abb1e2f19efc6d7ea98d16245ad55e70f43c73222373ce5e76beb3561e9f1616a533c1dcdb8c1e6c767703264ac84491dc8a3b06cc4048a005db46ac2221","name":"nevada_daily_orders.csv","type":"text/csv","lastModified":1686068139338.571,"size":575834},{"id":"d7371630fba87e6b204fbd54ccf3e4b5d582589ee1743c6a2fdc191a829d6961b67a8e5596d63ed7a19cc7b404a2984efd8500ce85e120d636060eaedca87016","href":"https://static.observableusercontent.com/files/d7371630fba87e6b204fbd54ccf3e4b5d582589ee1743c6a2fdc191a829d6961b67a8e5596d63ed7a19cc7b404a2984efd8500ce85e120d636060eaedca87016","name":"orderItems-1.parquet","type":"application/octet-stream","lastModified":1686068139338.571,"size":6180623},{"id":"d0cad3c610647300f5e5a53d5788ce61aa256c79feee03f9c592d3465eb624fa878ad5eaf5a656354dab5d2040abbf8b7f20b320aca1c75e14bbf9722d3b9649","href":"https://static.observableusercontent.com/files/d0cad3c610647300f5e5a53d5788ce61aa256c79feee03f9c592d3465eb624fa878ad5eaf5a656354dab5d2040abbf8b7f20b320aca1c75e14bbf9722d3b9649","name":"california_daily_orders.csv","type":"text/csv","lastModified":1686068139338.571,"size":758158},{"id":"2a2cd2baa5c4eaf678f05cd13d90d4dda75008abc3d47c21179f051b8e018d10505d71fda7b212d91434803f29435e40b8cb945c4cf08f0ce536d2b12439a7a4","href":"https://static.observableusercontent.com/files/2a2cd2baa5c4eaf678f05cd13d90d4dda75008abc3d47c21179f051b8e018d10505d71fda7b212d91434803f29435e40b8cb945c4cf08f0ce536d2b12439a7a4","name":"customers-1.csv","type":"text/csv","lastModified":1686068139338.571,"size":1591967},{"id":"c653108ab176088cacbb338eaf2344c4f5781681702bd6afb55697a3f91b511c6686ff469f3e3a27c75400001a2334dbd39a4499fe46b50a8b3c278b7d2f7fb5","href":"https://static.observableusercontent.com/files/c653108ab176088cacbb338eaf2344c4f5781681702bd6afb55697a3f91b511c6686ff469f3e3a27c75400001a2334dbd39a4499fe46b50a8b3c278b7d2f7fb5","name":"orders_by_product_day.csv","type":"text/csv","lastModified":1686068139338.571,"size":2329284},{"id":"24e3ce238b4bf544c73eaf2e33aff298877266c45c7e2323f867a9f3ab9ada7da60acac1541c7dda76d0e280a90798c3507fd274de94262de7e42327bfc9f570","href":"https://static.observableusercontent.com/files/24e3ce238b4bf544c73eaf2e33aff298877266c45c7e2323f867a9f3ab9ada7da60acac1541c7dda76d0e280a90798c3507fd274de94262de7e42327bfc9f570","name":"orders-1.parquet","type":"application/octet-stream","lastModified":1686068139338.571,"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\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","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) 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","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_items\n , mode(orders.nItems) as mode_items\n FROM orders","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":"","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":"","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":"","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":"","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":"","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":"","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":"","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":"","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":"","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"}