31:T76f,testYourKnowledge([ { title: 'Window functions', backText: [`A window function is a SQL function where the input values are taken from a “window” of one or more rows in the results set of a SELECT statement.`, `A window can be defined by the number of rows (preceding and following), a date range (interval) or by a condition (partition by)`], question: "Window functions allow you to look at a window of records, whether by the number of rows, a date range, by some condition.", answer: true }, { title: 'WITH statements', backText: ['WITH statements are easy to write. All you have to do is use the WITH expression, provide a name to your temporary table, then write the query in parenthesis.', 'WITH statements are helpful for breaking up complicated queries into smaller parts. It can help make them more readable.'], question: 'WITH statements are difficult to write, and should be used sparingly.', answer: false }, { title: 'Query Optimization', backText: [`Writing optimal queries is very important! One, it can save you time to have a query that's better written, as it'll reduce the runtime, two, it can save money as some SQL services charge by the runtime.`, `It's also better for readability and shareability to have optimal queries, and when you're working at a large organization, those differences matter.`], question: `It doesn't matter how you write the query, as long as you return the correct results.`, answer: false }, { title: 'SQL Dialects', backText: [`Most SQL dialects are very similar. They have the same clauses (SELECT, FROM, WHERE, etc.) and many of the same functions.`, `To note, most differences in SQL dialects will be between the functions they have. For example, SQLite have very few functions compared to Snowflake.`], question: `Most SQL dialects are very similar.`, answer: true }, ], 300, 300)32:[{"id":"be89abb1e2f19efc6d7ea98d16245ad55e70f43c73222373ce5e76beb3561e9f1616a533c1dcdb8c1e6c767703264ac84491dc8a3b06cc4048a005db46ac2221","href":"https://static.observableusercontent.com/files/be89abb1e2f19efc6d7ea98d16245ad55e70f43c73222373ce5e76beb3561e9f1616a533c1dcdb8c1e6c767703264ac84491dc8a3b06cc4048a005db46ac2221","name":"nevada_daily_orders.csv","type":"text/csv","lastModified":1685649966508.525,"size":575834},{"id":"1910f031d64b8be82c3ee121c372ca383b908b5f84b9694e90b3307b0235fb42987a306c1dc1a4892fa23877c7c77c793f283da3fcc335aeace802711c12b17c","href":"https://static.observableusercontent.com/files/1910f031d64b8be82c3ee121c372ca383b908b5f84b9694e90b3307b0235fb42987a306c1dc1a4892fa23877c7c77c793f283da3fcc335aeace802711c12b17c","name":"stores-1.csv","type":"text/csv","lastModified":1685649965083.566,"size":2464},{"id":"672ab9f359d9ffc9c53007075f522cb704208e586f217ea70eef80a9a5b6ac24621cf2fb9d813b0b69dfc054f1024439cc40586e70d626b2da30c6133b5b6842","href":"https://static.observableusercontent.com/files/672ab9f359d9ffc9c53007075f522cb704208e586f217ea70eef80a9a5b6ac24621cf2fb9d813b0b69dfc054f1024439cc40586e70d626b2da30c6133b5b6842","name":"california_stores.csv","type":"text/csv","lastModified":1685649965051.138,"size":1380},{"id":"d2de5ac62407741030d131dcd5e2358b697a28bd66b59c9be3ab1980805026b69e09b890e51fe860d21fe587ab303061e57501fb10bc3012758958ec94c4429c","href":"https://static.observableusercontent.com/files/d2de5ac62407741030d131dcd5e2358b697a28bd66b59c9be3ab1980805026b69e09b890e51fe860d21fe587ab303061e57501fb10bc3012758958ec94c4429c","name":"nevada_stores.csv","type":"text/csv","lastModified":1685649965167.472,"size":998},{"id":"d0cad3c610647300f5e5a53d5788ce61aa256c79feee03f9c592d3465eb624fa878ad5eaf5a656354dab5d2040abbf8b7f20b320aca1c75e14bbf9722d3b9649","href":"https://static.observableusercontent.com/files/d0cad3c610647300f5e5a53d5788ce61aa256c79feee03f9c592d3465eb624fa878ad5eaf5a656354dab5d2040abbf8b7f20b320aca1c75e14bbf9722d3b9649","name":"california_daily_orders.csv","type":"text/csv","lastModified":1685649966976.967,"size":758158},{"id":"0ab2b5a209427df5bd252d3e92b295d75da9255ee46d58c5be9dca5dbce2404a3a292680c80109285b36ef8531cf762014303cb3a6af9ce55b3aa94b1603dbb1","href":"https://static.observableusercontent.com/files/0ab2b5a209427df5bd252d3e92b295d75da9255ee46d58c5be9dca5dbce2404a3a292680c80109285b36ef8531cf762014303cb3a6af9ce55b3aa94b1603dbb1","name":"products-1.csv","type":"text/csv","lastModified":1685649965400.484,"size":3796},{"id":"2a2cd2baa5c4eaf678f05cd13d90d4dda75008abc3d47c21179f051b8e018d10505d71fda7b212d91434803f29435e40b8cb945c4cf08f0ce536d2b12439a7a4","href":"https://static.observableusercontent.com/files/2a2cd2baa5c4eaf678f05cd13d90d4dda75008abc3d47c21179f051b8e018d10505d71fda7b212d91434803f29435e40b8cb945c4cf08f0ce536d2b12439a7a4","name":"customers-1.csv","type":"text/csv","lastModified":1685649968057.76,"size":1591967},{"id":"24e3ce238b4bf544c73eaf2e33aff298877266c45c7e2323f867a9f3ab9ada7da60acac1541c7dda76d0e280a90798c3507fd274de94262de7e42327bfc9f570","href":"https://static.observableusercontent.com/files/24e3ce238b4bf544c73eaf2e33aff298877266c45c7e2323f867a9f3ab9ada7da60acac1541c7dda76d0e280a90798c3507fd274de94262de7e42327bfc9f570","name":"orders-1.parquet","type":"application/octet-stream","lastModified":1685649979349.517,"size":26591479},{"id":"c653108ab176088cacbb338eaf2344c4f5781681702bd6afb55697a3f91b511c6686ff469f3e3a27c75400001a2334dbd39a4499fe46b50a8b3c278b7d2f7fb5","href":"https://static.observableusercontent.com/files/c653108ab176088cacbb338eaf2344c4f5781681702bd6afb55697a3f91b511c6686ff469f3e3a27c75400001a2334dbd39a4499fe46b50a8b3c278b7d2f7fb5","name":"orders_by_product_day.csv","type":"text/csv","lastModified":1685649968974.764,"size":2329284},{"id":"d7371630fba87e6b204fbd54ccf3e4b5d582589ee1743c6a2fdc191a829d6961b67a8e5596d63ed7a19cc7b404a2984efd8500ce85e120d636060eaedca87016","href":"https://static.observableusercontent.com/files/d7371630fba87e6b204fbd54ccf3e4b5d582589ee1743c6a2fdc191a829d6961b67a8e5596d63ed7a19cc7b404a2984efd8500ce85e120d636060eaedca87016","name":"orderItems-1.parquet","type":"application/octet-stream","lastModified":1685649971864.11,"size":6180623}] 2f:{"title":"SQL Course: Session 4","cells":[{"id":0,"value":"# SQL Course: Session 4","pinned":false,"hidden":false,"mode":"md"},{"id":7,"value":"- [Session 4 slide deck](https://docs.google.com/presentation/d/16BYbJA9se4dmWJ-okvxrg3MkCyrSZ1rQzqEuCtFunLQ/edit?usp=sharing)","pinned":false,"hidden":false,"mode":"md"},{"id":8,"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":9,"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":10,"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":11,"value":"---\n## Activity 1\n
","pinned":false,"hidden":false,"mode":"md"},{"id":47,"value":"### Window Functions\n- Create a row number for each product partitioned by category and ordered by the launch date.","pinned":false,"hidden":false,"mode":"md"},{"id":149,"value":"SELECT row_number() over (partition by Category order by Launch) as \"Row Number\"\n , *\n FROM products ","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":143,"value":"- Rank products by category based on the number of orders.","pinned":false,"hidden":false,"mode":"md"},{"id":151,"value":"SELECT products.name\n , products.size\n , products.category \n , count(distinct orders.id) as \"Number of Orders\"\n , rank() over (partition by products.category order by count(distinct orders.id) DESC) as \"Order Rank\"\n FROM products \n JOIN order_items\n ON products.SKU = order_items.SKU\n JOIN orders\n ON order_items.orderId = orders.id \n GROUP BY 1, 2, 3\n","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":202,"value":"- Write a query that finds the first products orders by each year.","pinned":false,"hidden":false,"mode":"md"},{"id":221,"value":"SELECT products.name\n , products.size\n , products.category \n , date_part('year', orders.orderDate::date)::string as year_name\n , row_number() over (partition by year_name order by orderDate asc) as row_num\n FROM products \n JOIN order_items\n ON products.SKU = order_items.SKU\n JOIN orders\n ON order_items.orderId = orders.id \n ORDER BY row_num, year_name \n LIMIT 3","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":12,"value":"---\n## Activity 2\n
\n\n### WITH statements\n- Write a query that creates WITH statement for Utah stores, and count sales by month for just Utah stores, filtering with a JOIN.","pinned":false,"hidden":false,"mode":"md"},{"id":210,"value":"WITH utahStores AS (\n SELECT *\n FROM stores\n WHERE state = 'Utah'\n)\n\n SELECT date_trunc('month', orders.orderDate::date) as \"Month Name\"\n , count(distinct orders.id) as \"Number of Orders\"\n FROM orders\n JOIN utahStores\n ON orders.storeId = utahStores.id\n GROUP BY 1","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":95,"value":"- Make a 7-day moving average of the number of Hawaiian pizza orders, using a WITH statement to pre-aggregate orders by day.","pinned":false,"hidden":false,"mode":"md"},{"id":98,"value":"WITH hawaiianOrders AS (\n SELECT orders.orderDate::date as order_date\n , count(distinct orders.id) as num_orders\n FROM products \n JOIN order_items\n ON products.SKU = order_items.SKU\n JOIN orders\n ON order_items.orderId = orders.id \n WHERE products.name like '%Hawaiian%'\n GROUP BY 1\n ORDER BY 1 ASC\n), returnResults AS (\n SELECT *\n , avg(num_orders) over (\n order by order_date asc\n range between interval 3 days preceding \n and interval 3 days following\n ) as moving_avg\n FROM hawaiianOrders\n)\n\nSELECT * \n FROM returnResults\n","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":13,"value":"---\n## Activity 3\n
\n- Optimize this query following the simple principles: filter data early, create subsets with WITH, use LEFT JOINs only when necessary, be careful for duplicate rows.","pinned":false,"hidden":false,"mode":"md"},{"id":235,"value":"WITH newData AS (\n SELECT orders.id\n , products.name\n , products.Size\n FROM orders\n JOIN order_items \n ON orders.id = order_items.orderId\n JOIN products\n ON order_items.SKU = products.SKU\n WHERE Category = 'Vegetarian'\n \n)\n\nSELECT name\n , Size\n , count(distinct id)\n FROM newData\n GROUP BY 1, 2","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":242,"value":"","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":16,"value":"---\n## Wrap Up\nTest your knowledge with these short questions.","pinned":false,"hidden":false,"mode":"md"},{"id":17,"value":"$31","pinned":false,"hidden":false,"mode":"ojs"},{"id":18,"value":"

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

","pinned":false,"hidden":false,"mode":"md"},{"id":20,"value":"---\n## Appendix","pinned":false,"hidden":false,"mode":"md"},{"id":21,"value":"import {note} from \"@observablehq/callout-boxes\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":22,"value":"import { showMe } from \"@observablehq/show-me\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":23,"value":"import {testYourKnowledge} from \"@observablehq/test-your-knowledge-cards\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":50,"value":"SELECT avg(amount) as avg_amount\n FROM groceries\n WHERE category = 'Fruits'","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:groceries"},{"id":51,"value":"groceries = [\n{category: 'Fruits', value: 'Apple', amount: 10},\n{category: 'Fruits', value: 'Orange', amount: 6},\n{category: 'Fruits', value: 'Pear', amount: 2},\n{category: 'Fruits', value: 'Banana', amount: 20},\n{category: 'Vegetables', value: 'Corn', amount: 9},\n{category: 'Vegetables', value: 'Potato', amount: 15},\n{category: 'Vegetables', value: 'Artichoke', amount: 5},\n{category: 'Vegetables', value: 'Leek', amount: 2},\n]","pinned":false,"hidden":false,"mode":"ojs"},{"id":24,"value":"color = d3.schemeBlues[7]","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W32","stdlib":"1","titling":"auto"}