Business context
Vanguard's digital commerce analytics team wants a recent sales snapshot for products and customer retention signals.
Task
As of 2025-02-01, write one PostgreSQL query that returns the top three products by sales during the inclusive 30-day period ending on that date, and identifies customers who made another purchase within seven days of an earlier purchase.
Requirements
- Join purchases to products and calculate product sales as
quantity * unit_price.
- Filter product sales to the inclusive 30-day period and rank products by total sales, returning only the top three.
- Use purchase dates to identify customers with a later purchase no more than seven days after an earlier purchase.
- Return both result types in one result set, with
result_type set to TOP_PRODUCT or REPEAT_CUSTOMER.
