sql
sql
sql
sql
sql
sql
sql
sql
sql
sql
sql
Author
Comments (6)
Paul Buffa
Jun 27, 2023To note, by using date_part rather than date_trunc, you'll aggregate months across years. The prompt was a bit ambiguous, so this is totally correct!
Also, there is a function called monthname() that will extract the name for you, so you can save a little bit of code!
Paul Buffa
Jun 27, 2023Nice! There's also a common shorthand for casting as a date, which is
orders.orderDate::date
Paul Buffa
Jun 27, 2023This is great! The prompt asks you to use the product launch date, but I think this works even better, as many products were launched on the same date.
Paul Buffa
Jun 27, 2023Nice! Only thing is that you don't need the DISTINCT in your select statement, but otherwise, looks good!
Paul Buffa
Jun 27, 2023Great job on all the prompts and going for the bonus points! You've completed the assignment, and we'll send you your certificate soon. (We're first going through all the assignments, so might be in a few days.)
If you want to check out my solution for the bonus points, see the comment below. It's a little different, as it looks at the combinations of products in an order.
Jun 27, 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.