Public
Edited
Jun 24, 2023
10
pizzeriaDB
SELECT products.name, products.size, COUNT(DISTINCT orders.id) AS order_count
FROM orders
JOIN order_items ON orders.id = order_items.orderID
JOIN products ON order_items.SKU = products.SKU
GROUP BY products.name, products.size
ORDER BY order_count DESC;
pizzeriaDB
SELECT
name,
AVG(orders) AS average,
MIN(orders) AS minimum,
MAX(orders) AS maximum,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY orders) AS median,
MODE() WITHIN GROUP (ORDER BY orders) AS mode
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
FROM products
JOIN order_items ON products.SKU = order_items.SKU
JOIN orders ON order_items.orderID = orders.id
GROUP BY products.name, products.size
HAVING COUNT(DISTINCT orders.id) > 100000;
pizzeriaDB
SELECT DATE_TRUNC('month', CAST(orderDate AS DATE)) AS month, SUM(total) AS total_revenue
FROM orders
GROUP BY month
ORDER BY month;
pizzeriaDB
SELECT
DATE_PART('dow', CAST(orderDate AS DATE)) AS day_of_week,
COUNT(DISTINCT orders.id) AS order_count
FROM
orders
JOIN
order_items ON orders.id = order_items.orderID
JOIN
products ON order_items.SKU = products.SKU
WHERE
products.Name LIKE '%Pepperoni%'
GROUP BY
day_of_week
ORDER BY
order_count DESC
LIMIT 1;
pizzeriaDB
SELECT
CAST(orderDate AS DATE) AS order_date,
AVG(COUNT(id)) OVER (ORDER BY CAST(orderDate AS DATE) ROWS BETWEEN 27 PRECEDING AND CURRENT ROW) AS moving_avg_orders
FROM
orders
GROUP BY
CAST(orderDate AS DATE)
ORDER BY
CAST(orderDate AS DATE);
pizzeriaDB
WITH first_product_cte AS (
SELECT
products.Name,
products.Size,
CAST(products.Launch AS DATE) AS launch_date,
orders.id AS order_id,
orders.customerId,
orders.total,
ROW_NUMBER() OVER (PARTITION BY products.Name, products.Size ORDER BY CAST(products.Launch AS DATE)) AS rn
FROM
orders
JOIN order_items ON orders.id = order_items.orderID
JOIN products ON order_items.SKU = products.SKU
)
SELECT
Name,
Size,
launch_date,
COUNT(DISTINCT order_id) AS order_count,
SUM(total) AS total_revenue,
COUNT(DISTINCT customerId) AS customer_count
FROM
first_product_cte
WHERE
rn = 1
GROUP BY
Name,
Size,
launch_date;
pizzeriaDB
WITH customer_variations AS (
SELECT
customerID,
orders.total - LAG(orders.total) OVER (PARTITION BY customerID ORDER BY CAST(orders.orderDate AS DATE)) AS order_variation
FROM
orders
)
SELECT
customerID,
MEDIAN(order_variation) AS median_variation,
MAX(order_variation) AS max_variation,
MIN(order_variation) AS min_variation,
AVG(order_variation) AS avg_variation
FROM
customer_variations
GROUP BY
customerID;
pizzeriaDB
SELECT
orders.customerID,
COUNT(DISTINCT products.size) / COUNT(DISTINCT orders.id) AS average_size_change
FROM
orders
JOIN
order_items ON orders.id = order_items.orderId
JOIN
products ON order_items.SKU = products.SKU
GROUP BY
orders.customerID;
pizzeriaDB
SELECT * FROM order_items LIMIT 5
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.
