25:["$","title",null,{"children":"SQL Course: Session 4 | Federica Gazzelloni | Observable"}] 49: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)4a:[{"id":"be89abb1e2f19efc6d7ea98d16245ad55e70f43c73222373ce5e76beb3561e9f1616a533c1dcdb8c1e6c767703264ac84491dc8a3b06cc4048a005db46ac2221","href":"https://static.observableusercontent.com/files/be89abb1e2f19efc6d7ea98d16245ad55e70f43c73222373ce5e76beb3561e9f1616a533c1dcdb8c1e6c767703264ac84491dc8a3b06cc4048a005db46ac2221","name":"nevada_daily_orders.csv","type":"text/csv","lastModified":1686154702940.025,"size":575834},{"id":"1910f031d64b8be82c3ee121c372ca383b908b5f84b9694e90b3307b0235fb42987a306c1dc1a4892fa23877c7c77c793f283da3fcc335aeace802711c12b17c","href":"https://static.observableusercontent.com/files/1910f031d64b8be82c3ee121c372ca383b908b5f84b9694e90b3307b0235fb42987a306c1dc1a4892fa23877c7c77c793f283da3fcc335aeace802711c12b17c","name":"stores-1.csv","type":"text/csv","lastModified":1686154702940.025,"size":2464},{"id":"672ab9f359d9ffc9c53007075f522cb704208e586f217ea70eef80a9a5b6ac24621cf2fb9d813b0b69dfc054f1024439cc40586e70d626b2da30c6133b5b6842","href":"https://static.observableusercontent.com/files/672ab9f359d9ffc9c53007075f522cb704208e586f217ea70eef80a9a5b6ac24621cf2fb9d813b0b69dfc054f1024439cc40586e70d626b2da30c6133b5b6842","name":"california_stores.csv","type":"text/csv","lastModified":1686154702940.025,"size":1380},{"id":"d2de5ac62407741030d131dcd5e2358b697a28bd66b59c9be3ab1980805026b69e09b890e51fe860d21fe587ab303061e57501fb10bc3012758958ec94c4429c","href":"https://static.observableusercontent.com/files/d2de5ac62407741030d131dcd5e2358b697a28bd66b59c9be3ab1980805026b69e09b890e51fe860d21fe587ab303061e57501fb10bc3012758958ec94c4429c","name":"nevada_stores.csv","type":"text/csv","lastModified":1686154702940.025,"size":998},{"id":"d0cad3c610647300f5e5a53d5788ce61aa256c79feee03f9c592d3465eb624fa878ad5eaf5a656354dab5d2040abbf8b7f20b320aca1c75e14bbf9722d3b9649","href":"https://static.observableusercontent.com/files/d0cad3c610647300f5e5a53d5788ce61aa256c79feee03f9c592d3465eb624fa878ad5eaf5a656354dab5d2040abbf8b7f20b320aca1c75e14bbf9722d3b9649","name":"california_daily_orders.csv","type":"text/csv","lastModified":1686154702940.025,"size":758158},{"id":"0ab2b5a209427df5bd252d3e92b295d75da9255ee46d58c5be9dca5dbce2404a3a292680c80109285b36ef8531cf762014303cb3a6af9ce55b3aa94b1603dbb1","href":"https://static.observableusercontent.com/files/0ab2b5a209427df5bd252d3e92b295d75da9255ee46d58c5be9dca5dbce2404a3a292680c80109285b36ef8531cf762014303cb3a6af9ce55b3aa94b1603dbb1","name":"products-1.csv","type":"text/csv","lastModified":1686154702940.025,"size":3796},{"id":"2a2cd2baa5c4eaf678f05cd13d90d4dda75008abc3d47c21179f051b8e018d10505d71fda7b212d91434803f29435e40b8cb945c4cf08f0ce536d2b12439a7a4","href":"https://static.observableusercontent.com/files/2a2cd2baa5c4eaf678f05cd13d90d4dda75008abc3d47c21179f051b8e018d10505d71fda7b212d91434803f29435e40b8cb945c4cf08f0ce536d2b12439a7a4","name":"customers-1.csv","type":"text/csv","lastModified":1686154702940.025,"size":1591967},{"id":"24e3ce238b4bf544c73eaf2e33aff298877266c45c7e2323f867a9f3ab9ada7da60acac1541c7dda76d0e280a90798c3507fd274de94262de7e42327bfc9f570","href":"https://static.observableusercontent.com/files/24e3ce238b4bf544c73eaf2e33aff298877266c45c7e2323f867a9f3ab9ada7da60acac1541c7dda76d0e280a90798c3507fd274de94262de7e42327bfc9f570","name":"orders-1.parquet","type":"application/octet-stream","lastModified":1686154702940.025,"size":26591479},{"id":"c653108ab176088cacbb338eaf2344c4f5781681702bd6afb55697a3f91b511c6686ff469f3e3a27c75400001a2334dbd39a4499fe46b50a8b3c278b7d2f7fb5","href":"https://static.observableusercontent.com/files/c653108ab176088cacbb338eaf2344c4f5781681702bd6afb55697a3f91b511c6686ff469f3e3a27c75400001a2334dbd39a4499fe46b50a8b3c278b7d2f7fb5","name":"orders_by_product_day.csv","type":"text/csv","lastModified":1686154702940.025,"size":2329284},{"id":"d7371630fba87e6b204fbd54ccf3e4b5d582589ee1743c6a2fdc191a829d6961b67a8e5596d63ed7a19cc7b404a2984efd8500ce85e120d636060eaedca87016","href":"https://static.observableusercontent.com/files/d7371630fba87e6b204fbd54ccf3e4b5d582589ee1743c6a2fdc191a829d6961b67a8e5596d63ed7a19cc7b404a2984efd8500ce85e120d636060eaedca87016","name":"orderItems-1.parquet","type":"application/octet-stream","lastModified":1686154702940.025,"size":6180623}] 29:{"id":"1ab99752c64f4fbd","slug":null,"title":"SQL Course: Session 4","thumbnail":"36d8d97b8dcc95a2577e51460a0ac44d6a2eb5d2a647e6f8979d9b84d32cbd41","access_level":"public","create_time":"$D2023-06-07T16:18:22.940Z","update_time":"$D2023-06-07T17:00:16.238Z","view_time":null,"first_public_time":"$D2023-06-07T16:18:22.940Z","has_unpublished_edits":null,"fork":{"id":"987695cd750036f0","slug":"sql-course-session-4","title":"SQL Course: Session 4","owner":{"id":"f35c755083683fe5","login":"observablehq","name":"Observable"}},"stars":0,"starred":false,"owner":{"type":"team","id":"d928373a708282a1","login":"fgazzelloni","name":"Federica Gazzelloni","avatar_url":"https://avatars-next.observableusercontent.com/2a457391178ccd15d10c512b0592b41d3300b8703d8e1c4c934d6116b4cb867a","tier":"starter_2024","bio":"","followed_by_you":false,"follows_you":false},"version":297,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"2eccbec97fff60ed","login":"fgazzelloni","name":"Federica Gazzelloni","avatar_url":"https://avatars-next.observableusercontent.com/2a457391178ccd15d10c512b0592b41d3300b8703d8e1c4c934d6116b4cb867a","tier":null,"bio":"","followed_by_you":false,"follows_you":false}],"members":[{"type":"individual","id":"2eccbec97fff60ed","login":"fgazzelloni","name":"Federica Gazzelloni","avatar_url":"https://avatars-next.observableusercontent.com/2a457391178ccd15d10c512b0592b41d3300b8703d8e1c4c934d6116b4cb867a","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":0,"collection":{"id":"d53d45cf2907c72e","slug":"sql-course","title":"SQL Course","owner":{"id":"d928373a708282a1","login":"fgazzelloni","name":"Federica Gazzelloni"}},"type":"notebook","body":{"titling":"auto","title":"SQL Course: Session 4","cells":[{"id":0,"value":"# SQL Course: Session 4","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":7,"value":"- [Session 4 slide deck](https://docs.google.com/presentation/d/16BYbJA9se4dmWJ-okvxrg3MkCyrSZ1rQzqEuCtFunLQ/edit?usp=sharing)","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":11,"value":"---\n## Activity 1\n
","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":143,"value":"- Rank products by category based on the number of orders.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"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\"\nFROM products\nJOIN order_items\nON products.SKU = order_items.SKU\nJOIN orders\nON order_items.orderId = orders.id\nGROUP BY 1,2,3\n","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","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","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"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\nFROM products\nJOIN order_items\nON products.SKU = order_items.SKU\nJOIN orders\nON order_items.orderId = orders.id\n ORDER BY row_num, year_name\nLIMIT 3","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","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","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":210,"value":"WITH utahStores AS (\n SELECT *\n FROM stores\n WHERE state = 'Utah'\n )\nSELECT date_trunc('month',orders.orderDate::date) as \"Month Name\"\n , count(distinct orders.id) as \"Number of Orders\"\nFROM orders\n JOIN utahStores\n ON orders.storeId = utahStores.id\nGROUP BY 1","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","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","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"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\nJOIN order_items\nON products.SKU = order_items.SKU\nJOIN orders\nON order_items.orderId = orders.id\n WHERE products.name like '%Hawaiian%'\n GROUP BY 1\n ORDER BY 1 ASC\n)\nSELECT *\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 )\nFROM hawaiianOrders","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","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","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":235,"value":"WITH newData AS (\n SELECT orders.id\n , products.* \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)\nSELECT DISTINCT name\n , Size\n , count(distinct id)\n FROM newData\n GROUP BY 1, 2","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":242,"value":"","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":16,"value":"---\n## Wrap Up\nTest your knowledge with these short questions.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":17,"value":"$49","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":18,"value":"

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

","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":20,"value":"---\n## Appendix","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":21,"value":"import {note} from \"@observablehq/callout-boxes\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":22,"value":"import { showMe } from \"@observablehq/show-me\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":23,"value":"import {testYourKnowledge} from \"@observablehq/test-your-knowledge-cards\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":50,"value":"SELECT avg(amount) as avg_amount\n FROM groceries\n WHERE category = 'Fruits'","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","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","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":24,"value":"color = d3.schemeBlues[7]","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W4a","stdlib":"1"}} 24:null