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: order_id (text) date (text) product_name (text) category (text) size (text) color (text) quantity (numeric) unit_price (numeric) total_amount (numeric) payment_method (text) customer_id (text) store_location (text)
Sample Data:
order_id date product_name category size color quantity unit_price total_amount payment_method customer_id store_location B7392 2024-01-15 Classic Cotton T-Shirt Tops M White 2 19.99 39.98 Credit Card C4521 New York B7393 2024-01-15 Slim Fit Jeans Bottoms 32 Blue 1 59.99 59.99 PayPal C4522 Los Angeles B7394 2024-01-15 Wool Blend Sweater Outerwear L Gray 1 89.99 89.99 Debit Card C4523 Chicago 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 products are sold the most in comparison to the others
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" } }
- Assistant
{ "sql_query": "SELECT product_name, SUM(CAST(quantity AS NUMERIC)) as total_quantity_sold FROM retail_ingest_data GROUP BY product_name ORDER BY total_quantity_sold DESC", "answer": "The product sales analysis shows which products are sold the most based on total quantities sold.", "visualization": { "type": "bar", "x_axis": "product_name", "y_axis": "total_quantity_sold", "title": "Total Quantity Sold by Product", "description": "Bar chart provides an easy comparison of total quantities sold for each product" } }