NishanthPudota/NL2SQLDataset

1Instruction,Input,Output
2Get total sales by product category.,,"SELECT pc.ProductCategoryName, SUM(o.Sales) FROM Orders o JOIN Products p ON o.ProductID = p.ProductID JOIN ProductCategory pc ON p.ProductCategoryID = pc.ProductCategoryID GROUP BY pc.ProductCategoryName;"
3Show customers who ordered in 2023.,,SELECT DISTINCT c.CustomerName FROM Orders o JOIN Customer c ON o.CustomerID = c.CustomerID JOIN Datetable d ON o.DatetimeID = d.DatetimeID WHERE d.Orderyear = 2023;
4List products and their categories.,,"SELECT p.ProductName, pc.ProductCategoryName FROM Products p JOIN ProductCategory pc ON p.ProductCategoryID = pc.ProductCategoryID;"
5Find the total sales by region.,,"SELECT r.RegionName, SUM(o.Sales) FROM Orders o JOIN Area a ON o.PostalCodeID = a.PostalCodeID JOIN Region r ON a.RegionID = r.RegionID GROUP BY r.RegionName;"
6Get all orders shipped in January 2023.,,"SELECT o.OrderID, d.Shipdate FROM Orders o JOIN Datetable d ON o.DatetimeID = d.DatetimeID WHERE d.Shipdate BETWEEN '2023-01-01' AND '2023-01-31';"
7Find the top 5 customers by total sales.,,"SELECT c.CustomerName, SUM(o.Sales) AS TotalSales FROM Orders o JOIN Customer c ON o.CustomerID = c.CustomerID GROUP BY c.CustomerName ORDER BY TotalSales DESC LIMIT 5;"
8"List all products in the ""Electronics"" category.",,SELECT p.ProductName FROM Products p JOIN ProductCategory pc ON p.ProductCategoryID = pc.ProductCategoryID WHERE pc.ProductCategoryName = 'Electronics';
9Count the number of orders per segment.,,"SELECT s.SegmentName, COUNT(o.OrderID) FROM Orders o JOIN Segment s ON o.SegmentID = s.SegmentID GROUP BY s.SegmentName;"
10Show total sales by shipment mode.,,"SELECT sm.Shipmode, SUM(o.Sales) FROM Orders o JOIN Shipment sm ON o.ShipmodeID = sm.ShipmodeID GROUP BY sm.Shipmode;"
11Find orders delivered more than 7 days after order date.,,"SELECT o.OrderID, d.Orderdate, d.Shipdate FROM Orders o JOIN Datetable d ON o.DatetimeID = d.DatetimeID WHERE d.Shipdate > d.Orderdate + INTERVAL '7 days';"
12Get total sales by city.,,"SELECT ci.CityName, SUM(o.Sales) FROM Orders o JOIN Area a ON o.PostalCodeID = a.PostalCodeID JOIN City ci ON a.CityID = ci.CityID GROUP BY ci.CityName;"
13Find customers who made multiple orders.,,"SELECT c.CustomerName, COUNT(o.OrderID) AS OrderCount FROM Orders o JOIN Customer c ON o.CustomerID = c.CustomerID GROUP BY c.CustomerName HAVING COUNT(o.OrderID) > 1;"
14Retrieve all orders along with their shipment modes.,,"SELECT o.OrderID, sm.Shipmode FROM Orders o JOIN Shipment sm ON o.ShipmodeID = sm.ShipmodeID;"
15Show products sold by subcategory.,,"SELECT psc.ProductSubCategoryName, COUNT(o.OrderID) FROM Orders o JOIN Products p ON o.ProductID = p.ProductID JOIN ProductSubCategory psc ON p.ProductSubCategoryID = psc.ProductSubCategoryID GROUP BY psc.ProductSubCategoryName;"
16Find sales data for a specific postal code.,,"SELECT a.PostalCode, SUM(o.Sales) FROM Orders o JOIN Area a ON o.PostalCodeID = a.PostalCodeID WHERE a.PostalCode = '12345' GROUP BY a.PostalCode;"
17Calculate the average sales per order.,,SELECT AVG(o.Sales) AS AverageSales FROM Orders o;
18Find the number of orders placed in each year.,,"SELECT d.Orderyear, COUNT(o.OrderID) FROM Orders o JOIN Datetable d ON o.DatetimeID = d.DatetimeID GROUP BY d.Orderyear;"
19List products and their sales amounts.,,"SELECT p.ProductName, SUM(o.Sales) FROM Orders o JOIN Products p ON o.ProductID = p.ProductID GROUP BY p.ProductName;"
20Count distinct customers by region.,,"SELECT r.RegionName, COUNT(DISTINCT c.CustomerID) FROM Orders o JOIN Customer c ON o.CustomerID = c.CustomerID JOIN Area a ON o.PostalCodeID = a.PostalCodeID JOIN Region r ON a.RegionID = r.RegionID GROUP BY r.RegionName;"
21Get orders by shipment mode and year.,,"SELECT sm.Shipmode, d.Orderyear, COUNT(o.OrderID) FROM Orders o JOIN Shipment sm ON o.ShipmodeID = sm.ShipmodeID JOIN Datetable d ON o.DatetimeID = d.DatetimeID GROUP BY sm.Shipmode, d.Orderyear;"
22Find sales trends by month.,,"SELECT d.Ordermonth, SUM(o.Sales) AS MonthlySales FROM Orders o JOIN Datetable d ON o.DatetimeID = d.DatetimeID GROUP BY d.Ordermonth ORDER BY d.Ordermonth;"
23Analyze yearly growth in sales.,,"SELECT d.Orderyear, SUM(o.Sales) AS YearlySales, SUM(o.Sales) - LAG(SUM(o.Sales)) OVER (ORDER BY d.Orderyear) AS Growth FROM Orders o JOIN Datetable d ON o.DatetimeID = d.DatetimeID GROUP BY d.Orderyear;"
24Calculate cumulative sales by region.,,"SELECT r.RegionName, SUM(o.Sales) AS CumulativeSales FROM Orders o JOIN Area a ON o.PostalCodeID = a.PostalCodeID JOIN Region r ON a.RegionID = r.RegionID GROUP BY r.RegionName;"
25Find the percentage contribution of each category to total sales.,,"SELECT pc.ProductCategoryName, SUM(o.Sales) * 100.0 / SUM(SUM(o.Sales)) OVER () AS PercentageContribution FROM Orders o JOIN Products p ON o.ProductID = p.ProductID JOIN ProductCategory pc ON p.ProductCategoryID = pc.ProductCategoryID GROUP BY pc.ProductCategoryName;"
26Compare sales performance between two regions.,,"SELECT r.RegionName, SUM(o.Sales) AS TotalSales FROM Orders o JOIN Area a ON o.PostalCodeID = a.PostalCodeID JOIN Region r ON a.RegionID = r.RegionID WHERE r.RegionName IN ('Region1', 'Region2') GROUP BY r.RegionName;"
27Identify the most popular product category by sales.,,"SELECT pc.ProductCategoryName, SUM(o.Sales) AS TotalSales FROM Orders o JOIN Products p ON o.ProductID = p.ProductID JOIN ProductCategory pc ON p.ProductCategoryID = pc.ProductCategoryID GROUP BY pc.ProductCategoryName ORDER BY TotalSales DESC LIMIT 1;"
28Find the average order value per region.,,"SELECT r.RegionName, AVG(o.Sales) AS AvgOrderValue FROM Orders o JOIN Area a ON o.PostalCodeID = a.PostalCodeID JOIN Region r ON a.RegionID = r.RegionID GROUP BY r.RegionName;"
29Identify top-selling products by profit margin.,,"SELECT p.ProductName, SUM(o.Sales) - SUM(o.Cost) AS Profit FROM Orders o JOIN Products p ON o.ProductID = p.ProductID GROUP BY p.ProductName ORDER BY Profit DESC LIMIT 10;"
30Determine seasonal trends in sales.,,"SELECT d.Ordermonth, SUM(o.Sales) FROM Orders o JOIN Datetable d ON o.DatetimeID = d.DatetimeID GROUP BY d.Ordermonth ORDER BY d.Ordermonth;"
31Analyze high-risk customers with low sales and high returns.,,"SELECT c.CustomerName, SUM(o.Sales) AS TotalSales, COUNT(r.ReturnID) AS ReturnCount FROM Orders o JOIN Customer c ON o.CustomerID = c.CustomerID LEFT JOIN Returns r ON o.OrderID = r.OrderID GROUP BY c.CustomerName HAVING TotalSales < 500 AND ReturnCount > 2;"
32Evaluate shipping cost impact on sales by shipment mode.,,"SELECT sm.Shipmode, AVG(o.Sales) AS AvgSales, AVG(o.ShippingCost) AS AvgShippingCost FROM Orders o JOIN Shipment sm ON o.ShipmodeID = sm.ShipmodeID GROUP BY sm.Shipmode;"
33Calculate reorder frequency by product.,,"SELECT p.ProductName, COUNT(o.OrderID) AS ReorderCount FROM Orders o JOIN Products p ON o.ProductID = p.ProductID GROUP BY p.ProductName HAVING ReorderCount > 1;"
34Assess profitability by region and category.,,"SELECT r.RegionName, pc.ProductCategoryName, SUM(o.Sales - o.Cost) AS Profit FROM Orders o JOIN Products p ON o.ProductID = p.ProductID JOIN ProductCategory pc ON p.ProductCategoryID = pc.ProductCategoryID JOIN Area a ON o.PostalCodeID = a.PostalCodeID JOIN Region r ON a.RegionID = r.RegionID GROUP BY r.RegionName, pc.ProductCategoryName;"
35Predict high-revenue segments.,,"SELECT s.SegmentName, SUM(o.Sales) AS TotalSales FROM Orders o JOIN Segment s ON o.SegmentID = s.SegmentID GROUP BY s.SegmentName ORDER BY TotalSales DESC;"
36Identify customers with declining purchase trends.,,"SELECT c.CustomerName, SUM(o.Sales) AS CurrentYearSales, LAG(SUM(o.Sales)) OVER (PARTITION BY c.CustomerID ORDER BY d.Orderyear) AS PreviousYearSales FROM Orders o JOIN Customer c ON o.CustomerID = c.CustomerID JOIN Datetable d ON o.DatetimeID = d.DatetimeID GROUP BY c.CustomerName, d.Orderyear HAVING CurrentYearSales < PreviousYearSales;"
37Calculate product contribution margin.,,"SELECT p.ProductName, (SUM(o.Sales) - SUM(o.Cost)) / SUM(o.Sales) * 100 AS ContributionMargin FROM Orders o JOIN Products p ON o.ProductID = p.ProductID GROUP BY p.ProductName ORDER BY ContributionMargin DESC;"
38Find regions with declining sales trends.,,"SELECT r.RegionName, SUM(o.Sales) AS CurrentYearSales, LAG(SUM(o.Sales)) OVER (PARTITION BY r.RegionName ORDER BY d.Orderyear) AS PreviousYearSales FROM Orders o JOIN Area a ON o.PostalCodeID = a.PostalCodeID JOIN Region r ON a.RegionID = r.RegionID JOIN Datetable d ON o.DatetimeID = d.DatetimeID GROUP BY r.RegionName, d.Orderyear HAVING CurrentYearSales < PreviousYearSales;"
39Evaluate customer lifetime value (CLV).,,"SELECT c.CustomerName, SUM(o.Sales) AS TotalValue FROM Orders o JOIN Customer c ON o.CustomerID = c.CustomerID GROUP BY c.CustomerName ORDER BY TotalValue DESC;"
40Determine most profitable shipment modes.,,"SELECT sm.Shipmode, SUM(o.Sales - o.ShippingCost) AS Profit FROM Orders o JOIN Shipment sm ON o.ShipmodeID = sm.ShipmodeID GROUP BY sm.Shipmode ORDER BY Profit DESC;"
41Analyze product bundling opportunities.,,"SELECT p.ProductName, COUNT(o.OrderID) AS OrderCount FROM Orders o JOIN Products p ON o.ProductID = p.ProductID GROUP BY p.ProductName ORDER BY OrderCount DESC;"
42Assess high-performing regions by profit.,,"SELECT r.RegionName, SUM(o.Sales - o.Cost) AS Profit FROM Orders o JOIN Area a ON o.PostalCodeID = a.PostalCodeID JOIN Region r ON a.RegionID = r.RegionID GROUP BY r.RegionName ORDER BY Profit DESC;"
43Compare cost-to-sales ratio by category.,,"SELECT pc.ProductCategoryName, SUM(o.Cost) / SUM(o.Sales) * 100 AS CostToSalesRatio FROM Orders o JOIN Products p ON o.ProductID = p.ProductID JOIN ProductCategory pc ON p.ProductCategoryID = pc.ProductCategoryID GROUP BY pc.ProductCategoryName ORDER BY CostToSalesRatio;"
44Analyze regional growth patterns.,,"SELECT r.RegionName, d.Orderyear, SUM(o.Sales) AS YearlySales FROM Orders o JOIN Area a ON o.PostalCodeID = a.PostalCodeID JOIN Region r ON a.RegionID = r.RegionID JOIN Datetable d ON o.DatetimeID = d.DatetimeID GROUP BY r.RegionName, d.Orderyear ORDER BY r.RegionName, d.Orderyear;"
45Evaluate product sales performance across regions.,,"SELECT r.RegionName, p.ProductName, SUM(o.Sales) AS TotalSales FROM Orders o JOIN Products p ON o.ProductID = p.ProductID JOIN Area a ON o.PostalCodeID = a.PostalCodeID JOIN Region r ON a.RegionID = r.RegionID GROUP BY r.RegionName, p.ProductName ORDER BY TotalSales DESC;"
46Compare average sales per product in each region.,,"SELECT r.RegionName, p.ProductName, AVG(o.Sales) AS AvgSales FROM Orders o JOIN Products p ON o.ProductID = p.ProductID JOIN Area a ON o.PostalCodeID = a.PostalCodeID JOIN Region r ON a.RegionID = r.RegionID GROUP BY r.RegionName, p.ProductName;"
47