10
client
SELECT * FROM stock LIMIT 10
client
DESCRIBE TABLE recipes
client
SHOW TABLES
client
SHOW DATABASES
client
SELECT
rand(0) AS a,
rand(1) AS b,
a + b AS added,
a - b AS subtracted,
a * b AS multiplied,
a / b AS divided,
a % b AS moded,
abs(subtracted)
FROM numbers(10);
client
SELECT
rand(0) AS a,
rand(1) AS b,
a > b AS gt,
a < b AS lt,
a = b AS eq,
a != b AS ne
FROM numbers(10);
client
SELECT
dish_name,
dish_highest_price - dish_lowest_price AS range
FROM menu_item_denorm
WHERE dish_highest_price != 0 AND dish_lowest_price != 0
ORDER BY menu_id, dish_id
LIMIT 1000
client
SELECT
title,
source,
length(title),
concat(source, '-', trim(title)),
lower(title) LIKE '%peruvian%'
FROM recipes
ORDER BY title
client
SELECT
'https://' || link AS answer
FROM recipes
client
SELECT
now(),
today(),
dateDiff('hour', yesterday(), today()) AS hrs_btn_td_ytd,
today() + INTERVAL 1 day AS tomorrow,
today() - INTERVAL 1 year AS yester_year,
formatDateTime(now(), '%D at %I:%M:%S %p', 'EST')
client
SELECT
number,
[1,5,2,3,2,4,5,1] AS arr,
empty(arr),
length(arr),
has(arr, number),
indexOf(arr, number),
arrayDistinct(arr)
FROM numbers(10) -- numbers 0 thru 9
client
SELECT
title
FROM recipes
WHERE has(ingredients, '1 coke')
client
SELECT
CAST(1234 AS String) AS my_string,
CAST('1970-01-01' AS Date) AS my_date,
CAST('1899-01-01' AS Date) AS my_wrong_date,
CAST(0 AS BOOLEAN) AS my_false,
CAST(1 AS BOOLEAN) AS my_true
client
SELECT concat(CAST(number AS String), '%') AS answer FROM numbers(10)
client
SELECT
title,
length(ingredients) > 3 ? 'four or more ingredients' : 'three or less ingredients' AS category,
source,
CASE
WHEN source ='Gathered' THEN 'the internet'
WHEN source = 'my brain' THEN 'me'
ELSE 'idk'
END AS was_gathered
FROM recipes;
client
SELECT
title,
CASE
WHEN length(directions) <= 3 THEN 'Short'
WHEN length(directions) > 3 AND length(directions) <= 6 THEN 'Average'
WHEN length(directions) > 6 THEN 'Long'
END AS answer
FROM recipes
ORDER BY title
LIMIT 50
client
SELECT
m.id AS menu_id,
mp.id AS menu_page_id,
m.name AS menu_name,
mp.full_height AS full_height,
mp.full_width AS full_width
FROM menu_page AS mp
INNER JOIN menu AS m
ON mp.menu_id = m.id
WHERE menu_name != ''
LIMIT 30;
client
SELECT
mp.menu_id,
mp.page_number,
d.name AS dish_name,
mi.price AS dish_price
FROM dish AS d
INNER JOIN menu_item AS mi
ON d.id = mi.dish_id
INNER JOIN menu_page AS mp
ON mp.id = mi.menu_page_id
WHERE dish_price != 0
ORDER BY mp.menu_id, mp.page_number
LIMIT 50
client
SELECT
mp.image_id AS answer
FROM menu_page AS mp
INNER JOIN menu AS m
ON mp.menu_id = m.id
WHERE menu.sponsor LIKE '%LLOYD%' AND menu.page_count = 4
ORDER BY menu_id, page_number
LIMIT 50
client
SELECT
DISTINCT place AS answer
FROM menu AS m
INNER JOIN menu_page AS mp
ON mp.menu_id = m.id
INNER JOIN menu_item AS mi
ON mp.id = mi.menu_page_id
INNER JOIN dish AS d
ON mi.dish_id = d.id
WHERE d.name = 'Radishes'
ORDER BY m.id
LIMIT 50
client
SELECT
DISTINCT d.name AS answer
FROM menu AS m
INNER JOIN menu_page AS mp
ON mp.menu_id = m.id
INNER JOIN menu_item AS mi
ON mp.id = mi.menu_page_id
INNER JOIN dish AS d
ON mi.dish_id = d.id
WHERE m.event = 'BREAKFAST'
ORDER BY d.id
LIMIT 50
client
SELECT count(), sum(fare_amount), sum(tip_amount) FROM trips;
client
SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY
client
SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY
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;
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 'Hello World'; -- REPLACE THIS WITH YOUR QUERY
client
SELECT countIf(price > 1000) FROM stock
client
SELECT type, by FROM (
SELECT
type,
by,
text
FROM hackernews
LIMIT 100
)
WHERE by != 'pg'
client
SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY
client
SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY
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
SELECT
date,
max(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)
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) 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 CURRENT ROW) 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
SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY
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 'Hello World'; -- REPLACE THIS WITH YOUR QUERY
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.