Checkr's product analytics team wants to identify the products generating the most sales. Write a PostgreSQL query that returns the top 10 products by completed sales during the previous calendar quarter. For the sample data, assume the reporting date is 2025-01-15, so the previous quarter is October 1 through December 31, 2024.
Requirements
- Join
orders, order_items, and products.
- Include only orders with
status = 'completed' from the previous calendar quarter. Use an inclusive start date and exclusive end date.
- Calculate sales as
quantity * unit_price, aggregated by product.
- Return the product name and total sales, ordered from highest to lowest, with a deterministic product-name tie-breaker. Return only the top 10 products.
Representative Data
The data includes completed, cancelled, out-of-quarter, and null-status orders. Product Background Check API has no sales in the previous quarter, and product Identity Verification ranks eleventh.
