server/tasks/easy.py

"""
nl2sql-bench/server/tasks/easy.py
===================================
Task 1 — Simple Filter  (difficulty: easy)

All questions target a SINGLE table with basic WHERE / ORDER BY / LIMIT.
A competent small model should solve these in 1–2 steps.
"""

from __future__ import annotations

from .base import BaseTask, TaskExample, register


@register
class SimpleFilterTask(BaseTask):
    name = "simple-filter"
    difficulty = "easy"

    examples = [
        TaskExample(
            question=(
                "List all gold-tier customers ordered by their name alphabetically. "
                "Return columns: id, name, email, country."
            ),
            sql=(
                "SELECT id, name, email, country "
                "FROM customers "
                "WHERE tier = 'gold' "
                "ORDER BY name ASC"
            ),
            notes="Single table, equality filter, text sort.",
        ),
        TaskExample(
            question=(
                "Show all products with a price above $100, sorted by price from "
                "highest to lowest. Return columns: id, name, price."
            ),
            sql=(
                "SELECT id, name, price "
                "FROM products "
                "WHERE price > 100 "
                "ORDER BY price DESC"
            ),
            notes="Numeric range filter, descending sort.",
        ),
        TaskExample(
            question=(
                "Find all delivered orders with a total_amount greater than $200, "
                "ordered by total_amount descending. "
                "Return columns: id, customer_id, total_amount, created_at."
            ),
            sql=(
                "SELECT id, customer_id, total_amount, created_at "
                "FROM orders "
                "WHERE status = 'delivered' "
                "  AND total_amount > 200 "
                "ORDER BY total_amount DESC"
            ),
            notes="Two-condition WHERE on a single table.",
        ),
        TaskExample(
            question=(
                "Return the top 5 most expensive products. "
                "Return columns: id, name, price."
            ),
            sql=(
                "SELECT id, name, price "
                "FROM products "
                "ORDER BY price DESC "
                "LIMIT 5"
            ),
            notes="ORDER BY + LIMIT, no WHERE clause.",
        ),
        TaskExample(
            question=(
                "List all distinct countries where our customers come from, "
                "sorted alphabetically. Return a single column: country."
            ),
            sql=(
                "SELECT DISTINCT country "
                "FROM customers "
                "ORDER BY country ASC"
            ),
            notes="DISTINCT on a single column.",
        ),
    ]

    def description(self) -> str:
        return (
            "Single-table SELECT queries with WHERE filters, ORDER BY, and LIMIT. "
            "Tests basic SQL fluency."
        )