dataset:
  name: "QueryMind SQL Eval - Basic"
  description: "Basic SQL generation tasks for evaluating QueryMind's SQL capabilities"

  test_cases:
    - id: "sql_001"
      database_id: "adventureworks"
      dialect: "postgres"

      user_id: "admin"
      username: "Xihong"
      email: "admin@querymind"
      user_groups: ["admin", "user"]

      query: "Write a query in SQL to return only the rows for product that have a sellstartdate that is not NULL and a productline of 'T'. Return productid, productnumber, and name. Arranged the output in ascending order on name."
      ground_truth_sql: |
        SELECT productid, productnumber, name as producName
        FROM production.product
        WHERE sellstartdate IS NOT NULL
        AND production.product.productline= 'T'
        ORDER BY name;

      tags: ["filtering", "null_handling", "ordering"]
      difficulty: "easy"

      metadata:
        category: "filtering"
        source: "manual"
        query_language: "en"

      expected_outcome:
        tools_called: ["schema_retrieve", "run_sql"]
        final_answer_contains: ["SELECT", "FROM", "WHERE", "ORDER BY"]
        max_execution_time_ms: 90000

    - id: "sql_002"
      database_id: "adventureworks"
      dialect: "postgres"

      user_id: "admin"
      username: "Xihong"
      email: "admin@querymind"
      user_groups: ["admin", "user"]

      query: "write a query in SQL to calculate the total freight paid by each customer. Return customerid and total freight. Sort the output in ascending order on customerid."
      ground_truth_sql: |
        SELECT customerid, sum(freight) as total_freight 
        FROM sales.salesorderheader
        GROUP BY customerid
        ORDER BY customerid ASC;


      tags: ["aggregation", "group_by", "ordering"]
      difficulty: "easy"

      metadata:
        category: "aggregation"
        source: "manual"
        query_language: "en"

      expected_outcome:
        tools_called: ["schema_retrieve", "run_sql"]
        final_answer_contains: ["SELECT", "FROM", "GROUP BY", "ORDER BY"]
        max_execution_time_ms: 90000
    
    - id: "sql_003"
      database_id: "adventureworks"
      dialect: "postgres"

      user_id: "admin"
      username: "Xihong"
      email: "admin@querymind"
      user_groups: ["admin", "user"]

      query: "Write a query in SQL to find the sum, average, count, minimum, and maximum order quentity for those orders whose id are 43659 and 43664. Return SalesOrderID, ProductID, OrderQty, sum, average, count, max, and min order quantity."
      ground_truth_sql: |
        SELECT
          SalesOrderID,
          ProductID,
          OrderQty,
          SUM(OrderQty) OVER win AS "Total Quantity",
          AVG(OrderQty) OVER win AS "Avg Quantity",
          COUNT(OrderQty) OVER win AS "No of Orders",
          MIN(OrderQty) OVER win AS "Min Quantity",
          MAX(OrderQty) OVER win AS "Max Quantity"
        FROM Sales.SalesOrderDetail
        WHERE SalesOrderID IN (43659, 43664)
        WINDOW win AS (PARTITION BY SalesOrderID);

      tags: ["window", "aggregation", "filtering"]
      difficulty: "medium"

      metadata:
        category: "window"
        source: "manual"
        query_language: "en"

      expected_outcome:
        tools_called: ["schema_retrieve", "schema_retrieve", "run_sql"]
        final_answer_contains: ["SELECT", "FROM", "OVER", "PARTITION BY"]
        max_execution_time_ms: 120000
    
    - id: "sql_004"
      database_id: "adventureworks"
      dialect: "postgres"

      user_id: "admin"
      username: "Xihong"
      email: "admin@querymind"
      user_groups: ["admin", "user"]

      query: "Create a SQL query from the Sales Order Detail table to retrieve the product name and any associated sales orders. Additionally, it returns any sales orders that don't have any items mentioned in the Product table as well as any products that have sales orders other than those that are listed there. Return product name, salesorderid. Sort the result set on product name column."
      ground_truth_sql: |
        SELECT p.Name, sod.SalesOrderID  
        FROM Production.Product AS p  
        FULL OUTER JOIN Sales.SalesOrderDetail AS sod  
        ON p.ProductID = sod.ProductID  
        ORDER BY p.Name ;

      tags: ["join", "multi_table", "ordering"]
      difficulty: "medium"

      metadata:
        category: "join"
        source: "manual"
        query_language: "en"

      expected_outcome:
        tools_called: ["schema_retrieve", "schema_retrieve", "run_sql"]
        final_answer_contains: ["SELECT", "FROM", "JOIN", "ORDER BY"]
        max_execution_time_ms: 120000

    - id: "sql_005"
      database_id: "adventureworks"
      dialect: "postgres"

      user_id: "admin"
      username: "Xihong"
      email: "admin@querymind"
      user_groups: ["admin", "user"]

      query: "Write a SQL query to retrieve the territory name and BusinessEntityID. The result set includes all salespeople, regardless of whether or not they are assigned a territory."
      ground_truth_sql: |
        SELECT st.Name AS Territory, sp.BusinessEntityID  
        FROM Sales.SalesTerritory AS st   
        RIGHT OUTER JOIN Sales.SalesPerson AS sp  
        ON st.TerritoryID = sp.TerritoryID ;

      tags: ["join", "multi_table"]
      difficulty: "medium"

      metadata:
        category: "join"
        source: "manual"
        query_language: "en"

      expected_outcome:
        tools_called: ["schema_retrieve", "schema_retrieve", "run_sql"]
        final_answer_contains: ["SELECT", "FROM", "JOIN", "ON"]
        max_execution_time_ms: 120000

    - id: "sql_006"
      database_id: "adventureworks"
      dialect: "postgres"

      user_id: "admin"
      username: "Xihong"
      email: "admin@querymind"
      user_groups: ["admin", "user"]

      query: "Write a SQL query to retrieve the mailing address for any company that is outside the United States (US) and in a city whose name starts with Pa. Return Addressline1, Addressline2, city, postalcode, countryregioncode columns."
      ground_truth_sql: |
        SELECT
          a.AddressLine1,
          a.AddressLine2,
          a.City,
          a.PostalCode,
          s.CountryRegionCode
        FROM Person.Address AS a
        JOIN Person.StateProvince AS s
          ON a.StateProvinceID = s.StateProvinceID
        WHERE s.CountryRegionCode <> 'US'
          AND a.City LIKE 'Pa%';

      tags: ["join", "text_filter", "multi_condition"]
      difficulty: "easy"

      metadata:
        category: "filtering"
        source: "manual"
        query_language: "en"

      expected_outcome:
        tools_called: ["schema_retrieve", "schema_retrieve", "run_sql"]
        final_answer_contains: ["SELECT", "FROM", "JOIN", "WHERE"]
        max_execution_time_ms: 90000

    - id: "sql_007"
      database_id: "adventureworks"
      dialect: "postgres"

      user_id: "admin"
      username: "Xihong"
      email: "admin@querymind"
      user_groups: ["admin", "user"]

      query: "write a query in SQL to return the name, list price, and the alias 'LeastExpensive' for the product(s) that have the lowest list price within a given product subcategory (ProductSubcategoryID = 37). Ensure the query filters the results to this specific subcategory and correctly identifies the least expensive product(s)."
      ground_truth_sql: |
        SELECT
          p.Name,
          p.ListPrice,
          p.Name AS LeastExpensive
        FROM Production.Product AS p
        JOIN (
          SELECT MIN(ListPrice) AS MinPrice
          FROM Production.Product
          WHERE ProductSubcategoryID = 37
        ) AS sub
          ON p.ListPrice = sub.MinPrice
        WHERE p.ProductSubcategoryID = 37;

      tags: ["subquery", "aggregation", "filtering"]
      difficulty: "medium"

      metadata:
        category: "subquery"
        source: "manual"
        query_language: "en"

      expected_outcome:
        tools_called: ["schema_retrieve", "schema_retrieve", "run_sql"]
        final_answer_contains: ["SELECT", "FROM", "JOIN", "WHERE"]
        max_execution_time_ms: 120000

    - id: "sql_008"
      database_id: "adventureworks"
      dialect: "postgres"

      user_id: "admin"
      username: "Xihong"
      email: "admin@querymind"
      user_groups: ["admin", "user"]

      query: "Write a query in SQL to return the employee with the fewest number of vacation hours compared to other employees with the same job title. Partitions the employees by job title and apply the first value to each partition independently. "
      ground_truth_sql: |
        SELECT
          e.JobTitle,
          p.LastName,
          e.VacationHours,
          FIRST_VALUE(p.LastName) OVER (
            PARTITION BY e.JobTitle
            ORDER BY e.VacationHours ASC
            ROWS UNBOUNDED PRECEDING
          ) AS FewestVacationHours
        FROM HumanResources.Employee AS e
        INNER JOIN Person.Person AS p
          ON e.BusinessEntityID = p.BusinessEntityID
        ORDER BY e.JobTitle;

      tags: ["window", "ranking", "join", "ordering"]
      difficulty: "medium"

      metadata:
        category: "window"
        source: "manual"
        query_language: "en"

      expected_outcome:
        tools_called: ["schema_retrieve", "schema_retrieve", "run_sql"]
        final_answer_contains: ["SELECT", "FROM", "OVER", "PARTITION BY"]
        max_execution_time_ms: 120000

    - id: "sql_009"
      database_id: "adventureworks"
      dialect: "postgres"

      user_id: "admin"
      username: "Xihong"
      email: "admin@querymind"
      user_groups: ["admin", "user"]

      query: "Write a query in SQL to return the department, last name, rate, hire date, and the last hire date within each department partitioned by rate. The query should retrieve data for specific departments ('Information Services' and 'Document Control')."
      ground_truth_sql: |
        SELECT Department
            , LastName
            , Rate
            , HireDate
            , LAST_VALUE(HireDate) OVER (
                PARTITION BY Department ORDER BY Rate
                ) AS LastValue
        FROM HumanResources.vEmployeeDepartmentHistory AS edh
        INNER JOIN HumanResources.EmployeePayHistory AS eph
            ON eph.BusinessEntityID = edh.BusinessEntityID
        INNER JOIN HumanResources.Employee AS e
            ON e.BusinessEntityID = edh.BusinessEntityID
        WHERE Department IN ('Information Services', 'Document Control');

      tags: ["window", "join", "ordering", "multi_table"]
      difficulty: "medium"

      metadata:
        category: "window"
        source: "manual"
        query_language: "en"

      expected_outcome:
        tools_called: ["schema_retrieve", "schema_retrieve", "schema_retrieve", "run_sql"]
        final_answer_contains: ["SELECT", "FROM", "OVER", "PARTITION BY"]
        max_execution_time_ms: 120000

    - id: "sql_010"
      database_id: "adventureworks"
      dialect: "postgres"

      user_id: "admin"
      username: "Xihong"
      email: "admin@querymind"
      user_groups: ["admin", "user"]

      query: "Write a query in SQL to compare year-to-date sales between employees. Return TerritoryName, BusinessEntityID, SalesYTD, and sales of previous year i.e.PrevRepSales. Sort the result set in ascending order on territory name."
      ground_truth_sql: |
        SELECT TerritoryName, BusinessEntityID, SalesYTD,
              LAG(SalesYTD, 1, 0) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS PrevRepSales
        FROM Sales.vSalesPerson
        ORDER BY TerritoryName;        

      tags: ["window", "comparison", "ordering", "filtering"]
      difficulty: "medium"

      metadata:
        category: "window"
        source: "manual"
        query_language: "en"

      expected_outcome:
        tools_called: ["schema_retrieve", "schema_retrieve", "run_sql"]
        final_answer_contains: ["SELECT", "FROM", "OVER", "PARTITION BY"]
        max_execution_time_ms: 120000

    - id: "sql_011"
      database_id: "adventureworks"
      dialect: "postgres"

      user_id: "admin"
      username: "Xihong"
      email: "admin@querymind"
      user_groups: ["admin", "user"]

      query: "Write a query in SQL to return the BusinessEntityID, quarter, sales year, and sales quota for the current quarter. Additionally, calculate the differences in sales quota from the first and last quarters of the year for each salesperson. Only include records for years after 2005 and for BusinessEntityIDs between 274 and 275. Order the results by BusinessEntityID, sales year, and quarter."
      ground_truth_sql: |
        SELECT BusinessEntityID,
              DATE_PART('quarter', QuotaDate) AS Quarter,
              date_part('year',QuotaDate) AS SalesYear,
              SalesQuota AS QuotaThisQuarter,
              SalesQuota - FIRST_VALUE(SalesQuota) OVER (
                  PARTITION BY BusinessEntityID, date_part('year',QuotaDate)
                  ORDER BY DATE_PART('quarter', QuotaDate)
              ) AS DifferenceFromFirstQuarter,
              SalesQuota - LAST_VALUE(SalesQuota) OVER (
                  PARTITION BY BusinessEntityID, date_part('year',QuotaDate)
                  ORDER BY DATE_PART('quarter', QuotaDate)
                  RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
              ) AS DifferenceFromLastQuarter
        FROM Sales.SalesPersonQuotaHistory
        WHERE date_part('year',QuotaDate) > 2005 AND BusinessEntityID BETWEEN 274 AND 275
        ORDER BY BusinessEntityID, SalesYear, Quarter;        

      tags: ["window", "comparison", "date_filter", "ordering"]
      difficulty: "medium"

      metadata:
        category: "window"
        source: "manual"
        query_language: "en"

      expected_outcome:
        tools_called: ["schema_retrieve", "schema_retrieve", "run_sql"]
        final_answer_contains: ["SELECT", "FROM", "OVER", "PARTITION BY"]
        max_execution_time_ms: 120000

    - id: "sql_012"
      database_id: "adventureworks"
      dialect: "postgres"

      user_id: "admin"
      username: "Xihong"
      email: "admin@querymind"
      user_groups: ["admin", "user"]

      query: "Write a query in SQL to obtain a newdate by adding two days with current date for each salespersons. Filter the result set for those salespersons whose sales value is more than zero."
      ground_truth_sql: |
        SELECT 
            p.FirstName, 
            p.LastName,  
            (now() + INTERVAL '2 day') AS "New Date" 
        FROM 
            Sales.SalesPerson AS s   
            INNER JOIN Person.Person AS p   
                ON s.BusinessEntityID = p.BusinessEntityID  
            INNER JOIN Person.Address AS a   
                ON a.AddressID = p.BusinessEntityID  
        WHERE 
            TerritoryID IS NOT NULL   
            AND SalesYTD <> 0;        

      tags: ["date_filter", "numeric_filter", "join", "multi_table"]
      difficulty: "easy"

      metadata:
        category: "date_filter"
        source: "manual"
        query_language: "en"

      expected_outcome:
        tools_called: ["schema_retrieve", "schema_retrieve", "run_sql"]
        final_answer_contains: ["SELECT", "FROM", "JOIN", "WHERE"]
        max_execution_time_ms: 90000

    - id: "sql_013"
      database_id: "adventureworks"
      dialect: "postgres"

      user_id: "admin"
      username: "Xihong"
      email: "admin@querymind"
      user_groups: ["admin", "user"]

      query: "Write a query in SQL to divide rows into four groups of employees based on their year-to-date sales. Return first name, last name, group as quartile, year-to-date sales, and postal code. "
      ground_truth_sql: |
        SELECT 
            p.FirstName, 
            p.LastName,  
            NTILE(4) OVER(ORDER BY SalesYTD DESC) AS Quartile,  
            CAST(SalesYTD as VARCHAR(20)) AS SalesYTD,  
            a.PostalCode  
        FROM 
            Sales.SalesPerson AS s   
            INNER JOIN Person.Person AS p   
                ON s.BusinessEntityID = p.BusinessEntityID  
            INNER JOIN Person.Address AS a   
                ON a.AddressID = p.BusinessEntityID  
        WHERE 
            TerritoryID IS NOT NULL   
            AND SalesYTD <> 0;        

      tags: ["ranking", "window", "ordering", "join", "multi_table", "numeric_filter", "null_handling"]
      difficulty: "medium"

      metadata:
        category: "ranking"
        source: "manual"
        query_language: "en"

      expected_outcome:
        tools_called: ["schema_retrieve", "schema_retrieve", "schema_retrieve", "run_sql"]
        final_answer_contains: ["SELECT", "FROM", "OVER", "ORDER BY"]
        max_execution_time_ms: 120000

    - id: "sql_014"
      database_id: "adventureworks"
      dialect: "postgres"

      user_id: "admin"
      username: "Xihong"
      email: "admin@querymind"
      user_groups: ["admin", "user"]

      query: "Write a query in SQL to rank the products in inventory with locationID values between 3 and 4 according to their quantities. The result set is partitioned by LocationID and logically ordered by Quantity. Return productid, name, locationid, quantity, and rank."
      ground_truth_sql: |
        SELECT 
            i.ProductID, 
            p.Name, 
            i.LocationID, 
            i.Quantity,  
        RANK() OVER (
                PARTITION BY i.LocationID
                ORDER BY i.Quantity DESC
            ) AS Rank  
        FROM 
            Production.ProductInventory AS i   
            INNER JOIN Production.Product AS p   
                ON i.ProductID = p.ProductID  
        WHERE 
            i.LocationID BETWEEN 3 AND 4  
        ORDER BY 
            i.LocationID,
            i.Quantity DESC;        

      tags: ["ranking", "window", "ordering", "join", "multi_table", "numeric_filter"]
      difficulty: "medium"

      metadata:
        category: "ranking"
        source: "manual"
        query_language: "en"

      expected_outcome:
        tools_called: ["schema_retrieve", "schema_retrieve", "run_sql"]
        final_answer_contains: ["SELECT", "FROM", "OVER", "PARTITION BY"]
        max_execution_time_ms: 120000

    - id: "sql_015"
      database_id: "adventureworks"
      dialect: "postgres"

      user_id: "admin"
      username: "Xihong"
      email: "admin@querymind"
      user_groups: ["admin", "user"]

      query: "Write a query in SQL to calculate the salary percentile for each employee within the 'Information Services' and 'Document Control' departments. The query should return the department, last name, rate, cumulative distribution (CUME_DIST), and percent rank (PERCENT_RANK) of the rate. The result set should be ordered by department in ascending order and rate in descending order. "
      ground_truth_sql: |
        SELECT Department, LastName, Rate,   
              CUME_DIST () OVER (PARTITION BY Department ORDER BY Rate) AS CumeDist,   
              PERCENT_RANK() OVER (PARTITION BY Department ORDER BY Rate) AS PctRank  
        FROM HumanResources.vEmployeeDepartmentHistory AS edh  
            INNER JOIN HumanResources.EmployeePayHistory AS e    
            ON e.BusinessEntityID = edh.BusinessEntityID  
        WHERE Department IN ('Information Services','Document Control')   
        ORDER BY Department, Rate DESC;        

      tags: ["window", "ranking", "comparison", "ordering", "join", "multi_table"]
      difficulty: "hard"

      metadata:
        category: "window"
        source: "manual"
        query_language: "en"

      expected_outcome:
        tools_called: ["schema_retrieve", "schema_retrieve", "schema_retrieve", "run_sql"]
        final_answer_contains: ["SELECT", "FROM", "OVER", "PARTITION BY"]
        max_execution_time_ms: 150000

    - id: "sql_016"
      database_id: "adventureworks"
      dialect: "postgres"

      user_id: "admin"
      username: "Xihong"
      email: "admin@querymind"
      user_groups: ["admin", "user"]

      query: "Write a query in SQL to return the business entity ID, sales year, current sales quota, and the next year's sales quota for a specific salesperson. Use the LEAD window function to get the next year's sales quota, defaulting to 0 if not available. Filter the results for the salesperson with BusinessEntityID 277 for the years 2022 and 2023."
      ground_truth_sql: |
        SELECT 
            BusinessEntityID, 
            date_part('year', QuotaDate) AS SalesYear, 
            SalesQuota AS CurrentQuota,   
            LEAD(SalesQuota, 1, 0) OVER (
                -- Ordering the data by year
                ORDER BY date_part('year', QuotaDate)
            ) AS NextQuota  
        FROM 
            Sales.SalesPersonQuotaHistory  
        WHERE 
            BusinessEntityID = 277 
            AND date_part('year', QuotaDate) IN ('2022', '2023');

      tags: ["window", "comparison", "date_filter", "ordering"]
      difficulty: "hard"

      metadata:
        category: "window"
        source: "manual"
        query_language: "en"

      expected_outcome:
        tools_called: ["schema_retrieve", "schema_retrieve", "run_sql"]
        final_answer_contains: ["SELECT", "FROM", "OVER", "ORDER BY"]
        max_execution_time_ms: 150000

    - id: "sql_017"
      database_id: "adventureworks"
      dialect: "postgres"

      user_id: "admin"
      username: "Xihong"
      email: "admin@querymind"
      user_groups: ["admin", "user"]

      query: "Write a query in SQL to return only rows with a count of employees by department. Filter the results by department ID 8 or 10. Return name, jobtitle, grouping level and employee count."
      ground_truth_sql: |
        SELECT D.Name  
            ,E.JobTitle  
            ,GROUPING(D.Name, E.JobTitle) AS "Grouping Level"  
            ,COUNT(E.BusinessEntityID) AS "Employee Count"  
        FROM HumanResources.Employee AS E  
        INNER JOIN HumanResources.EmployeeDepartmentHistory AS DH  
            ON E.BusinessEntityID = DH.BusinessEntityID  
        INNER JOIN HumanResources.Department AS D  
            ON D.DepartmentID = DH.DepartmentID       
        WHERE DH.EndDate IS NULL  
            AND D.DepartmentID IN (8, 10)  
        GROUP BY ROLLUP(D.Name, E.JobTitle)
        HAVING GROUPING(D.Name, E.JobTitle) = 2;        

      tags: ["aggregation", "group_by", "having", "join", "multi_table"]
      difficulty: "hard"

      metadata:
        category: "aggregation"
        source: "manual"
        query_language: "en"

      expected_outcome:
        tools_called: ["schema_retrieve", "schema_retrieve", "schema_retrieve", "run_sql"]
        final_answer_contains: ["SELECT", "FROM", "GROUP BY", "HAVING"]
        max_execution_time_ms: 150000

    - id: "sql_018"
      database_id: "adventureworks"
      dialect: "postgres"

      user_id: "admin"
      username: "Xihong"
      email: "admin@querymind"
      user_groups: ["admin", "user"]

      query: "Write a query in SQL to return the difference in sales quotas for a specific employee over previous calendar quarters. Sort the results by salesperson with businessentity id 277 and quotadate year 2022 or 2023."
      ground_truth_sql: |
        SELECT quotadate AS Year, date_part('quarter',quotadate) AS Quarter, SalesQuota AS SalesQuota,  
              LAG(SalesQuota) OVER (ORDER BY date_part('year',quotadate), date_part('quarter',quotadate)) AS PrevQuota,  
              SalesQuota - LAG(SalesQuota) OVER (ORDER BY date_part('year',quotadate), date_part('quarter',quotadate)) AS Diff  
        FROM sales.salespersonquotahistory  
        WHERE businessentityid = 277 AND date_part('year',quotadate) IN (2022, 2023)  
        ORDER BY date_part('year',quotadate), date_part('quarter',quotadate);        

      tags: ["window", "comparison", "date_filter", "ordering"]
      difficulty: "hard"

      metadata:
        category: "window"
        source: "manual"
        query_language: "en"

      expected_outcome:
        tools_called: ["schema_retrieve", "schema_retrieve", "run_sql"]
        final_answer_contains: ["SELECT", "FROM", "OVER", "ORDER BY"]
        max_execution_time_ms: 150000

    - id: "sql_019"
      database_id: "adventureworks"
      dialect: "postgres"

      user_id: "admin"
      username: "Xihong"
      email: "admin@querymind"
      user_groups: ["admin", "user"]

      query: "Write a query in SQL to find product models where the maximum list price is more than twice the average."
      ground_truth_sql: |
        SELECT p1.ProductModelID
        FROM Production.Product AS p1
        GROUP BY p1.ProductModelID
        HAVING MAX(p1.ListPrice) > 
            (SELECT AVG(p2.ListPrice) * 2
            FROM Production.Product AS p2
            WHERE p1.ProductModelID = p2.ProductModelID);        

      tags: ["aggregation", "subquery", "group_by", "having", "comparison"]
      difficulty: "hard"

      metadata:
        category: "aggregation"
        source: "manual"
        query_language: "en"

      expected_outcome:
        tools_called: ["schema_retrieve", "schema_retrieve", "run_sql"]
        final_answer_contains: ["SELECT", "FROM", "GROUP BY", "HAVING"]
        max_execution_time_ms: 150000

    - id: "sql_020"
      database_id: "adventureworks"
      dialect: "postgres"

      user_id: "admin"
      username: "Xihong"
      email: "admin@querymind"
      user_groups: ["admin", "user"]

      query: "Write a query in SQL to find the first and last names of employees who have sold a product with the ProductNumber 'BK-M68B-42'. Return the distinct columns LastName and FirstName."
      ground_truth_sql: |
        SELECT DISTINCT pp.LastName, pp.FirstName 
        FROM Person.Person pp JOIN HumanResources.Employee e
        ON e.BusinessEntityID = pp.BusinessEntityID 
        WHERE pp.BusinessEntityID IN 
        (SELECT SalesPersonID 
        FROM Sales.SalesOrderHeader
        WHERE SalesOrderID IN 
            (SELECT SalesOrderID 
            FROM Sales.SalesOrderDetail
            WHERE ProductID IN 
                (SELECT ProductID 
                FROM Production.Product p 
                WHERE ProductNumber = 'BK-M68B-42')));        

      tags: ["distinct", "subquery", "multi_table", "join"]
      difficulty: "hard"

      metadata:
        category: "subquery"
        source: "manual"
        query_language: "en"

      expected_outcome:
        tools_called: ["schema_retrieve", "schema_retrieve", "schema_retrieve", "run_sql"]
        final_answer_contains: ["SELECT", "DISTINCT", "FROM", "IN"]
        max_execution_time_ms: 150000
