10
pizzeriaDB
SELECT DISTINCT products.name, products.size, Count(orders.id) as daOrders
FROM products
join Order_items Using(SKU)
join Orders ON Orders.id=order_items.orderID
group by products.name, products.size
ORDER by daOrders DESC
pizzeriaDB
SELECT orders_by_product_day.name, Min(orders_by_product_day.orders),Max(orders_by_product_day.orders),Median(orders_by_product_day.orders),Mode(orders_by_product_day.orders)
FROM orders_by_product_day
GROUP BY orders_by_product_day.name
pizzeriaDB
select Name, Size, Ingredients from products where (Ingredients NOT LIKE '%Tomato%') AND (Size = 'Small')
pizzeriaDB
SELECT products.name, products.size, COUNT(order_items.orderID) AS OrdCount
FROM products
JOIN order_items USING(SKU)
GROUP BY products.name, products.size
HAVING COUNT(order_items.orderID) > 100000
ORDER BY OrdCount DESC;
pizzeriaDB
SELECT
date_trunc('month', orders.orderDate::date) as "Month Name",
SUM(orders.total) AS total
FROM orders
GROUP BY 1
ORDER BY total DESC;
pizzeriaDB
WITH peppizza AS (
SELECT name,
orders,
day_of_week
FROM orders_by_product_day
WHERE name like '%Pepperoni%'
)
SELECT
SUM(orders) as sumoforders,
day_of_week
FROM peppizza
GROUP BY 2
ORDER BY sumoforders DESC
LIMIT 1
pizzeriaDB
SELECT
orderDate,
ROUND(moving_average, 0) AS rounded_moving_average
FROM (
SELECT
CAST(orderDate AS DATE) AS orderDate,
AVG(orders) OVER (ORDER BY CAST(orderDate AS DATE) ROWS BETWEEN 27 PRECEDING AND CURRENT ROW) AS moving_average
FROM (
SELECT
CAST(orderDate AS DATE) AS orderDate,
COUNT(id) AS orders
FROM orders
GROUP BY CAST(orderDate AS DATE)
) AS subquery
) AS moving_average_subquery
ORDER BY orderDate;
pizzeriaDB
SELECT
orderDate,
ROUND(moving_average, 0) AS rounded_moving_average
FROM (
SELECT
orderDate::DATE AS orderDate,
AVG(orders) OVER (ORDER BY CAST(orderDate AS DATE) ROWS BETWEEN 27 PRECEDING AND CURRENT ROW) AS moving_average
FROM (
SELECT
orderDate::date AS orderDate,
COUNT(id) AS orders
FROM orders
GROUP BY CAST(orderDate AS DATE)
) AS subquery
) AS moving_average_subquery
ORDER BY orderDate;
pizzeriaDB
SELECT
p.Name AS ProductName,
p.Size AS ProductSize,
p.Launch as ProductLaunch,
COUNT(DISTINCT o.id) AS OrderCount,
SUM(p.Price) AS TotalRevenue,
COUNT(DISTINCT o.customerId) AS CustomerCount
FROM
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY p.Category ORDER BY p.Launch) AS rn
FROM
products as p
) AS p
JOIN
order_items oi ON p.SKU = oi.SKU
JOIN
orders o ON oi.orderId = o.id
WHERE
p.rn = 1
GROUP BY
p.Name, p.Size, p.Launch
ORDER BY
p.Name;
pizzeriaDB
SELECT DISTINCT case when stores.state = 'Utah' then true else false end as isUtah
, orders.orderDate::date as order_date
, count(distinct orders.id) as num_orders
FROM products
LEFT JOIN order_items
ON products.SKU = order_items.SKU
LEFT JOIN orders
ON order_items.orderId = orders.id
LEFT JOIN stores
ON orders.storeId = stores.id
WHERE products.name like '%Hawaiian%'
GROUP BY 1, 2
HAVING isUtah = true;
pizzeriaDB
SELECT
CASE WHEN stores.state = 'Utah' THEN true ELSE false END AS isUtah,
orders.orderDate::date AS order_date,
COUNT(DISTINCT orders.id) AS num_orders
FROM
stores
LEFT JOIN orders
ON stores.id = orders.storeId
LEFT JOIN order_items
ON orders.id = order_items.orderId
LEFT JOIN products
ON order_items.SKU = products.SKU
WHERE
products.name LIKE '%Hawaiian%'
AND stores.state = 'Utah'
GROUP BY 1, 2;
pizzeriaDB
SELECT
customerid,
MIN(variation) AS min_variation,
MAX(variation) AS max_variation,
MEDIAN(variation) as median_variation,
AVG(variation) AS avg_variation
FROM (
SELECT
customerid,
COUNT(DISTINCT id) AS variation
FROM orders
GROUP BY customerid
) AS subquery
GROUP BY customerid
pizzeriaDB
SELECT
customerid,
COUNT(DISTINCT id) AS variation
FROM orders
GROUP BY customerid
HAVING COUNT(DISTINCT id) > (
SELECT AVG(variation)
FROM (
SELECT COUNT(DISTINCT id) AS variation
FROM orders
GROUP BY customerid
) AS subquery
)
pizzeriaDB
WITH customerOrders AS (
SELECT customerId
, orders.id
, group_concat(order_items.SKU, ',') as full_order
, count(distinct orders.id) as num_orders
FROM orders
JOIN order_items
ON orders.id = order_items.orderId
GROUP BY 1, 2
), customerOrderCounts AS (
SELECT customerId
, count(distinct id) as num
FROM orders
GROUP BY 1
HAVING num > 1
), orderFrequency AS (
SELECT customerId
, full_order
, count(distinct id) as num
FROM customerOrders
WHERE customerId in (SELECT DISTINCT customerId FROM customerOrderCounts)
GROUP BY 1, 2
)
SELECT customerOrderCounts.num as "Number of Orders"
, count(distinct customerOrderCounts.customerId) as "Number of Customers"
, median(orderFrequency.num) as "Median Number of Distinct Orders"
, avg(orderFrequency.num) as "Avg Number of Distinct Orders"
, max(orderFrequency.num) as "Max Number of Distinct Orders"
, min(orderFrequency.num) as "Min Number of Distinct Orders"
FROM orderFrequency
JOIN customerOrderCounts
ON orderFrequency.customerId = customerOrderCounts.customerId
GROUP BY 1
ORDER BY 1
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.
