10
pizzeriaDB
SELECT
products.name,
products.size,
count(distinct orders.id) as num_orders
FROM orders
JOIN order_items
ON orders.id = order_items.orderID
JOIN products
ON order_items.SKU = products.SKU
GROUP BY 1, 2
ORDER BY 3 DESC
pizzeriaDB
SELECT name,
avg(orders) as avg_orders,
min(orders) as min_orders,
max(orders) as max_orders,
median(orders) as median_orders,
mode(orders) as mode_orders
FROM orders_by_product_day
GROUP BY name
pizzeriaDB
SELECT name,
ingredients,
size
FROM products
WHERE (ingredients NOT like '%Tomato%' AND size = 'Small')
pizzeriaDB
SELECT
products.name,
products.size,
count(distinct orders.id) as num_orders
FROM orders
JOIN order_items
ON orders.id = order_items.orderID
JOIN products
ON order_items.SKU = products.SKU
GROUP BY 1, 2
HAVING num_orders > 100000
ORDER BY 3 DESC
pizzeriaDB
SELECT sum(total) as total_revenue_by_month,
date_trunc('month', orderDate::date) as month_name
FROM orders
GROUP BY 2
ORDER BY 2
pizzeriaDB
SELECT
products.name,
count(distinct orders.id) as num_orders,
case when date_part('dayofweek', orderDate::date) == 0 then 'Sunday'
when date_part('dayofweek', orderDate::date) == 1 then 'Monday'
when date_part('dayofweek', orderDate::date) == 2 then 'Tuesday'
when date_part('dayofweek', orderDate::date) == 3 then 'Wednesday'
when date_part('dayofweek', orderDate::date) == 4 then 'Thursday'
when date_part('dayofweek', orderDate::date) == 5 then 'Friday'
when date_part('dayofweek', orderDate::date) == 6 then 'Saturday' end as day_of_week
FROM orders
JOIN order_items
ON orders.id = order_items.orderID
JOIN products
ON order_items.SKU = products.SKU
WHERE products.name = 'Pepperoni Pizza'
GROUP BY 1, 3
ORDER BY 2 DESC
pizzeriaDB
WITH dayorders AS (
SELECT orderDate::date as order_date,
count(distinct id) as num_orders
FROM orders
GROUP BY 1
)
SELECT order_date,
avg(num_orders) over
( order by order_date asc
range between interval 14 days preceding and interval 14 days following) as moving_avg
FROM dayorders
pizzeriaDB
WITH first_product_table AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY launch) AS first_product
FROM products
), joined_tables AS (
SELECT
orders.id,
orders.total,
orders.customerId,
first_product_table.name,
first_product_table.category,
first_product_table.size
FROM first_product_table
JOIN order_items ON order_items.SKU = first_product_table.SKU
JOIN orders ON orders.id = order_items.orderID
WHERE first_product = 1
)
SELECT name,
count(distinct id) as num_orders,
count(distinct customerId) AS num_customers,
sum(total) as total_generated_revenue
FROM joined_tables
GROUP BY 1
ORDER BY 2 DESC
pizzeriaDB
SELECT * FROM stores
WHERE state = 'Utah'
pizzeriaDB
WITH stores_filtered AS (
SELECT DISTINCT case when stores.state = 'Utah' then true else false end as isUtah,
id
FROM stores
WHERE isUtah = true
), products_filtered AS (
SELECT SKU,
name
FROM products
WHERE name like '%Hawaiian%'
)
SELECT orders.orderDate::date as order_date,
count(distinct orders.id) as num_orders
FROM products_filtered
LEFT JOIN order_items
ON products_filtered.SKU = order_items.SKU
LEFT JOIN orders
ON order_items.orderId = orders.id
LEFT JOIN stores_filtered
ON orders.storeId = stores_filtered.id
GROUP BY 1
pizzeriaDB
WITH customer_variations AS (
SELECT
orders.customerId,
COUNT(DISTINCT id) AS num_orders,
COUNT(DISTINCT SKU) AS num_unique_skus
FROM orders
JOIN order_items
ON orders.id = order_items.orderID
GROUP BY 1
)
SELECT
MAX(num_orders) AS max_orders,
MIN(num_orders) AS min_orders,
AVG(num_orders) AS avg_orders,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY num_orders) AS median_orders,
MAX(num_unique_skus) AS max_unique_skus,
MIN(num_unique_skus) AS min_unique_skus,
AVG(num_unique_skus) AS avg_unique_skus,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY num_unique_skus) AS median_unique_skus
FROM customer_variations;
Purpose-built for displays of data
Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
