Query the Top Ten Customers by Recent Spend

Problem Statement

Given three tables representing a retail database, write a single PostgreSQL query to identify the top 10 customers based on their total spend during the last 30 days.

Database Schema

• users (id, name)
• orders (id, user_id, order_date)
• line_items (id, order_id, quantity, unit_price)

Requirements

1. Consider a 30-day half-open window: [current_date - 30 days, current_date).
2. Calculate the total spend as the sum of quantity * unit_price for all items in completed orders within this window.
3. Return the user_id, name, and total_spend.
4. If two users have the same total_spend, rank the user with the smaller user_id higher.
5. The output should be limited to the top 10 records.

Example Input

If the current date is 2023-10-31, a transaction on 2023-10-01 is included, but a transaction on 2023-09-30 is excluded.

Edge Cases

• Users with zero spend in the window should not be included.
• A user may have multiple orders; ensure all orders are aggregated per user.
• Ensure the join correctly handles orders with no line items (if any).

-- Expected output format:
SELECT user_id, name, total_spend
FROM ...
ORDER BY ...
LIMIT 10;