sql
sql
sql
sql
sql
sql
sql
sql
sql
sql
Author
Comments (5)
Paul Buffa
Jun 27, 2023Note, by grouping by date_part, you'll aggregating across years. If you use date_trunc, you'll retain the year information with the month. The prompt was a bit ambiguous, so this definitely counts!
Paul Buffa
Jun 27, 2023Note, while this is totally correct, a more common definition for a rolling 28 days would put the anchor date in the middle, making it 14 days preceding and 14 days following. This is still correct though, just a slightly less common definition.
Paul Buffa
Jun 27, 2023Great job with the prompts and for going after the bonus points! You are very close to a perfect solution. The only difference is that you're still grouping the results by customer. Take a look at the solution I have in the next comment to spot the difference.
You've successfully completed the assignment. We'll send you a certificate soon.
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
TO
Jun 30, 2023Thank you for a great course and for the detailed feedback on the assignment, Paul!
© 2026 Observable, Inc.