You are a data analyst expert in SQL. Analyze this PostgreSQL database: Table:…
- User
You are a data analyst expert in SQL. Analyze this PostgreSQL database:
Table: retail_ingest_data Available Columns with Types: transaction_id (text) date (text) customer_id (text) gender (text) age (text) product_category (text) quantity (numeric) price_per_unit (text) total_amount (numeric)
Sample Data:
transaction_id date customer_id gender age product_category quantity price_per_unit total_amount 1 2023-11-24 CUST001 Male 34 Beauty 3 50 150 2 2023-02-27 CUST002 Female 26 Clothing 2 500 1000 3 2023-01-13 CUST003 Male 50 Electronics 1 30 30 CRITICAL RULES:
- ONLY use columns from the list above
- If the available columns are insufficient to answer the question, clearly state that
- Keep answers concise and focused
- For numeric calculations, CAST string columns to NUMERIC using CAST(column_name AS NUMERIC)
- Table name is exactly 'retail_ingest_data'
VISUALIZATION RULES:
- Only suggest bar, line, or pie charts
- ONLY use column names that exist in the data
- Bar charts: For comparing categories or groups
- Line charts: For time-based trends
- Pie charts: For showing composition/distribution
Example Responses:
-
Time-based Question: "Show monthly sales trend" { "sql_query": "SELECT DATE_TRUNC('month', date::timestamp) as month, SUM(CAST(total_amount AS NUMERIC)) as total_sales FROM retail_ingest_data GROUP BY month ORDER BY month", "answer": "Monthly sales show an upward trend with peak in December at $50,000", "visualization": { "type": "line", "x_axis": "month", "y_axis": "total_sales", "title": "Monthly Sales Trend", "description": "Line chart shows sales progression over time" } }
-
Category Question: "What's the distribution of sales by product?" { "sql_query": "SELECT product_name, SUM(CAST(total_amount AS NUMERIC)) as total_sales FROM retail_ingest_data GROUP BY product_name ORDER BY total_sales DESC", "answer": "Product sales distribution shows Product A leading with 45% market share", "visualization": { "type": "pie", "x_axis": "product_name", "y_axis": "total_sales", "title": "Sales Distribution by Product", "description": "Pie chart shows relative market share of each product" } }
Question: What items should i keep in stock .. So there will be no shortage
Remember: Only use columns that exist in the data and match column names exactly as shown above.
Respond in this JSON format: { "sql_query": "Your PostgreSQL query here (or 'None' if columns are insufficient)", "answer": "Provide clear business insights from the query results", "visualization": { "type": "none | bar | line | pie", "x_axis": "column_name", "y_axis": "column_name", "title": "Chart title", "description": "Why this visualization is relevant" } }
Query Results:
product_category total_quantity_sold 0 Clothing 894 1 Electronics 849 2 Beauty 771 - Assistant
{ "sql_query": "SELECT product_category, SUM(CAST(quantity AS NUMERIC)) as total_quantity_sold FROM retail_ingest_data GROUP BY product_category ORDER BY total_quantity_sold DESC", "answer": "Clothing, Electronics, and Beauty products are in high demand based on total quantities sold, suggesting these items should be prioritized for restocking to prevent shortages.", "visualization": { "type": "bar", "x_axis": "product_category", "y_axis": "total_quantity_sold", "title": "Total Quantity Sold by Product Category", "description": "Bar chart shows which product categories have the highest sales volume, indicating restocking priorities." } }