2f:T532,# ClickHouse SQL Workshop Day 2 - Presenter Edition *2023-02-13* ${await FileAttachment("clickhouse-mark-4238843745.png").image()} This tutorial aims to introduce beginners to the topic of relational and non-relational databases and SQL in the context of data analytics using [ClickHouse](https://clickhouse.com/), a fast open-source OLAP DBMS. This tutorial uses an [Observable Notebook](https://observablehq.com/@observablehq/observable-overview-video?collection=@observablehq/getting-started) to give you an interactive experience. Note that ClickHouse is usually used via other clients such as `clickhouse-client`, [DataGrip](https://www.jetbrains.com/datagrip/), and [DBeaver](https://dbeaver.io/). If you accidentally change the cells of this notebook, you can undo the changes by pressing the "Discard" button on the top bar or by refreshing the page. ## SQL Cells Observable Notebook's SQL Client allows you to write and execute your own queries from your own web browser. Feel free to practice your SQL skills as you progress through this tutorial. If you would like to save your changes, feel free to create an ObservableHQ account and fork this notebook. > **Note**: Run cells by pressing SHIFT + ENTER or the blue triangle on the top right of the textbox Cells that allow you to run SQL look like the following:30:T922,--- ## Basic SQL Syntax Continued... During last session, we ran out of time before we were able to explore data types and functions/operators that operate on them. So we can give a dedicate some more time to do that today. Also, I neglected to include functions for working with Arrays last week so I added them to this notebook. ### Data Types Each column should contain one particular kind of value (ie. a number, a sequence of letters, true or false, a sequence of values, a date, etc). This structure to our schema helps ensure data integrity and makes it easier to read and perform operations on our data. In a column-oriented database, this also allows for efficient data storage via encodings and compression ClickHouse provides many data types to work with but here are the most commonly used. | Category | ClickHouse Data Types | Description | Examples | --- | --- | --- | --- | | Integers | `Int8`, `Int16`, `Int32`, `Int64`, ... | Integers, ${tex`\mathbb{Z}`} | -20, 0, 5 | | Unsigned Integers | `UInt8`, `UInt16`, `UInt32`, `UInt64`, ... | Positive integers and zero (aka the Whole Numbers, ${tex`\mathbb{W}`}) | 0, 5, 10 | | Floating Point Numbers | `Float32`, `Float64` | Numbers including decimal point precision. (aka the Real Numbers, ${tex`\mathbb{R}`}) | 1.0, -2.2 | | Booleans | `Boolean` | True or False | `true`, `false` | | Dates | `Date`, `Date32` | Dates | '2023-02-02' | | DateTimes | `DateTime`, `DateTime64` | The date and time stored together | `'2023-02-03 23:48:25'` | | Strings | `String`, `FixedString(N)` | A sequence of characters | `'Hello World'` | | Arrays | `Array(T)` | A series of values | `[1, 2, 3]`, `['a', 'b', 'c', 'd']` | Note that the number following some of the ClickHouse Data Types' names indicates the number of bits used to store each value. Fewer bits means smaller memory footprint, but also constrains the maximum value that can be stored. For example, a `Date` is stored in 2 bytes or 16 bits, so it can only represent dates from `1970-01-01` until `2149-06-06`. A `Date32` is stored as a signed 32-bit integer and can represent dates from `1900-01-01` until `2299-12-31`. Be careful storing dates outside these ranges as date-typed columns! ClickHouse may incorrectly store out-of-range values. #### Read More - https://clickhouse.com/docs/en/sql-reference/data-types/ 31:T52e,___ ### Operators & Functions In addition to retrieving columns, ClickHouse can perform operations on the data in the columns using various operators and functions to act on particular data types. Operators are keywords that act on expressions that are left or right of them. For example the `AND` operator (ie `true AND false` evaluates to `false`). Functions operate on zero or more parameters. For example `lower` is a function that converts `String`s to lowercase. `lower('FOO')` evaluates to `'foo'`. Notice that the parentheses that come after the name of the function contain a comma separated list of the parameters of the function. --- For this section we'll be using the `recipes` table as well as the `numbers(n)` table function. Columns of `recipes` table ${await client.describe('recipes')} Learn about the `numbers(N)` table function [here](https://clickhouse.com/docs/en/sql-reference/table-functions/numbers). #### Read More - [ClickHouse Documentation](https://clickhouse.com/docs/en/sql-reference/functions/). --- #### Operators & Functions for Numbers We had mentioned the numerical comparison operators (`<`, `<=`, `=`, `>=`, `>`, `!=`) earlier. Arithmetic - `plus(a,b)`, `a + b` - `minus(a,b)`, `a - b` - `multiply(a,b)`, `a * b` - `divide(a,b)`, `a / b` - `modulo(a,b)`, `a % b` - `abs(a)` 32:T197c,--- ## Table Relationships There are a few ways tables can store data within intrinsic relationships to one another. #### One-To-One ${await FileAttachment("one-to-one.png").image()} Each student is represented by a single row on the Students table and a single row on the Student Info table. The Student ID identifies each unique student. There is a *One-To-One* relationship between Students and Student Info. #### One-To-Many ${await FileAttachment("one-to-many.png").image()} Each professor may teach many classes or none at all. The Professor ID column of the Classes table identifies which professor on the Professors table is teaching the class. In other words, the Professors table has a *One-To-Many* relationship with Classes #### Many-To-Many ${await FileAttachment("many-to-many.png").image()} Each class can have many students and each student can be in many classes. This is a *many-to-many* relationship. In this case, we need a [junction table](https://en.wikipedia.org/wiki/Associative_entity) (a.k.a. join table, bridge table, linking table, ...). Each row on the junction table denotes the existence of one student in a particular class. Information about this relationship (ie information associated with a student's membership in his/her class) can be stored in this table. This may include the grade the student has earned, the time the student enrolled in the class, and whether the student is auditing the class. #### Read More - https://www.tutorialsteacher.com/sqlserver/tables-relations --- ### Keys A key is a value (or field) in a table used for identification. When modeling data, there are many distinctions to be made between types of keys including Primary keys, Natural keys, Surrogate keys, Composite keys, Alternate keys, Unique keys, Foreign keys, and more. We will only touch on a few of these. | Name | Description | Example | | --- | --- | --- | | Primary Key | Uniquely identifies a row in the table it resides in. | Student ID in the Students table | | Foreign Key | Identifies a row in a different table. | Professor ID in the Classes table | | Composite Key | Two or more field that together uniquely identify a row in the table they reside in, allowing them to act as a Primary Key. | Student ID and Class ID in the Students Classes Table | #### When The Data Disagrees With Itself What would it mean if we had two students with the same Student ID? What if we had a Professor ID in the classes table who was absent from the Professors table? In our model, this makes no sense and can be thought of as two of many different types of data corruption or data integrity violation. The uniqueness of the primary key and referential nature of the foreign key are constraints that a DBMS can enforce to improve data integrity and alert you about potential data corruption. This is a common feature of Relational DBMS (RDBMS) and is useful when new data is frequently inserted. Unfortunately, ClickHouse in allows non-unique Primary Keys and Nullable Primary Keys and does not enforce Foreign Key constraints. #### Read (or Watch) More - https://www.databasestar.com/database-keys/ - https://youtu.be/8wUUMOKAK-c --- ## Joins With the tables above how could you create a list of Class names alongside the names of the Professors teaching them? How would you create class rosters for each class with the names and phone numbers of each student in each class? To answer these questions or queries, we must combine information found in columns of different tables. To combine information from different tables, we find the rows where the foreign key in one table equals the primary key in the other. The key we try to match between two tables is termed the "join key". Once the rows with the same join key are paired, we combine the columns of both tables to create a new table. This is called a join. ```sql SELECT ... FROM JOIN USING ``` ```sql SELECT ... FROM JOIN ON ``` For this section we will use a series of tables relating to restaurant menu items. Here are their descriptions. ${await FileAttachment("menu_desc.png").image()} --- ### Types of Joins ${await FileAttachment("截屏2023-02-10 下午1.04.18").image()} When choosing between the standard SQL joins, the decision revolves around one question: > What do we do if the join key in one table does not exist in the other. - Inner Join: keep only rows where keys exist in both tables. - Full Outer Join: keep all rows even keys only exist in one of two tables. - Left Outer Join: keep rows with keys that exist in the left table regardless of whether they exist in the right table. There is another important question: > What do we do if the same keys are repeated? In the example with the Classes and Professors tables, the Classes table can and should have repeats in the Professor ID column since one Professor may teach many classes. If we wanted to get a list of names of classes and professors that taught them, then we would want to keep the repeated Professors. ${await FileAttachment("cartesian_product.png").image()} The default behavior keeps duplicates and produces a [Cartesian Product](https://en.wikipedia.org/wiki/Cartesian_product) from rows with matching join keys. The center table in the figure above would be generated by ```sql SELECT a.join_key AS join_key, a.value, b.value FROM a INNER JOIN b ON a.join_key = b.join_key; ``` If you would like to refrain from making a cartesian product and only keep the first row with matching join keys for each key, ClickHouse allows you to use an `ANY` Join. - `INNER ANY JOIN` - `OUTER ANY JOIN` - `LEFT ANY JOIN` These may be useful for speeding up your query when you know there is unlikely to be any repeating of join keys. ClickHouse supports other types of joins such as `SEMI JOIN`, `ANTI JOIN`, `ASOF JOIN` and `CROSS JOIN` which you can learn more about in the documentation. #### Read More - [Wikipedia article about Join in SQL](https://en.wikipedia.org/wiki/Join_%28SQL%29) - [ClickHouse Documentation about Joins](https://clickhouse.com/docs/en/sql-reference/statements/select/join/) --- ### Examples Imagine we have to print all our menus and send them out to their venues. We need to gather all the pages of every menu and get the sizes of each page. The `menu` table seems to have a one-to-many relationship with the `menu_page` table since the `menu_page` table has a `menu_id` foreign key.33:[{"id":"5acf54989c2155c1ad0c6dcb7433fc703e3464572d573c9873dc6d38863339d5e3178453f2ce927068ebe471a97c8e28cfdb1f927513263259c88bdcbfb653ec","href":"https://static.observableusercontent.com/files/5acf54989c2155c1ad0c6dcb7433fc703e3464572d573c9873dc6d38863339d5e3178453f2ce927068ebe471a97c8e28cfdb1f927513263259c88bdcbfb653ec","name":"clickhouse-mark-4238843745.png","type":"image/png","lastModified":1676328246901.547,"size":7715},{"id":"be7b68209f8d5d04e7cbf1a0079ba2f54a506b7b738c054cfa09059a4e5a3be9c0d7db59b289f9bc72ef9cc6f95d5d110818a10d2e8dc370f35ed3bf8ba9d6d8","href":"https://static.observableusercontent.com/files/be7b68209f8d5d04e7cbf1a0079ba2f54a506b7b738c054cfa09059a4e5a3be9c0d7db59b289f9bc72ef9cc6f95d5d110818a10d2e8dc370f35ed3bf8ba9d6d8","name":"many-to-many.png","type":"image/png","lastModified":1676328246901.547,"size":611321},{"id":"ed53008f75329a656c4566f8435f3c24bd2b01e0816d0bc253259c7c8778b27b03878ac57c0ffe604df564e00ae4fdc61895b85223185e1d716de45aa6834b8d","href":"https://static.observableusercontent.com/files/ed53008f75329a656c4566f8435f3c24bd2b01e0816d0bc253259c7c8778b27b03878ac57c0ffe604df564e00ae4fdc61895b85223185e1d716de45aa6834b8d","name":"one-to-many.png","type":"image/png","lastModified":1676328246901.547,"size":506524},{"id":"76d32fdf229d3d769cb188da365ee7ebef7c3f24e560b56d76563959ff8584ddde8f6d8c2104919f8ede9f5b5d8b5512b12330cc0bcfd9310531251a8bbb4e24","href":"https://static.observableusercontent.com/files/76d32fdf229d3d769cb188da365ee7ebef7c3f24e560b56d76563959ff8584ddde8f6d8c2104919f8ede9f5b5d8b5512b12330cc0bcfd9310531251a8bbb4e24","name":"menu_desc.png","type":"image/png","lastModified":1676328246901.547,"size":295292},{"id":"f0c83b96b901fa17625e28c042e51902e98fe2e0916decd78161fa2e8b07c0bae1dc2b92fc06249fd0417bfb9fd0f4e7eeccab50c33364987d9ca87fb16c913c","href":"https://static.observableusercontent.com/files/f0c83b96b901fa17625e28c042e51902e98fe2e0916decd78161fa2e8b07c0bae1dc2b92fc06249fd0417bfb9fd0f4e7eeccab50c33364987d9ca87fb16c913c","name":"one-to-one.png","type":"image/png","lastModified":1676328246901.547,"size":635905},{"id":"ebaf4853de0fff7077a3a0bc78408ecb48d603cbc26f1f428250f22e23b9d4459c0c7fb11961e0f1c1ac078f6b47242a992c40651432133a81f4d6788026c779","href":"https://static.observableusercontent.com/files/ebaf4853de0fff7077a3a0bc78408ecb48d603cbc26f1f428250f22e23b9d4459c0c7fb11961e0f1c1ac078f6b47242a992c40651432133a81f4d6788026c779","name":"截屏2023-02-10 下午1.04.18","type":"image/png","lastModified":1676328246901.547,"size":3017959},{"id":"4b5c101f82141e6bf75243dafef1c634c2f0ab4aa5b5e975359816a321d87240203360b7e749c6d79f328ed5fd8cca5a10b2cc2ae4e8f14c4dc3be9625e499e0","href":"https://static.observableusercontent.com/files/4b5c101f82141e6bf75243dafef1c634c2f0ab4aa5b5e975359816a321d87240203360b7e749c6d79f328ed5fd8cca5a10b2cc2ae4e8f14c4dc3be9625e499e0","name":"cartesian_product.png","type":"image/png","lastModified":1676328246901.547,"size":180987},{"id":"0929562ce162e386dc26525790e8774f0f109f5df5da30358bb06ff3497b336e5ef11cab52305f9ba26b55cb9d06d227bcf1c06110a4defb5dfd4c4667fe527a","href":"https://static.observableusercontent.com/files/0929562ce162e386dc26525790e8774f0f109f5df5da30358bb06ff3497b336e5ef11cab52305f9ba26b55cb9d06d227bcf1c06110a4defb5dfd4c4667fe527a","name":"sliding-window@1.png","type":"image/png","lastModified":1676328246901.547,"size":405793},{"id":"3db75123ef7445fba287aed68301c83a2b15422b8bd3c78a42bf6aed5c839eb1497d51494f743fd9dae8d2672f27b2156c2b2443a17bb115762f87408b4da4bc","href":"https://static.observableusercontent.com/files/3db75123ef7445fba287aed68301c83a2b15422b8bd3c78a42bf6aed5c839eb1497d51494f743fd9dae8d2672f27b2156c2b2443a17bb115762f87408b4da4bc","name":"answers2@1.db","type":"application/x-sqlite3","lastModified":1676340648374.064,"size":110592},{"id":"291ffdaaef1366e902439d072cbfd6c80c09852c66799b541a6daa1f69663fab2ba05f40b560f6c45ab174e3fd77e00f1bb2cdeacdad8ae3ae1ee69a1b26df5c","href":"https://static.observableusercontent.com/files/291ffdaaef1366e902439d072cbfd6c80c09852c66799b541a6daa1f69663fab2ba05f40b560f6c45ab174e3fd77e00f1bb2cdeacdad8ae3ae1ee69a1b26df5c","name":"answers1@1.db","type":"application/x-sqlite3","lastModified":1676340930253.159,"size":45056}] 2d:{"title":"ClickHouse SQL Workshop Day 2 - Presenter Edition","cells":[{"id":0,"value":"$2f","pinned":false,"hidden":false,"mode":"md"},{"id":341,"value":"SELECT * FROM stock LIMIT 10","pinned":true,"hidden":false,"mode":"sql","output":"query1","database":"var:client"},{"id":369,"value":"## Exercises\n\nSome cells can check your work and make sure your practice SQL queries are the expected results.\n\nTry changing the query above to `SELECT * FROM stock` (and press CTRL/CMD + ENTER) and the text below will turn red since we are no longer getting only the first 10 rows.","pinned":false,"hidden":false,"mode":"md"},{"id":349,"value":"test(\"Get the first 10 rows of the `stock` table\", () => {\n expect(query1).to.have.lengthOf(10);\n const columns = _.keys(query1[0]);\n expect(columns).to.deep.equal([\n \"symbol\",\n \"date\",\n \"price\",\n \"volume\",\n \"open\",\n \"low\",\n \"high\"\n ]);\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":32,"value":"toc({\n headers: \"h2,h3\",\n skip: [\n \"Imports\",\n \"Topics\",\n \"SQL Cells\",\n \"Exercises\",\n \"Other great references for ClickHouse\",\n \"Appendix\",\n \"What we will be covering\",\n \"What won't be covered\"\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2059,"value":"## Some Housekeeping Before I Forget\n\n1. Does Monday 6pm work for everyone for the next two weeks?\n2. Any questions?\n3. Let's do some exercises to review the basics from last week","pinned":false,"hidden":false,"mode":"md"},{"id":757,"value":"$30","pinned":false,"hidden":false,"mode":"md"},{"id":955,"value":"DESCRIBE TABLE recipes","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1095,"value":"`SHOW TABLES` and `SHOW DATABASES` do exactly what they say","pinned":false,"hidden":false,"mode":"md"},{"id":1097,"value":"SHOW TABLES","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1100,"value":"SHOW DATABASES","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":800,"value":"$31","pinned":false,"hidden":false,"mode":"md"},{"id":818,"value":"SELECT \n rand(0) AS a, \n rand(1) AS b,\n a + b AS added,\n a - b AS subtracted, \n a * b AS multiplied,\n a / b AS divided,\n a % b AS moded,\n abs(subtracted)\nFROM numbers(10);","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":885,"value":"SELECT \n rand(0) AS a, \n rand(1) AS b,\n a > b AS gt,\n a < b AS lt, \n a = b AS eq,\n a != b AS ne\nFROM numbers(10);","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":808,"value":"#### Read More\n- https://clickhouse.com/docs/en/sql-reference/functions/arithmetic-functions\n- https://clickhouse.com/docs/en/sql-reference/functions/random-functions\n- https://clickhouse.com/docs/en/sql-reference/functions/comparison-functions","pinned":false,"hidden":false,"mode":"md"},{"id":840,"value":"#### Exercise","pinned":false,"hidden":false,"mode":"md"},{"id":849,"value":"test(\"Find the range of dish prices (high-low) using `dish_highest_price` and `dish_lowest_price` columns of `menu_item_denorm` table. Remove rows where those columns are zero. Get the first thousand results sorted by menu_id and dish_id. Name the result column `range`. Include the `dish_name` column associated with each of those ranges\", async () => {\n checkExerciseAttempted(s3s1e1);\n const answer = await answers1.query(\"SELECT dish_name, range FROM s3s1e1\");\n expect(s3s1e1).to.deep.equal(answer);\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2133,"value":"*Why I couldn't get the \"correct\" answer during the workshop on day 2: The question prompt was not clear with the fact that the `dish_name` column had to be present. Fixed.*","pinned":false,"hidden":false,"mode":"md"},{"id":836,"value":"SELECT \n dish_name, \n dish_highest_price - dish_lowest_price AS range\nFROM menu_item_denorm \nWHERE dish_highest_price != 0 AND dish_lowest_price != 0 \nORDER BY menu_id, dish_id\nLIMIT 1000","pinned":true,"hidden":false,"mode":"sql","output":"s3s1e1","database":"var:client"},{"id":892,"value":"---\n#### Operators and Functions For Strings\n\nOperators\n- `concat(a,b)`, `a || b` -- combine two strings\n- `a LIKE b` -- TRUE if String matches a pattern, b.\n- `a = b` -- TRUE if String a is the same as String b\n\n[(There are more sophisticated search functions if necessary)](https://clickhouse.com/docs/en/sql-reference/functions/string-search-functions) \n\nThere are a multitude of useful functions for working with strings. I'll cover a few I find very useful. \n\n- `empty(x)` - TRUE if String is empty. Otherwise FALSE.\n- `length(x)` - Length of String\n- `trim(x)` - Remove leading and trailing blank space.\n- `substring(s, offset, length)` - Extract part of a string\n","pinned":false,"hidden":false,"mode":"md"},{"id":898,"value":"SELECT \n title, \n source,\n length(title),\n concat(source, '-', trim(title)),\n lower(title) LIKE '%peruvian%'\nFROM recipes\nORDER BY title","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":915,"value":"#### Read More\n- https://clickhouse.com/docs/en/sql-reference/functions/string-functions\n- https://clickhouse.com/docs/en/sql-reference/functions/string-search-functions\n- https://clickhouse.com/docs/en/sql-reference/functions/string-replace-functions","pinned":false,"hidden":false,"mode":"md"},{"id":922,"value":"#### Exercise","pinned":false,"hidden":false,"mode":"md"},{"id":924,"value":"test(\"Get all values from the `link` column of the `recipes` table and add 'https://' to the beginning of each link. Name this the `answer` column.\", () => {\n checkExerciseAttempted(s3s2e1);\n expect(\n _.every(_.map(s3s2e1, \"answer\"), (str) => str.substr(0, 8) === \"https://\")\n );\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":927,"value":"SELECT \n 'https://' || link AS answer\nFROM recipes","pinned":true,"hidden":false,"mode":"sql","output":"s3s2e1","database":"var:client"},{"id":963,"value":"___\n#### Date and Time functions\n\n- `now()` -- Get the current date and time\n- `today()` -- Get the current date\n- `yesterday()` -- Get yesterday's date\n- `dateDiff(unit, start, end)` -- subtract datetimes in terms of a unit (second, minute, hour, day, week, month, quarter, year)\n- `addYears`, `addMonths`, `addWeeks`, `addDays`, `addHours`, `addMinutes`, `addSeconds`, `addQuarters`\n- `subtractYears`, `subtractMonths`, `subtractWeeks`, `subtractDays`, `subtractHours`, `subtractMinutes`, `subtractSeconds`, `subtractQuarters`\n- `formatDateTime(datetime, format[, timezone])` - Return a string containing the date time formatted in a way you specify","pinned":false,"hidden":false,"mode":"md"},{"id":970,"value":"SELECT\n now(),\n today(),\n dateDiff('hour', yesterday(), today()) AS hrs_btn_td_ytd,\n today() + INTERVAL 1 day AS tomorrow,\n today() - INTERVAL 1 year AS yester_year,\n formatDateTime(now(), '%D at %I:%M:%S %p', 'EST')","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":967,"value":"#### Read More\n- https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions","pinned":false,"hidden":false,"mode":"md"},{"id":1777,"value":"#### Functions for working with Arrays\n\nArrays are a list of some kind of value for example: \n- a list of `String` like `['a', 'b', 'c']`\n- a list of `UInt8` like `[1,2,3]`\n- a list of `Array(UInt8)` like `[[1], [2], [3]]`\n\nClickHouse provides many functions to make it easier to work with Arrays \nHere are some examples.\n- `empty(arr)` - Check if Array is empty\n- `has(arr, elem)` - Check whether Array has a value\n- `length(arr)` - Get length of array\n- `indexOf(arr, elem)` - Get the position of a value in the list\n- `arrayDistinct(arr)` - Get only the unique items in the list","pinned":false,"hidden":false,"mode":"md"},{"id":1779,"value":"SELECT\n number,\n [1,5,2,3,2,4,5,1] AS arr,\n empty(arr),\n length(arr),\n has(arr, number),\n indexOf(arr, number),\n arrayDistinct(arr)\nFROM numbers(10) -- numbers 0 thru 9","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1785,"value":"#### Exercises\n\nHere is the description for the recipes table again. It happens to use some `Array(String)`\n\n${await client.describe('recipes')}","pinned":false,"hidden":false,"mode":"md"},{"id":1801,"value":"test(\"Get the title of all recipes for which '1 coke' is one of the ingredients\", () => {\n checkExerciseAttempted(s3s4e1);\n const expected = [\n { title: \"Long Island Ice Tea\" },\n { title: \"Classic Cola Glazed Ham\" },\n { title: \"Coca Cola Barbecue Chicken\" },\n { title: \"Vanilla Coke\" }\n ];\n\n expect(s3s4e1).to.deep.equal(expected);\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1798,"value":"SELECT\n title\nFROM recipes\nWHERE has(ingredients, '1 coke')","pinned":true,"hidden":false,"mode":"sql","output":"s3s4e1","database":"var:client"},{"id":986,"value":"---\n### Convert between data types using `CAST`\n\nSometimes you need to treat numbers as Strings to add commas, dollar signs, and percent signs. Sometimes you need to treat Strings as Dates. Sometimes you need to convert booleans to numbers. `CAST` will tell ClickHouse to treat one data type as another.\n\nSyntax: \n- `CAST( AS )` \n- `CAST(, '')` \n- `::type`\n\nThere are also many more specific conversion functions such as `toDate32` and `toString`. [See documentation](https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions)","pinned":false,"hidden":false,"mode":"md"},{"id":990,"value":"SELECT\n CAST(1234 AS String) AS my_string,\n CAST('1970-01-01' AS Date) AS my_date,\n CAST('1899-01-01' AS Date) AS my_wrong_date,\n CAST(0 AS BOOLEAN) AS my_false,\n CAST(1 AS BOOLEAN) AS my_true","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":998,"value":"#### Read More\n- https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions/#castx-t","pinned":false,"hidden":false,"mode":"md"},{"id":1013,"value":"#### Exercise","pinned":false,"hidden":false,"mode":"md"},{"id":1015,"value":"test(\"Add a percent sign to the end of each of the numbers below. Name the resulting column `answer`.\", () => {\n if (!(\"answer\" in s4s1e1[0])) throw new UnattemptedExerciseError();\n expect(_.every(s4s1e1, (r) => typeof r.answer === \"string\")).to.be.true;\n expect(_.every(s4s1e1, (r) => r.answer.slice(-1))).to.be.true;\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1010,"value":"SELECT concat(CAST(number AS String), '%') AS answer FROM numbers(10)","pinned":true,"hidden":false,"mode":"sql","output":"s4s1e1","database":"var:client"},{"id":1001,"value":"---\n### Conditional logic using `if(cond, then, else)`, `CASE WHEN` Expression, and the Ternary Operator\n\n- `if(cond, then, else)`\n- `cond ? val_if_true : val_if_false` -- aka the ternary operator\n- ```sql\n CASE\n WHEN x = a THEN b\n ...[WHEN THEN ]\n ELSE c\n END\n ```","pinned":false,"hidden":false,"mode":"md"},{"id":1041,"value":"SELECT\n title, \n length(ingredients) > 3 ? 'four or more ingredients' : 'three or less ingredients' AS category,\n source,\n CASE \n WHEN source ='Gathered' THEN 'the internet'\n WHEN source = 'my brain' THEN 'me'\n ELSE 'idk'\n END AS was_gathered\nFROM recipes;","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1032,"value":"#### Read More\n\n- https://clickhouse.com/docs/en/sql-reference/operators/#conditional-expression\n- https://clickhouse.com/docs/en/sql-reference/functions/conditional-functions","pinned":false,"hidden":false,"mode":"md"},{"id":1235,"value":"test('Get title (`recipes.title`) of each recipe and make a column (named answer) that summarizes how long the list of directions (`recipes.directions`) is. \"Short\" if <= 3 directions, \"Average\" if 4-6 directions inclusive. \"Long\" if more than 6 directions. Get first 50 results sorted by `title`', async () => {\n checkExerciseAttempted(s5s1e1);\n const expected = await answers1.query(\"SELECT * FROM s5s1e1;\");\n expect(s5s1e1).to.deep.equal(expected);\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2139,"value":"*Why I couldn't get the \"correct\" answer during the workshop: There was a small error when getting the correct answer that was saved to the notebook. Fixed it.*","pinned":false,"hidden":false,"mode":"md"},{"id":1226,"value":"SELECT\n title, \n CASE\n WHEN length(directions) <= 3 THEN 'Short'\n WHEN length(directions) > 3 AND length(directions) <= 6 THEN 'Average'\n WHEN length(directions) > 6 THEN 'Long'\n END AS answer\nFROM recipes\nORDER BY title\nLIMIT 50\n","pinned":true,"hidden":false,"mode":"sql","output":"s5s1e1","database":"var:client"},{"id":1060,"value":"---\n### Get the results in your desired output format using `FORMAT`\n\nClickHouse supports a vast variety of input and output formats. See the list [here](https://clickhouse.com/docs/en/sql-reference/formats). It uses `TabSeparated` by default (`PrettyCompat` if you're using clickhouse-client interactively). \n\nSyntax: `FORMAT `\n\n```sql\nSELECT * FROM trips WHERE cab_type = 'green' LIMIT 10 FORMAT CSVWithNames;\n```\n\nOutput:\n```csv\n${await fetch(\n \"https://play.clickhouse.com/?add_http_cors_header=1&user=play&default_format=JSON\",\n {\n method: \"POST\",\n body: \"SELECT * FROM trips WHERE cab_type = 'green' LIMIT 10 FORMAT CSVWithNames;\"\n }\n ).then((res) => res.text())\n}\n```\n\n#### Read More\n- [List of formats](https://clickhouse.com/docs/en/sql-reference/formats)\n- https://clickhouse.com/docs/en/sql-reference/statements/select/format/","pinned":false,"hidden":false,"mode":"md"},{"id":1088,"value":"---\n### Save to file with `INTO OUTFILE`\n\n`INTO OUTFILE file_name [COMPRESSION type]` saves the results into a file on the **client** side. It's able to infer compression type based on file extension or you can input it explicitly.\n\n```sql\nSELECT \n * \nFROM trips \nWHERE cab_type = 'green' \nLIMIT 10 \nINTO OUTFILE 'my-result.csv.gz'\nFORMAT CSVWithNames;\n```\n\nAlternatively, you can redirect output from your command-line process into a file. For example:\n\n```bash\n$ clickhouse-client --query \"SELECT * FROM table\" --format CSVWithNames > output.csv\n```\n\n#### Read More\n- https://clickhouse.com/docs/en/faq/integration/file-export/\n- https://clickhouse.com/docs/en/sql-reference/statements/select/into-outfile/","pinned":false,"hidden":false,"mode":"md"},{"id":54,"value":"$32","pinned":false,"hidden":false,"mode":"md"},{"id":1133,"value":"SELECT\n m.id AS menu_id,\n mp.id AS menu_page_id,\n m.name AS menu_name,\n mp.full_height AS full_height,\n mp.full_width AS full_width\nFROM menu_page AS mp\nINNER JOIN menu AS m\nON mp.menu_id = m.id\nWHERE menu_name != ''\nLIMIT 30;","pinned":true,"hidden":false,"mode":"sql","output":"data","database":"var:client"},{"id":1182,"value":"When we print out the pages of each menu, we need to know the names and prices of each dish. \n\nThe `dish` table has a many-to-many relationship with the `menu_page` table using the `menu_item` as a junction table. The dish name is stored in the `dish` table, and the price is stored in the `menu_item` table.","pinned":false,"hidden":false,"mode":"md"},{"id":1181,"value":"SELECT\n mp.menu_id,\n mp.page_number,\n d.name AS dish_name,\n mi.price AS dish_price\nFROM dish AS d\nINNER JOIN menu_item AS mi\nON d.id = mi.dish_id\nINNER JOIN menu_page AS mp\nON mp.id = mi.menu_page_id\nWHERE dish_price != 0\nORDER BY mp.menu_id, mp.page_number\nLIMIT 50","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1199,"value":"#### Exercises","pinned":false,"hidden":false,"mode":"md"},{"id":1240,"value":"test(\"get the image ids of all menu_pages for menus that have four pages total (`menu.page_count`) and have a sponsor (`menu.sponsor`) containing 'LLOYD' somewhere in its name. Sort by the menu_id and page number. Get the first 50 results. Name the result column as `answer`\", async () => {\n checkExerciseAttempted(s8s1e1);\n const expected = await answers2.query(\"SELECT * FROM s8s1e1\");\n expect(s8s1e1).to.deep.equal(expected);\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1202,"value":"SELECT\n mp.image_id AS answer\nFROM menu_page AS mp\nINNER JOIN menu AS m\nON mp.menu_id = m.id\nWHERE menu.sponsor LIKE '%LLOYD%' AND menu.page_count = 4\nORDER BY menu_id, page_number\nLIMIT 50","pinned":true,"hidden":false,"mode":"sql","output":"s8s1e1","database":"var:client"},{"id":1259,"value":"test(\"Get all the unique places (`menu.place`) that serve 'Radishes' (`dish.name`). Get the first 50 results sorted by menu.id. Name the result column as `answer`\", async () => {\n checkExerciseAttempted(s8s2e1);\n const expected = await answers2.query(\"SELECT * FROM s8s2e1\");\n expect(s8s2e1).to.deep.equal(expected);\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1218,"value":"SELECT\n DISTINCT place AS answer\nFROM menu AS m\nINNER JOIN menu_page AS mp\nON mp.menu_id = m.id \nINNER JOIN menu_item AS mi\nON mp.id = mi.menu_page_id\nINNER JOIN dish AS d\nON mi.dish_id = d.id\nWHERE d.name = 'Radishes'\nORDER BY m.id\nLIMIT 50","pinned":true,"hidden":false,"mode":"sql","output":"s8s2e1","database":"var:client"},{"id":1276,"value":"test(\"Get all unique names of dishes (`dish.name`) that are served for 'BREAKFAST' (`menu.event`). Get the first 50 results sorted by `dish.id`. Name the result column as `answer`\", async () => {\n checkExerciseAttempted(s8s3e1);\n const expected = await answers2.query(\"SELECT * FROM s8s3e1\");\n expect(s8s3e1).to.deep.equal(expected);\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2144,"value":"*Why I couldn't get the \"correct\" answer during the workshop: It was unclear what name the result column should have and originally the answers assumed that it would be named `d.name`*","pinned":false,"hidden":false,"mode":"md"},{"id":1267,"value":"SELECT\n DISTINCT d.name AS answer\nFROM menu AS m\nINNER JOIN menu_page AS mp\nON mp.menu_id = m.id \nINNER JOIN menu_item AS mi\nON mp.id = mi.menu_page_id\nINNER JOIN dish AS d\nON mi.dish_id = d.id\nWHERE m.event = 'BREAKFAST'\nORDER BY d.id\nLIMIT 50","pinned":true,"hidden":false,"mode":"sql","output":"s8s3e1","database":"var:client"},{"id":2111,"value":"## Stopped here 2023-02-13","pinned":false,"hidden":false,"mode":"md"},{"id":62,"value":"## Aggregation\n\nClickHouse (as well as other SQL DBMS's) offer a variety of functions that can be used to summarize your data. ClickHouse provides functions for easily finding the `sum`, `avg`, `quantile(level)`, `min`, `max`, `count`, `stddevPop`, and more.\n\nWe'll look at a few examples. [You can find the complete list here](https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/)\n\n","pinned":false,"hidden":false,"mode":"md"},{"id":1331,"value":"SELECT count(), sum(fare_amount), sum(tip_amount) FROM trips;","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1334,"value":"That's a lot of taxi rides and a lot of money.\n\nNotice that we got one row that summarizes all the rows of the table for those columns;","pinned":false,"hidden":false,"mode":"md"},{"id":1329,"value":"#### Read More\n- [ClickHouse Documentation](https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/)\n- [Video Tutorial Using Postgres](https://youtu.be/jcoJuc5e3RE)\n\n#### Exercises\n\nHere is the description of the `stock` table\n\n${await client.describe('stock')}","pinned":false,"hidden":false,"mode":"md"},{"id":1416,"value":"test(\"What is the most expensive a stock has ever been on the `stock` table? Name the result column as answer.\", () => {\n checkExerciseAttempted(s9s1e1);\n expect(s9s1e1).to.deep.equal([{ answer: 9963.24 }]);\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1396,"value":"SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY","pinned":true,"hidden":false,"mode":"sql","output":"s9s1e1","database":"var:client"},{"id":1377,"value":"test(\"Get the average price of all stocks (`stock.price`) on '2000-01-01'. Round the answer to two decimal places using the `round` function. Name this column as `answer`\", () => {\n checkExerciseAttempted(s9s1e2);\n expect(s9s1e2).to.deep.equal([{ answer: 21.94 }]);\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1406,"value":"*Note: `WHERE` is applied before aggregation. So `WHERE` can filter out rows that you don't want in the aggregate calculation.*","pinned":false,"hidden":false,"mode":"md"},{"id":1370,"value":"SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY","pinned":true,"hidden":false,"mode":"sql","output":"s9s1e2","database":"var:client"},{"id":1313,"value":"---\n### `GROUP BY` and `HAVING`\n\nWe can group the rows by a particular value in a column before aggregation.","pinned":false,"hidden":false,"mode":"md"},{"id":1433,"value":"SELECT\n cab_type,\n payment_type_,\n count(),\n sum(fare_amount),\n sum(tip_amount)\nFROM trips \nGROUP BY cab_type, payment_type_ \nORDER BY cab_type, 3 DESC;","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1431,"value":"So now we can see a majority of those rides were yellow cabs that were paid with the 'CSH' or 'CRE' payment methods.\n\nWhat if we only wanted the rows with a total `fare_amount` of less than 10 million? The `HAVING` clause can filter out results after aggregation has occurred. ","pinned":false,"hidden":false,"mode":"md"},{"id":1454,"value":"SELECT\n cab_type,\n payment_type_,\n sum(fare_amount) AS sum_fare\nFROM trips \nGROUP BY cab_type, payment_type_ \nHAVING sum_fare < 10000000\nORDER BY cab_type, 3 DESC;","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1452,"value":"#### Read More\n- [Video Tutorial Using Postgres](https://www.youtube.com/watch?v=nNrgRVIzeHg)\n\n#### Exercise","pinned":false,"hidden":false,"mode":"md"},{"id":1466,"value":"test(\"Get the ticker symbols (`stock.symbol`) of the stocks with the top 10 most expensive prices (`stock.price`) of all time\", () => {\n checkExerciseAttempted(s9s2e1);\n expect(s9s2e1).to.deep.equal([\n { symbol: \"RBAK\" },\n { symbol: \"SEB\" },\n { symbol: \"WPO\" },\n { symbol: \"NVR\" },\n { symbol: \"GIW\" },\n { symbol: \"WTM\" },\n { symbol: \"INFY\" },\n { symbol: \"QCOM\" },\n { symbol: \"MCHXP\" },\n { symbol: \"MTB\" }\n ]);\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1424,"value":"SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY","pinned":true,"hidden":false,"mode":"sql","output":"s9s2e1","database":"var:client"},{"id":1315,"value":"---\n### Combinators\n\nThis is a ClickHouse-specific feature. Suffixes can be appended to the end of Aggregate functions in ClickHouse to modify the behavior of the aggregate function. For example, `-If` turns `count` into `countIf`. It will only count rows that meet a certain condition. \n\nMany more combinators are offered by ClickHouse, but since they are well-described in the documentation, we won't cover them more here.\n\nThe following query gets records of when an individual stock price was over 1000.","pinned":false,"hidden":false,"mode":"md"},{"id":1482,"value":"SELECT countIf(price > 1000) FROM stock","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1488,"value":"#### Read More\n- [ClickHouse Documentation](https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators)","pinned":false,"hidden":false,"mode":"md"},{"id":58,"value":"## Subqueries\n\nA query within a query. Subqueries use the result of one query to process another query.\nThey can be used in a variety of places including the `FROM` and `WHERE` clauses as well as in expression list of the `SELECT` statement. \n\nLets use the `hackernews` table\n\n${await client.describe('hackernews')}\n\n---\n\n### Subquery with `FROM`\n\nWe can make a query that gets information from a result set of another query. This is similar to and possibly less performant than the CTE we will cover when we discuss the `WITH` clause below. \n\nThe following is a relatively impractical query for real life, but I hope this conveys an example of how the subquery works. It gets 100 posts from hackernews and another query discards one of the columns and some rows from the result set.","pinned":false,"hidden":false,"mode":"md"},{"id":1494,"value":"SELECT type, by FROM (\n SELECT \n type, \n by, \n text \n FROM hackernews \n LIMIT 100\n)\nWHERE by != 'pg'","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1511,"value":"#### Read (Watch) More\n- https://youtu.be/m1KcNV-Zhmc\n- https://www.youtube.com/watch?v=GpC0XyiJPEo","pinned":false,"hidden":false,"mode":"md"},{"id":1514,"value":"#### Exercise","pinned":false,"hidden":false,"mode":"md"},{"id":1524,"value":"test(\"Count number of records on hackernews grouped by type (`hackernews.type`) and authorship (`hackernews.by`). Do this for only the earliest 100 records (`hackernews.time`) saved in the table. Omit the posts that don't have an author when getting the 100 records. The answer should consist of three columns `type`, `by`, and `count`\", async () => {\n checkExerciseAttempted(s10s1e1);\n const expected = await answers2.query(\"SELECT * FROM s10s1e1\");\n expect(s10s1e1).to.deep.equal(expected);\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1517,"value":"SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY","pinned":true,"hidden":false,"mode":"sql","output":"s10s1e1","database":"var:client"},{"id":1300,"value":"---\n### Subquery with `WHERE`\n\nThe part of expressions in the `WHERE` clause can also be a result of a query. I find this particularly useful when using the `IN` operator and working with multiple tables.\n\nIf I had a list of authors I was following like `my_subscriptions` below,\n\n> ```sql\nSELECT * FROM my_subscriptions;\n```\n\n> | username |\n> | --- |\n> | pg |\n> | phyllis |\n> | perler |\n\nThen we can get all the posts from authors I'm subscribed to like so.\n\n```sql\nSELECT type, text FROM hackernews\nWHERE by IN (SELECT * FROM my_subscriptions);\n```\n\nA similar result can be achieved using a join as described in a previous section.\n\n#### Exercise\n\nHere's a description of the `primes` table.\n\n${await client.describe('primes')}","pinned":false,"hidden":false,"mode":"md"},{"id":1547,"value":"test(\"Get the titles (`hackernews.title`) of the posts on hackernews whose score (`hackernews.score`) is one of the first 11th through 20th prime numbers. Name the result column as answer. Get only the first 100 columns.\", async () => {\n checkExerciseAttempted(s10s2e1);\n const expected = await answers2.query(\"SELECT * FROM s10s2e1\");\n expect(s10s2e1).to.deep.equal(expected);\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1533,"value":"SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY","pinned":true,"hidden":false,"mode":"sql","output":"s10s2e1","database":"var:client"},{"id":1304,"value":"---\n### Subquery with `SELECT`\n\nWe can use results of other queries within the expression list in the `SELECT` statement.\n\nWhile this may be better achieved by a window function as described in the next section, we can compute the average price for a stock in the table and see if the stock price of the current row is above or below average.","pinned":false,"hidden":false,"mode":"md"},{"id":1591,"value":"SELECT\n symbol,\n date,\n price,\n (SELECT avg(price) FROM stock) AS average_price,\n price > average_price \n ? 'above average' \n : 'below or equal to average' AS above_or_below\nFROM stock\nLIMIT 10;","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1321,"value":"---\n### `WITH`\n\nThe `WITH` clause helps organize your subqueries, storing their results in a temporary named result set. This is also known as a Common Table Expression or CTE.\n\nSyntax: `WITH AS `\n\nWe can rewrite our examples from above.\n\n#### The example using `FROM`\n```sql\nSELECT type, by FROM (\n SELECT \n type, \n by, \n text \n FROM hackernews \n LIMIT 100\n)\nWHERE by != 'pg'\n```","pinned":false,"hidden":false,"mode":"md"},{"id":1603,"value":"WITH first_100 AS (\n SELECT \n type, \n by, \n text \n FROM hackernews \n LIMIT 100\n)\nSELECT type, by FROM first_100\nWHERE by != 'pg'","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1608,"value":"#### The Example Using `WHERE`\n\n```sql\nSELECT type, text FROM hackernews\nWHERE by IN (SELECT * FROM my subscriptions);\n```\n\nWould become\n\n```sql\nWITH my_subs AS (SELECT * FROM my_subscriptions)\nSELECT type, text FROM hackernews\nWHERE by IN my_subs;\n```\n\n#### Constant Expressions in `WITH`\n\nWhat about our example using `SELECT`? That subquery returned a single value--the average. It's less of a result table and more of a constant value. The syntax for using `WITH` as an alias for constant values is a bit different. And it can be used with literal values as well as subqueries. \n\nThere's also a subtle benefit of having calculated the value, but hiding those values from the result set if you don't need to receive those values within the results.\n\n#### The Example Using `SELECT`\n\n```sql\nSELECT\n symbol,\n date,\n price,\n (SELECT avg(price) FROM stock) AS average_price\nFROM stock\nLIMIT 10;\n```\n\nWould become","pinned":false,"hidden":false,"mode":"md"},{"id":1615,"value":"WITH (SELECT avg(price) FROM stock) AS average_price\nSELECT\n symbol,\n date,\n price,\n average_price\nFROM stock\nLIMIT 10;","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1632,"value":"We can also alias value literals like so:","pinned":false,"hidden":false,"mode":"md"},{"id":1634,"value":"WITH \n 'A' AS my_symbol\nSELECT\n date,\n max(price)\nFROM stock\nWHERE symbol = my_symbol AND date < '2000-01-01'\nGROUP BY date\nORDER BY date","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1600,"value":"\n#### Read (or Watch) More\n- [Video Tutorial using Postgres](https://www.youtube.com/watch?v=_SanZ41uTlw)\n- [ClickHouse Documentation](https://clickhouse.com/docs/en/sql-reference/statements/select/with)","pinned":false,"hidden":false,"mode":"md"},{"id":64,"value":"## Window Functions and the `OVER` clause\n\nWindow functions allow you to perform an operation over a set of subset of rows called a window. Generally there are three types of window functions:\n* Aggregate window functions (ie `sum`) perform a calculation the data in the column.\n* Ranking window functions (ie `rank`, `row_number`) numbers the rows using some rule\n* Value window functions (ie `lag`, `lead`) get a value from a row before or after the current row.\n\nLet's see how to define the window\n\nSyntax:\n\n```sql\n(...) OVER (\n PARTITION BY \n ORDER BY \n [ RANGE | ROWS ] BETWEEN frame_start AND frame_end\n)\n```\n","pinned":false,"hidden":false,"mode":"md"},{"id":1653,"value":"SELECT\n number,\n sum(number) OVER () AS total\nFROM numbers(3) -- the numbers from 0 through 2","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1684,"value":"#### Read More\n- https://www.sqltutorial.org/sql-window-functions/\n","pinned":false,"hidden":false,"mode":"md"},{"id":1659,"value":"\n### `PARTITION BY`\n\nFunctions much like a `GROUP BY` when splitting rows into groups.","pinned":false,"hidden":false,"mode":"md"},{"id":1664,"value":"SELECT\n number,\n number % 2 = 0 AS is_even,\n sum(number) OVER (PARTITION BY is_even)\nFROM numbers(10)","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1688,"value":"\n### `ORDER BY`\n\n`ORDER BY` sorts the rows within the partition. With this sort order we can calculate running averages and number the rows.","pinned":false,"hidden":false,"mode":"md"},{"id":1692,"value":"SELECT \n number,\n rand() AS random_number,\n row_number() OVER (ORDER BY number) AS sort_order,\n avg(random_number) OVER (ORDER BY number) AS running_average, \n sum(random_number) OVER (ORDER BY number) AS running_total\nFROM \n numbers(10) -- numbers from 0 thru 9","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1699,"value":"### The Sliding Window\n\n${await FileAttachment(\"sliding-window@1.png\").image()}\n\nYou may wonder, how does it know where to start summing and finish summing for the running totals?\n\nWe can specify the number of rows before and after the current row to use for the calculation.","pinned":false,"hidden":false,"mode":"md"},{"id":1702,"value":"SELECT\n number,\n anyOrNull(number) OVER (ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) one_before,\n anyOrNull(number) OVER (ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) one_after,\n sum(number) OVER (ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) sum_this_and_prev, \n avg(number) OVER (ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) avg_this_and_next,\n groupArray(number) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) window_unbounded_prev\nFROM numbers(5)","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1718,"value":"---\n\n#### `RANGE` vs `ROWS` \n\n- `RANGE` calculates a result for each distinct value\n- `ROWS` calculates a result for each row","pinned":false,"hidden":false,"mode":"md"},{"id":1725,"value":"SELECT\n intDiv(number, 2) as div_2,\n sum(div_2) OVER (ORDER BY div_2 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_by_rows,\n sum(div_2) OVER (ORDER BY div_2 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_by_range,\n groupArray(div_2) OVER (ORDER BY div_2 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS window_by_range\nFROM numbers(10)","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1736,"value":"\n---\n### Examples\n\nLet's go through some examples with different types of window functions using the syntax we just learned.\n\n#### Aggregation Window Functions\n\nThe majority of the examples we used earlier aggregated the rows within the window.\n\nHere we can calculate the average price over all time for each stock, then calculate an average of the last 10 business days. I shortened \"all time\" to just the year of 1999 so that the data we look at is more managable.","pinned":false,"hidden":false,"mode":"md"},{"id":1758,"value":"SELECT \n symbol,\n date,\n price,\n avg(price) OVER (PARTITION BY symbol) AS avg_all,\n avg(price) OVER (\n PARTITION BY symbol\n ORDER BY date\n RANGE 10 PRECEDING\n ) AS ten_day_moving_avg,\n avg_all < ten_day_moving_avg ? 'better than avg_all' : 'worse than avg_all' AS better_or_worse\nFROM stock\nWHERE date >= '1999-01-01' AND date <= '1999-12-31'\nLIMIT 500;","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1962,"value":"#### Exercise","pinned":false,"hidden":false,"mode":"md"},{"id":1865,"value":"test(\"For the stock with symbol = 'BKR', and using the max of `stock.high` for the week (week max price) and the min of `stock.low` for the week (week min price), calculate how many dollars above the week min price the price each day is. Name this column `over_min`. Calculate how many dollars under the week max price the price each day is. Name this column `under_max`. Sort the result set by date.\", async () => {\n checkExerciseAttempted(s11s5e1);\n const expected = (await answers2.query(\"SELECT * FROM s11s5e1\")).map((x) => ({\n over_min: +x.over_min,\n under_max: +x.under_max\n }));\n expect(s11s5e1).to.deep.equal(expected);\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1837,"value":"SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY","pinned":true,"hidden":false,"mode":"sql","output":"s11s5e1","database":"var:client"},{"id":1744,"value":"\n\n---\n#### Ranking window function\n\nOrdering is useful when we're trying to get the rows with the n highest or lowest values.\n\nThe most common ranking functions are `row_number()`, `rank()`, and `dense_rank()`. \n\n- `row_number` - the number of the current row given a particular order. Ties are ignored.\n- `rank` - the nth highest given a particular sort order. Skips positions in the event of ties.\n- `dense_rank` - the nth highest given a particular sort order. Does not skip positions in the event of ties.\n\n`ntile` is another useful ranking window function. It is available in many SQL databases, but it's not supported by ClickHouse.","pinned":false,"hidden":false,"mode":"md"},{"id":1771,"value":"SELECT \n intDiv(number, 2) AS n,\n row_number() OVER (ORDER BY n DESC),\n rank() OVER (ORDER BY n DESC),\n dense_rank() OVER (ORDER BY n DESC)\nFROM numbers(10)","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1769,"value":"\n\nLet's get the highest price ever reached for each stock in the stock table and the date on which that happened.","pinned":false,"hidden":false,"mode":"md"},{"id":1738,"value":"SELECT\n symbol,\n date,\n price\nFROM (\n SELECT\n symbol,\n date,\n price,\n dense_rank() OVER (PARTITION BY symbol ORDER BY price DESC) AS nth_place\n FROM stock\n)\nWHERE nth_place = 1\nORDER BY price DESC","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1767,"value":"#### Read More\n- https://learnsql.com/cookbook/whats-the-difference-between-rank-and-dense_rank-in-sql/\n\n#### Exercise","pinned":false,"hidden":false,"mode":"md"},{"id":1885,"value":"test(\"Get the date, location_key, and new_confirmed for the location (`covid.location_key`) with the most new confirmed cases (`covid.new_confirmed`) for each day between Jan 1st - Apr 30th 2021\", async () => {\n checkExerciseAttempted(s11s5e2);\n const expected = (await answers2.query(\"SELECT * FROM s11s5e2\")).map((x) => ({\n ...x,\n new_confirmed: +x.new_confirmed\n }));\n expect(s11s5e2).to.deep.equal(expected);\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1873,"value":"SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY","pinned":true,"hidden":false,"mode":"sql","output":"s11s5e2","database":"var:client"},{"id":1650,"value":"\n\n---\n#### Value Window Functions (`LAG`/`LEAD`)\n\nThese functions can get a value that comes either before or after the current row.\n\nThese are not supported by ClickHouse. However their functionality can be achieved using the `any` aggregate function as we did in the sliding window section above as well as the `lagInFrame` and `leadInFrame` functions. [See Documentation](https://clickhouse.com/docs/en/sql-reference/window-functions/)\n\n#### More Practice\n\n- https://www.windowfunctions.com/questions/intro/","pinned":false,"hidden":false,"mode":"md"},{"id":1946,"value":"SELECT\n number,\n anyOrNull(number) OVER (ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS lag,\n anyOrNull(number) OVER (ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS lead\nFROM numbers(5) -- numbers 0 thru 4","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1950,"value":"SELECT\n number,\n lagInFrame(number, 1, 255) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lag,\n leadInFrame(number, 1, 255) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lead\nFROM numbers(5)","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":94,"value":"## Appendix","pinned":false,"hidden":false,"mode":"md"},{"id":5,"value":"import { client } from '@stas-sl/clickhouse-playground'","pinned":false,"hidden":false,"mode":"ojs"},{"id":17,"value":"import { vl } from \"@vega/vega-lite-api\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":28,"value":"import {toc} from \"@nebrius/indented-toc\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":867,"value":"answers1 = FileAttachment(\"answers1@1.db\").sqlite()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1964,"value":"answers2 = FileAttachment(\"answers2@1.db\").sqlite()","pinned":false,"hidden":false,"mode":"ojs"},{"id":87,"value":"import { test, expect } from \"@samchencode/utilities\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":697,"value":"class UnattemptedExerciseError extends Error {\n name = \"Exercise Not Attempted\";\n constructor() {\n super(\"Modify the SQL Cell below to get started!\");\n }\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":700,"value":"function checkExerciseAttempted(rows) {\n const firstVal = _.values(rows[0])[0];\n if (firstVal === \"Hello World\") {\n throw new UnattemptedExerciseError();\n }\n}","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W33","stdlib":"1","titling":"auto"}