Data Scientist, Author, DataVis, Educator, Speaker, Public Service @ClintonBrownley
https://cbrownley.github.io
Public
Edited
Jun 27, 2023
10
pizzeriaDB
SELECT
products.name AS "Name",
products.size AS "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
WITH order_by_product_day AS (
SELECT
products.name,
DATE_TRUNC('DAY', orders.orderDate::date) AS order_day,
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
)
SELECT name
, min(num_orders) as min_num_orders
, max(num_orders) as max_num_orders
, avg(num_orders) as avg_num_orders
, median(num_orders) as median_num_orders
, mode(num_orders) as mode_num_orders
FROM order_by_product_day
GROUP BY 1
ORDER BY 1
pizzeriaDB
SELECT *
FROM products
WHERE Ingredients NOT LIKE '%Tomato%'
AND Size = 'Small'
pizzeriaDB
SELECT
products.name AS "Name",
products.size AS "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 COUNT(DISTINCT orders.id) > 100000
ORDER BY 3 DESC
pizzeriaDB
SELECT
DATE_TRUNC('MONTH', orderDate::date) AS order_month
, SUM(total) AS total_revenue
FROM orders
GROUP BY 1
ORDER BY 1
pizzeriaDB
WITH pepperoni_pizza_orders AS (
SELECT
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,
orders.id
FROM orders
JOIN order_items ON orders.id = order_items.orderID
JOIN products on order_items.SKU = products.SKU
WHERE products.Name LIKE '%Pepperoni%'
)
SELECT
day_of_week
, COUNT(DISTINCT id) AS num_orders
FROM pepperoni_pizza_orders
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1
pizzeriaDB
WITH dailyOrders AS (
SELECT date_trunc('day', orders.orderDate::date) as order_date
, count(distinct orders.id) as num_orders
FROM products
JOIN order_items
ON products.SKU = order_items.SKU
JOIN orders
ON order_items.orderId = orders.id
GROUP BY 1
ORDER BY 1 ASC
)
SELECT *
, avg(num_orders) over (
order by order_date asc
range between interval 14 days preceding
and interval 14 days following
) as moving_avg
FROM dailyOrders
pizzeriaDB
WITH product_stats AS (SELECT products.name
, products.size
, products.category
, sum(orders.total) AS total_revenue
, count(distinct orders.id) as num_orders
, count(distinct orders.customerId) as num_customers
FROM products
JOIN order_items
ON products.SKU = order_items.SKU
JOIN orders
ON order_items.orderId = orders.id
GROUP BY
name
, size
, category),
launch_rank AS (
SELECT row_number() over (partition by Category order by Launch) as "Row Number"
, *
FROM products
)
SELECT product_stats.*
FROM product_stats
JOIN launch_rank ON product_stats.name = launch_rank.name
AND product_stats.size = launch_rank.size
AND product_stats.category = launch_rank.category
WHERE launch_rank."Row Number" = 1
pizzeriaDB
WITH utahStores AS (
SELECT *
FROM stores
WHERE state = 'Utah'
),
hawaiianPizzas AS (
SELECT *
FROM products
WHERE name like '%Hawaiian%'
)
SELECT orders.orderDate::date as order_date
, count(distinct orders.id) as num_orders
FROM hawaiianPizzas
JOIN order_items
ON hawaiianPizzas.SKU = order_items.SKU
JOIN orders
ON order_items.orderId = orders.id
JOIN utahStores
ON orders.storeId = utahStores.id
GROUP BY 1
pizzeriaDB
Type SQL, then Shift-Enter. Ctrl-space for more options.
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.
