The avatar for @samchencode
samchencode
Public
Spring 2023 ClickHouse Workshop
Edited
Feb 20, 2023
1 fork
4
Insert cell
Insert cell
client
SELECT * FROM stock LIMIT 10
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
client
SELECT count(), sum(fare_amount), sum(tip_amount) FROM trips;
Insert cell
Insert cell
Insert cell
Insert cell
client
SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY
Insert cell
Insert cell
Insert cell
client
SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY
Insert cell
Insert cell
client
SELECT
cab_type,
payment_type_,
count(),
sum(fare_amount),
sum(tip_amount)
FROM trips
GROUP BY cab_type, payment_type_
ORDER BY cab_type, 3 DESC;
Insert cell
Insert cell
client
SELECT
cab_type,
payment_type_,
sum(fare_amount) AS sum_fare
FROM trips
GROUP BY cab_type, payment_type_
HAVING sum_fare < 10000000
ORDER BY cab_type, 3 DESC;
Insert cell
Insert cell
Insert cell
client
SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY
Insert cell
Insert cell
client
SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY
Insert cell
Insert cell
client
SELECT countIf(price > 1000) FROM stock
Insert cell
Insert cell
Insert cell
client
SELECT type, by FROM (
SELECT
type,
by,
text
FROM hackernews
LIMIT 100
)
WHERE by != 'pg'
Insert cell
Insert cell
Insert cell
Insert cell
client
SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY
Insert cell
Insert cell
Insert cell
client
SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY
Insert cell
Insert cell
client
SELECT
symbol,
date,
price,
(SELECT avg(price) FROM stock) AS average_price,
price > average_price
? 'above average'
: 'below or equal to average' AS above_or_below
FROM stock
LIMIT 10;
Insert cell
Insert cell
client
WITH first_100 AS (
SELECT
type,
by,
text
FROM hackernews
LIMIT 100
)
SELECT type, by FROM first_100
WHERE by != 'pg'
Insert cell
Insert cell
client
WITH (SELECT avg(price) FROM stock) AS average_price
SELECT
symbol,
date,
price,
average_price
FROM stock
LIMIT 10;
Insert cell
Insert cell
client
WITH
'A' AS my_symbol
SELECT
date,
max(price)
FROM stock
WHERE symbol = my_symbol AND date < '2000-01-01'
GROUP BY date
ORDER BY date
Insert cell
Insert cell
Insert cell
client
SELECT
number,
sum(number) OVER () AS total
FROM numbers(3) -- the numbers from 0 through 2
Insert cell
Insert cell
Insert cell
client
SELECT
number,
number % 2 = 0 AS is_even,
sum(number) OVER (PARTITION BY is_even)
FROM numbers(10)
Insert cell
Insert cell
client
SELECT
number,
rand() AS random_number,
row_number() OVER (ORDER BY number) AS sort_order,
avg(random_number) OVER (ORDER BY number) AS running_average,
sum(random_number) OVER (ORDER BY number) AS running_total
FROM
numbers(10) -- numbers from 0 thru 9
Insert cell
Insert cell
client
SELECT
number,
anyOrNull(number) OVER (ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) one_before,
anyOrNull(number) OVER (ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) one_after,
sum(number) OVER (ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) sum_this_and_prev,
avg(number) OVER (ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) avg_this_and_next,
groupArray(number) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) window_unbounded_prev
FROM numbers(5)
Insert cell
Insert cell
client
SELECT
intDiv(number, 2) as div_2,
sum(div_2) OVER (ORDER BY div_2 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_by_rows,
sum(div_2) OVER (ORDER BY div_2 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_by_range,
groupArray(div_2) OVER (ORDER BY div_2 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS window_by_range
FROM numbers(10)
Insert cell
Insert cell
client
SELECT
symbol,
date,
price,
avg(price) OVER (PARTITION BY symbol) AS avg_all,
avg(price) OVER (
PARTITION BY symbol
ORDER BY date
RANGE 10 PRECEDING
) AS ten_day_moving_avg,
avg_all < ten_day_moving_avg ? 'better than avg_all' : 'worse than avg_all' AS better_or_worse
FROM stock
WHERE date >= '1999-01-01' AND date <= '1999-12-31'
LIMIT 500;
Insert cell
Insert cell
Insert cell
client
SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY
Insert cell
Insert cell
client
SELECT
intDiv(number, 2) AS n,
row_number() OVER (ORDER BY n DESC),
rank() OVER (ORDER BY n DESC),
dense_rank() OVER (ORDER BY n DESC)
FROM numbers(10)
Insert cell
Insert cell
client
SELECT
symbol,
date,
price
FROM (
SELECT
symbol,
date,
price,
dense_rank() OVER (PARTITION BY symbol ORDER BY price DESC) AS nth_place
FROM stock
)
WHERE nth_place = 1
ORDER BY price DESC
Insert cell
Insert cell
Insert cell
client
SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY
Insert cell
Insert cell
client
SELECT
number,
anyOrNull(number) OVER (ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS lag,
anyOrNull(number) OVER (ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS lead
FROM numbers(5) -- numbers 0 thru 4
Insert cell
client
SELECT
number,
lagInFrame(number, 1, 255) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lag,
leadInFrame(number, 1, 255) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lead
FROM numbers(5)
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell

One platform to build and deploy the best data apps

Experiment and prototype by building visualizations in live JavaScript notebooks. Collaborate with your team and decide which concepts to build out.
Use Observable Framework to build data apps locally. Use data loaders to build in any language or library, including Python, SQL, and R.
Seamlessly deploy to Observable. Test before you ship, use automatic deploy-on-commit, and ensure your projects are always up-to-date.
Learn more