4
client
SELECT * FROM stock LIMIT 10
client
SELECT count(), sum(fare_amount), sum(tip_amount) FROM trips;
client
SELECT max(price) AS answer FROM stock
client
SELECT round(avg(price), 2) AS answer FROM stock WHERE date = '1992-08-12'
client
SELECT
cab_type,
payment_type_,
count(),
sum(fare_amount),
sum(tip_amount)
FROM trips
GROUP BY 1, payment_type_
ORDER BY 1, 3 DESC;
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;
client
SELECT
symbol
FROM stock
GROUP BY symbol
ORDER BY max(price) DESC
LIMIT 10
client
SELECT
date AS answer
FROM stock
GROUP BY date
HAVING round(avg(price), 2) = 34.01
client
SELECT count(), countIf(price > 1000), avgIf(price, price > 1000), avg(price) FROM stock
client
SELECT type, by FROM (
SELECT
type,
by,
text
FROM hackernews
LIMIT 100
)
WHERE by != 'pg'
client
SELECT
type,
by,
count() AS count
FROM (
SELECT * FROM hackernews WHERE by != '' ORDER BY time LIMIT 100
) GROUP BY type, by
ORDER BY type, by
client
SELECT title AS answer
FROM hackernews
WHERE score IN (SELECT n FROM primes LIMIT 10 OFFSET 10)
ORDER BY id
LIMIT 100;
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;
client
WITH first_100 AS (
SELECT
type,
by,
text
FROM hackernews
LIMIT 100
)
SELECT type, by FROM first_100
WHERE by != 'pg'
client
WITH (SELECT avg(price) FROM stock) AS average_price
SELECT
symbol,
date,
price,
average_price
FROM stock
LIMIT 10;
client
WITH
'A' AS my_symbol,
min(price) AS min_price
SELECT
date,
max(price),
max(price) - min_price
FROM stock
WHERE symbol = my_symbol AND date < '2000-01-01'
GROUP BY date
ORDER BY date
client
SELECT
number,
sum(number) OVER () AS total
FROM numbers(3) -- the numbers from 0 through 2
client
SELECT
number,
number % 2 = 0 AS is_even,
sum(number) OVER (PARTITION BY is_even) AS grp_sum,
number - grp_sum
FROM numbers(10)
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
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM
numbers(10) -- numbers from 0 thru 9
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 1 FOLLOWING) window_unbounded_prev
FROM numbers(5)
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)
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;
client
WITH
max(stock.high) OVER (PARTITION BY toStartOfWeek(date)) AS week_max_price,
min(stock.low) OVER (PARTITION BY toStartOfWeek(date)) AS week_min_price,
price
SELECT
price - week_min_price AS over_min,
week_max_price - price AS under_max
FROM stock
WHERE symbol = 'BKR'
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)
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
client
SELECT
date,
location_key,
new_confirmed
FROM (
SELECT
rank() OVER (PARTITION BY date ORDER BY new_confirmed DESC) as rnk,
location_key,
new_confirmed,
date
FROM covid
)
WHERE rnk = 1 AND date >= '2021-01-01' AND date <= '2021-04-30'
ORDER BY date
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
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)
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.