Skip to content

Instantly share code, notes, and snippets.

@lopinho

lopinho/contoso_dw.json

Last active September 12, 2025 19:21
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save lopinho/a07b6aeef52ff01c2b20d4fe0ab1dc91 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save lopinho/a07b6aeef52ff01c2b20d4fe0ab1dc91 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
contoso_dw.json
{
"success": true,
"message": "NLP2SQL conversion completed successfully",
"thread_id": "dbfb5b1a-d425-4015-81bc-3e27035c999f",
"result": {
"success": true,
"user_query": "can you sumarize the data by available products",
"workflow_completed_at": "2025-09-12T17:52:46.548640+00:00",
"relevant_metadata": {
"success": true,
"query": "can you sumarize the data by available products",
"keywords": [
"you",
"sumarize",
"data",
"available",
"products"
],
"relevant_tables": [
{
"name": "dimproductsubcategory",
"type": "BASE TABLE",
"comment": null,
"row_count": [
{
"row_count": 44
}
],
"relevance_score": 17.0,
"columns": [
{
"column_name": "productsubcategorykey",
"data_type": "integer",
"is_nullable": "NO",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": 32,
"numeric_scale": 0,
"column_comment": null,
"is_primary_key": true,
"relevance_score": 15.0
},
{
"column_name": "productsubcategoryname",
"data_type": "text",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": null,
"numeric_scale": null,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 10.0
},
{
"column_name": "productcategorykey",
"data_type": "integer",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": 32,
"numeric_scale": 0,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 3.0
}
],
"foreign_keys": [
{
"column_name": "productcategorykey",
"referenced_table": "dimproductcategory",
"referenced_column": "productcategorykey"
}
]
},
{
"name": "dimproduct",
"type": "BASE TABLE",
"comment": null,
"row_count": [
{
"row_count": 2233
}
],
"relevance_score": 4.0,
"columns": [
{
"column_name": "productsubcategorykey",
"data_type": "integer",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": 32,
"numeric_scale": 0,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 13.0
},
{
"column_name": "availableforsaledate",
"data_type": "date",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": null,
"numeric_scale": null,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 10.0
},
{
"column_name": "productkey",
"data_type": "integer",
"is_nullable": "NO",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": 32,
"numeric_scale": 0,
"column_comment": null,
"is_primary_key": true,
"relevance_score": 5.0
},
{
"column_name": "classid",
"data_type": "integer",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": 32,
"numeric_scale": 0,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 3.0
},
{
"column_name": "styleid",
"data_type": "integer",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": 32,
"numeric_scale": 0,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 3.0
},
{
"column_name": "colorid",
"data_type": "integer",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": 32,
"numeric_scale": 0,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 3.0
},
{
"column_name": "unitofmeasureid",
"data_type": "integer",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": 32,
"numeric_scale": 0,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 3.0
},
{
"column_name": "stocktypeid",
"data_type": "integer",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": 32,
"numeric_scale": 0,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 3.0
},
{
"column_name": "productname",
"data_type": "text",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": null,
"numeric_scale": null,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 0.0
},
{
"column_name": "productdescription",
"data_type": "text",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": null,
"numeric_scale": null,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 0.0
},
{
"column_name": "manufacturer",
"data_type": "text",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": null,
"numeric_scale": null,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 0.0
},
{
"column_name": "brandname",
"data_type": "text",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": null,
"numeric_scale": null,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 0.0
},
{
"column_name": "classname",
"data_type": "text",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": null,
"numeric_scale": null,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 0.0
},
{
"column_name": "stylename",
"data_type": "text",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": null,
"numeric_scale": null,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 0.0
},
{
"column_name": "colorname",
"data_type": "text",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": null,
"numeric_scale": null,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 0.0
},
{
"column_name": "weight",
"data_type": "numeric",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": 15,
"numeric_scale": 2,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 0.0
},
{
"column_name": "weightunitmeasureid",
"data_type": "text",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": null,
"numeric_scale": null,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 0.0
},
{
"column_name": "unitofmeasurename",
"data_type": "text",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": null,
"numeric_scale": null,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 0.0
},
{
"column_name": "stocktypename",
"data_type": "text",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": null,
"numeric_scale": null,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 0.0
},
{
"column_name": "unitcost",
"data_type": "numeric",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": 15,
"numeric_scale": 2,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 0.0
},
{
"column_name": "unitprice",
"data_type": "numeric",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": 15,
"numeric_scale": 2,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 0.0
},
{
"column_name": "status",
"data_type": "text",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": null,
"numeric_scale": null,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 0.0
}
],
"foreign_keys": [
{
"column_name": "productsubcategorykey",
"referenced_table": "dimproductsubcategory",
"referenced_column": "productsubcategorykey"
}
]
},
{
"name": "dimaccount",
"type": "BASE TABLE",
"comment": null,
"row_count": [
{
"row_count": 24
}
],
"relevance_score": 0.0,
"columns": [
{
"column_name": "accountkey",
"data_type": "integer",
"is_nullable": "NO",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": 32,
"numeric_scale": 0,
"column_comment": null,
"is_primary_key": true,
"relevance_score": 5.0
},
{
"column_name": "parentaccountkey",
"data_type": "numeric",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": 15,
"numeric_scale": 2,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 0.0
},
{
"column_name": "accountname",
"data_type": "text",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": null,
"numeric_scale": null,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 0.0
},
{
"column_name": "accountdescription",
"data_type": "text",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": null,
"numeric_scale": null,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 0.0
},
{
"column_name": "accounttype",
"data_type": "text",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": null,
"numeric_scale": null,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 0.0
},
{
"column_name": "valuetype",
"data_type": "text",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": null,
"numeric_scale": null,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 0.0
}
],
"foreign_keys": []
},
{
"name": "dimchannel",
"type": "BASE TABLE",
"comment": null,
"row_count": [
{
"row_count": 4
}
],
"relevance_score": 0.0,
"columns": [
{
"column_name": "channelkey",
"data_type": "integer",
"is_nullable": "NO",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": 32,
"numeric_scale": 0,
"column_comment": null,
"is_primary_key": true,
"relevance_score": 5.0
},
{
"column_name": "channelname",
"data_type": "text",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": null,
"numeric_scale": null,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 0.0
},
{
"column_name": "channeldescription",
"data_type": "text",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": null,
"numeric_scale": null,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 0.0
}
],
"foreign_keys": []
},
{
"name": "dimcurrency",
"type": "BASE TABLE",
"comment": null,
"row_count": [
{
"row_count": 28
}
],
"relevance_score": 0.0,
"columns": [
{
"column_name": "currencykey",
"data_type": "integer",
"is_nullable": "NO",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": 32,
"numeric_scale": 0,
"column_comment": null,
"is_primary_key": true,
"relevance_score": 5.0
},
{
"column_name": "currencyname",
"data_type": "text",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": null,
"numeric_scale": null,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 0.0
},
{
"column_name": "currencydescription",
"data_type": "text",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": null,
"numeric_scale": null,
"column_comment": null,
"is_primary_key": false,
"relevance_score": 0.0
}
],
"foreign_keys": []
}
],
"total_tables_found": 23,
"database": null,
"schema": "public"
},
"sql_generation": {
"generated_sql": "SELECT dps.productsubcategoryname, COUNT(dp.productkey) AS product_count, AVG(dp.unitprice) AS average_price, MIN(dp.unitprice) AS min_price, MAX(dp.unitprice) AS max_price FROM dimproduct dp JOIN dimproductsubcategory dps ON dp.productsubcategorykey = dps.productsubcategorykey WHERE dp.availableforsaledate <= CURRENT_DATE GROUP BY dps.productsubcategoryname ORDER BY product_count DESC LIMIT 100;",
"validation": {
"valid": true,
"errors": [],
"warnings": []
},
"need_more_data": false,
"success": true
},
"sql_execution": {
"success": true,
"rows": [
{
"productsubcategoryname": "Lamps",
"product_count": 158,
"average_price": "227.8358227848101266",
"min_price": "29.99",
"max_price": "635.99"
},
{
"productsubcategoryname": "Projectors & Screens",
"product_count": 103,
"average_price": "750.9417475728155340",
"min_price": "109.00",
"max_price": "2499.00"
},
{
"productsubcategoryname": "Camcorders",
"product_count": 103,
"average_price": "752.5242718446601942",
"min_price": "158.00",
"max_price": "1620.00"
},
{
"productsubcategoryname": "Microwaves",
"product_count": 102,
"average_price": "230.1483333333333333",
"min_price": "94.99",
"max_price": "665.94"
},
{
"productsubcategoryname": "Printers, Scanners & Fax",
"product_count": 101,
"average_price": "158.7920792079207921",
"min_price": "78.00",
"max_price": "248.00"
},
{
"productsubcategoryname": "Smart phones & PDAs",
"product_count": 101,
"average_price": "280.2079207920792079",
"min_price": "129.00",
"max_price": "402.00"
},
{
"productsubcategoryname": "Digital Cameras",
"product_count": 100,
"average_price": "204.1260000000000000",
"min_price": "129.00",
"max_price": "290.00"
},
{
"productsubcategoryname": "Digital SLR Cameras",
"product_count": 100,
"average_price": "446.6960000000000000",
"min_price": "302.00",
"max_price": "673.00"
},
{
"productsubcategoryname": "Home Theater System",
"product_count": 100,
"average_price": "506.1340000000000000",
"min_price": "199.00",
"max_price": "1109.00"
},
{
"productsubcategoryname": "Computers Accessories",
"product_count": 99,
"average_price": "21.2494949494949495",
"min_price": "9.50",
"max_price": "52.90"
},
{
"productsubcategoryname": "Home & Office Phones",
"product_count": 92,
"average_price": "30.0352173913043478",
"min_price": "9.99",
"max_price": "49.99"
},
{
"productsubcategoryname": "Movie DVD",
"product_count": 90,
"average_price": "108.1460000000000000",
"min_price": "9.99",
"max_price": "289.99"
},
{
"productsubcategoryname": "Refrigerators",
"product_count": 86,
"average_price": "879.2958139534883721",
"min_price": "129.99",
"max_price": "3199.99"
},
{
"productsubcategoryname": "Fans",
"product_count": 78,
"average_price": "83.7361538461538462",
"min_price": "4.99",
"max_price": "400.00"
},
{
"productsubcategoryname": "Monitors",
"product_count": 78,
"average_price": "330.8205128205128205",
"min_price": "49.00",
"max_price": "869.00"
},
{
"productsubcategoryname": "Laptops",
"product_count": 78,
"average_price": "653.0730769230769231",
"min_price": "320.00",
"max_price": "1299.00"
},
{
"productsubcategoryname": "Coffee Machines",
"product_count": 74,
"average_price": "540.5767567567567568",
"min_price": "129.90",
"max_price": "1650.00"
},
{
"productsubcategoryname": "Washers & Dryers",
"product_count": 70,
"average_price": "1811.6571428571428571",
"min_price": "999.00",
"max_price": "2652.90"
},
{
"productsubcategoryname": "Touch Screen Phones",
"product_count": 62,
"average_price": "291.2903225806451613",
"min_price": "189.00",
"max_price": "589.00"
},
{
"productsubcategoryname": "Air Conditioners",
"product_count": 62,
"average_price": "366.3448387096774194",
"min_price": "109.99",
"max_price": "635.99"
},
{
"productsubcategoryname": "Cameras & Camcorders Accessories",
"product_count": 55,
"average_price": "91.1001818181818182",
"min_price": "6.95",
"max_price": "595.00"
},
{
"productsubcategoryname": "Bluetooth Headphones",
"product_count": 50,
"average_price": "101.8182000000000000",
"min_price": "25.69",
"max_price": "249.99"
},
{
"productsubcategoryname": "Televisions",
"product_count": 50,
"average_price": "748.7044000000000000",
"min_price": "119.99",
"max_price": "2899.99"
},
{
"productsubcategoryname": "Car Video",
"product_count": 46,
"average_price": "426.4565217391304348",
"min_price": "199.00",
"max_price": "999.00"
},
{
"productsubcategoryname": "Boxed Games",
"product_count": 46,
"average_price": "8.2493478260869565",
"min_price": "4.98",
"max_price": "16.99"
},
{
"productsubcategoryname": "MP4&MP3",
"product_count": 45,
"average_price": "146.0562222222222222",
"min_price": "12.99",
"max_price": "299.23"
},
{
"productsubcategoryname": "Water Heaters",
"product_count": 31,
"average_price": "784.8870967741935484",
"min_price": "257.50",
"max_price": "1475.00"
},
{
"productsubcategoryname": "Cell phones Accessories",
"product_count": 27,
"average_price": "25.3385185185185185",
"min_price": "2.94",
"max_price": "129.99"
},
{
"productsubcategoryname": "VCD & DVD",
"product_count": 26,
"average_price": "107.6538461538461538",
"min_price": "56.90",
"max_price": "169.00"
},
{
"productsubcategoryname": "Recording Pen",
"product_count": 20,
"average_price": "198.1325000000000000",
"min_price": "149.95",
"max_price": "296.00"
}
],
"row_count": 30,
"columns": [
"productsubcategoryname",
"product_count",
"average_price",
"min_price",
"max_price"
],
"execution_time": 0.143827,
"error": null
}
},
"error": null
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment