Prompt Editor

Refine, edit, and optimize your generated prompt.

Ready for your AI model

System Role
You are an expert SQL database analyst specializing in complex analytical queries.

Objective
Generate a single, comprehensive SQL query that calculates LifeTime Value (LTV) for users who have made a purchase within the last 30 days, joining specified tables.

Instructions
1.  **Schema Assumption:** Assume the following table structures and relationships:
    *   `Users`: `user_id` (Primary Key), `registration_date`
    *   `Orders`: `order_id` (Primary Key), `user_id` (Foreign Key to `Users`), `order_date`, `product_id` (Foreign Key to `Products`), `quantity`, `item_price` (price at the time of purchase for that item in the order).
    *   `Products`: `product_id` (Primary Key), `product_name`, `current_price`
2.  **Table Joins:** The query must join the `Users`, `Orders`, and `Products` tables.
3.  **LTV Calculation:** Calculate the LifeTime Value (LTV) for each user. LTV should be defined as the sum of `(Orders.quantity * Orders.item_price)` for all orders associated with that user.
4.  **Filtering:** Filter the results to include only users who have placed at least one order within the last 30 days, relative to the current date.
5.  **Output Columns:** The final result set must include `user_id`, the calculated `LTV` for that user, and the `last_purchase_date` for each qualified user.
6.  **SQL Dialect:** Use standard SQL syntax compatible with most relational databases (e.g., PostgreSQL, MySQL).

Constraints
*   The output must be a single `SELECT` statement.
*   Do not include any DDL (Data Definition Language) or DML (Data Manipulation Language) statements other than the final `SELECT` query.
*   Assume the `CURRENT_DATE` function or an equivalent date/time function (e.g., `NOW()`, `GETDATE()`) is available for date calculations.

Output Format
The complete, executable SQL query, enclosed within a markdown code block.

Generated Outputtext

Model completion generated for this prompt

SELECT u.user_id, SUM(o.quantity * o.item_price) AS LTV, MAX(o.order_date) AS last_purchase_date FROM Users u INNER JOIN Orders o ON u.user_id = o.user_id INNER JOIN Products p ON o.product_id = p.product_id GROUP BY u.user_id HAVING MAX(o.order_date) >= CURRENT_DATE - INTERVAL '30' DAY;

Metadata

Target Engine

ChatGPT

Prompt Editor

Refine, edit, and optimize your generated prompt.

Ready for your AI model

System Role
You are an expert SQL database analyst specializing in complex analytical queries.

Objective
Generate a single, comprehensive SQL query that calculates LifeTime Value (LTV) for users who have made a purchase within the last 30 days, joining specified tables.

Instructions
1.  **Schema Assumption:** Assume the following table structures and relationships:
    *   `Users`: `user_id` (Primary Key), `registration_date`
    *   `Orders`: `order_id` (Primary Key), `user_id` (Foreign Key to `Users`), `order_date`, `product_id` (Foreign Key to `Products`), `quantity`, `item_price` (price at the time of purchase for that item in the order).
    *   `Products`: `product_id` (Primary Key), `product_name`, `current_price`
2.  **Table Joins:** The query must join the `Users`, `Orders`, and `Products` tables.
3.  **LTV Calculation:** Calculate the LifeTime Value (LTV) for each user. LTV should be defined as the sum of `(Orders.quantity * Orders.item_price)` for all orders associated with that user.
4.  **Filtering:** Filter the results to include only users who have placed at least one order within the last 30 days, relative to the current date.
5.  **Output Columns:** The final result set must include `user_id`, the calculated `LTV` for that user, and the `last_purchase_date` for each qualified user.
6.  **SQL Dialect:** Use standard SQL syntax compatible with most relational databases (e.g., PostgreSQL, MySQL).

Constraints
*   The output must be a single `SELECT` statement.
*   Do not include any DDL (Data Definition Language) or DML (Data Manipulation Language) statements other than the final `SELECT` query.
*   Assume the `CURRENT_DATE` function or an equivalent date/time function (e.g., `NOW()`, `GETDATE()`) is available for date calculations.

Output Format
The complete, executable SQL query, enclosed within a markdown code block.

Generated Outputtext

Model completion generated for this prompt

Metadata

Target Engine

ChatGPT

CatalystPrompt Studio

Prompt Editor

Metadata

CatalystPrompt Studio

Prompt Editor

Metadata