2f:T525,# ClickHouse SQL Workshop Day 3 - Presenter Edition ${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:[{"id":"5acf54989c2155c1ad0c6dcb7433fc703e3464572d573c9873dc6d38863339d5e3178453f2ce927068ebe471a97c8e28cfdb1f927513263259c88bdcbfb653ec","href":"https://static.observableusercontent.com/files/5acf54989c2155c1ad0c6dcb7433fc703e3464572d573c9873dc6d38863339d5e3178453f2ce927068ebe471a97c8e28cfdb1f927513263259c88bdcbfb653ec","name":"clickhouse-mark-4238843745.png","type":"image/png","lastModified":1676933616886.125,"size":7715},{"id":"0929562ce162e386dc26525790e8774f0f109f5df5da30358bb06ff3497b336e5ef11cab52305f9ba26b55cb9d06d227bcf1c06110a4defb5dfd4c4667fe527a","href":"https://static.observableusercontent.com/files/0929562ce162e386dc26525790e8774f0f109f5df5da30358bb06ff3497b336e5ef11cab52305f9ba26b55cb9d06d227bcf1c06110a4defb5dfd4c4667fe527a","name":"sliding-window@1.png","type":"image/png","lastModified":1676933616886.125,"size":405793},{"id":"291ffdaaef1366e902439d072cbfd6c80c09852c66799b541a6daa1f69663fab2ba05f40b560f6c45ab174e3fd77e00f1bb2cdeacdad8ae3ae1ee69a1b26df5c","href":"https://static.observableusercontent.com/files/291ffdaaef1366e902439d072cbfd6c80c09852c66799b541a6daa1f69663fab2ba05f40b560f6c45ab174e3fd77e00f1bb2cdeacdad8ae3ae1ee69a1b26df5c","name":"answers1@1.db","type":"application/x-sqlite3","lastModified":1676933616886.125,"size":45056},{"id":"b372673816926edfc246ed5c65711b25dd7463ca0f56967cb7e7b90c292840c59dde20d4cd45b2e21f43333c08199535a56985fd681bd901941ab699435ab22c","href":"https://static.observableusercontent.com/files/b372673816926edfc246ed5c65711b25dd7463ca0f56967cb7e7b90c292840c59dde20d4cd45b2e21f43333c08199535a56985fd681bd901941ab699435ab22c","name":"answers2.db","type":"application/x-sqlite3","lastModified":1676933616886.125,"size":110592}] 2d:{"title":"ClickHouse SQL Workshop Day 3 - 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, \"Expected 10 rows of results.\");\n const columns = _.keys(query1[0]);\n expect(columns).to.deep.equal(\n [\"symbol\", \"date\", \"price\", \"volume\", \"open\", \"low\", \"high\"],\n \"Not all the expected columns are present.\"\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":2187,"value":"## Before We Start...\n\n1. Any questions?\n2. Let's review joins.","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(_.keys(s9s1e1[0])).to.deep.equal(\n [\"answer\"],\n \"Expected column to be named `answer`\"\n );\n expect(s9s1e1).to.deep.equal(\n [{ answer: 9963.24 }],\n \"Expected a different answer.\"\n );\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1396,"value":"SELECT max(price) AS answer FROM stock","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 '1992-08-12' (`stock.date`). 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 round(avg(price), 2) AS answer FROM stock WHERE date = '1992-08-12'","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 1, payment_type_ \nORDER BY 1, 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. Name the result column as `symbol`\", () => {\n checkExerciseAttempted(s9s2e1);\n expect(_.keys(s9s2e1[0])).to.deep.equal(\n [\"symbol\"],\n \"Expected a column named `symbol`\"\n );\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\n symbol\nFROM stock\nGROUP BY symbol \nORDER BY max(price) DESC\nLIMIT 10","pinned":true,"hidden":false,"mode":"sql","output":"s9s2e1","database":"var:client"},{"id":2210,"value":"test(\"On what dates (`stock.date`) was the average price (`stock.price`) of all stocks 34.01 when rounded to two decimal places? Name your result column as `answer`. Sort results with earlier dates at the top.\", () => {\n checkExerciseAttempted(s9s2e2);\n expect(_.keys(s9s2e2[0])).to.deep.equal(\n [\"answer\"],\n \"Expected a column named `answer`\"\n );\n expect(s9s2e2).to.deep.equal([\n { answer: \"1985-10-28\" },\n { answer: \"1989-07-27\" },\n { answer: \"1989-10-19\" }\n ], \"Expected a different answer.\");\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2212,"value":"SELECT\n date AS answer\nFROM stock\nGROUP BY date\nHAVING round(avg(price), 2) = 34.01","pinned":true,"hidden":false,"mode":"sql","output":"s9s2e2","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 count(), countIf(price > 1000), avgIf(price, price > 1000), avg(price) 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`. Sort your answer by `type` and `by` columns.\", async () => {\n checkExerciseAttempted(s10s1e1);\n expect(_.keys(s10s1e1[0])).to.deep.equal(\n [\"type\", \"by\", \"count\"],\n \"Expected three columns named `type`, `by`, and `count` in that order. Note that a column named `count()` is not the same as a column named `count`.\"\n );\n const expected = await answers2.query(\"SELECT * FROM s10s1e1\");\n expect(s10s1e1).to.deep.equal(expected, \"Expected a different answer.\");\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1517,"value":"SELECT \n type,\n by,\n count() AS count\nFROM (\n SELECT * FROM hackernews WHERE by != '' ORDER BY time LIMIT 100\n) GROUP BY type, by\nORDER BY type, by","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. Sort your answer by `hackernews.id`. Get only the first 100 columns.\", async () => {\n checkExerciseAttempted(s10s2e1);\n const expected = await answers2.query(\"SELECT * FROM s10s2e1\");\n const expectedColumns = _.keys(expected[0]);\n expect(_.keys(s10s2e1[0])).to.deep.equal(\n expectedColumns,\n `Expected column named ${_.keys(expected[0])[0]}`\n );\n expect(s10s2e1).to.deep.equal(expected, \"Expected a different answer\");\n})\n","pinned":false,"hidden":false,"mode":"ojs"},{"id":1533,"value":"SELECT title AS answer\nFROM hackernews \nWHERE score IN (SELECT n FROM primes LIMIT 10 OFFSET 10)\nORDER BY id \nLIMIT 100;","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,\n min(price) AS min_price\nSELECT\n date,\n max(price),\n max(price) - min_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) AS grp_sum,\n number - grp_sum\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 (\n ORDER BY number\n ) AS running_average, \n sum(random_number) OVER (ORDER BY number\n RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW\n ) 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 1 FOLLOWING) 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 expect(_.keys(s11s5e1[0])).to.deep.equal(\n [\"over_min\", \"under_max\"],\n \"Expected two columns named `over_min` and `under_max` in that order.\"\n );\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, \"Expected a different answer.\");\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1837,"value":"WITH\n max(stock.high) OVER (PARTITION BY toStartOfWeek(date)) AS week_max_price,\n min(stock.low) OVER (PARTITION BY toStartOfWeek(date)) AS week_min_price,\n price\nSELECT\n price - week_min_price AS over_min,\n week_max_price - price AS under_max\nFROM stock\nWHERE symbol = 'BKR'","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 const expectedColumns = _.keys(expected[0]);\n expect(_.keys(s11s5e2[0])).to.deep.equal(\n expectedColumns,\n `Expected columns named ${expectedColumns\n .map((n) => `\\`${n}\\``)\n .join(\", \")} in that order.`\n );\n expect(s11s5e2).to.deep.equal(expected, \"Expected a different answer\");\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1873,"value":"SELECT \n date,\n location_key,\n new_confirmed\nFROM (\n SELECT\n rank() OVER (PARTITION BY date ORDER BY new_confirmed DESC) as rnk,\n location_key,\n new_confirmed,\n date\n FROM covid\n)\nWHERE rnk = 1 AND date >= '2021-01-01' AND date <= '2021-04-30'\nORDER BY date \n","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.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":"$W30","stdlib":"1","titling":"auto"}