sql
sql
sql
sql
sql
sql
sql
sql
sql
sql
sql
Author
Comments (7)
Paul Buffa
Jun 28, 2023That's right!
Paul Buffa
Jun 28, 2023You can also cast using the "::" shorthand, like this:
date_trunc('month', orderDate::date) as month
Paul Buffa
Jun 28, 2023That is Saturday, though the week begins with Sunday, but it also starts with 0 :)
Paul Buffa
Jun 28, 2023Very close! The prompt asks to check by category, so you would just need to change the PARTITION BY in your ROW_NUMBER() function to use products.Category, rather than the name and size.
Paul Buffa
Jun 28, 2023This is great! Just to note, you could remove the LEFT JOINs and just make them JOINs
Paul Buffa
Jun 28, 2023Great job with all the prompts and for going for the bonus points. If you're curious to see my solution, I've pasted it in the comment below.
You've completed the SQL course assignment. Nice work! We'll send over the certificate soon. We're just first going through and grading all the assignments.
Jun 28, 2023
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
© 2026 Observable, Inc.