Write a reusable prompt with replaceable inputs. Submit your prompt and one test output for this example.
Task: Create a reusable prompt for generating a read-only analytical SQL query from a schema and business definition.
Example input: Use SQLite syntax. Tables: customers(id, name), orders(id, customer_id, status), order_items(order_id, quantity, unit_price_cents). Only orders with status "paid" count. Customers: (1, Ada), (2, Bo), (3, Cy). Orders: (10,1,paid), (11,1,paid), (12,2,cancelled). Items: (10,2,300), (10,1,400), (11,1,500), (12,1,900). Return every customer with paid-order count and paid-item revenue in cents, including zeros. Customer names need not be unique.
Constraints:
- Use a SELECT query only; do not modify any table.
- Count each paid order once regardless of item count.
- State how paid orders without items are handled.
Required output from your prompt: The SQL query, an explanation of its join strategy, and the expected result for the supplied fixture.
Success checks:
- Ada has two paid orders and 1,500 cents of revenue.
- Bo and Cy both have zero paid orders and revenue.
- The grouping key is customer ID.
- The query does not overcount order 10 because it has two item rows.