GenAI for Data Work: Text-to-SQL, AI Coding Assistants, Data Cleaning and Automated Insights

import re, json, duckdb, pandas as pd, numpy as np
sales = pd.read_csv("superstore_sales.csv", parse_dates=["order_date", "ship_date"])
con = duckdb.connect(); con.register("sales", sales)
schema = "\n".join(f"  {c} {t}" for c, t, *_ in con.execute("DESCRIBE sales").fetchall())
PROMPT = f"""You write DuckDB SQL for an Indian office-supplies retailer.

<schema>
TABLE sales (one row per order line)
{schema}
</schema>

<definitions>
- Revenue = SUM(sales); profit margin % = 100 * SUM(profit) / SUM(sales).
- An order is a distinct order_id; AOV = revenue / number of orders.
- Years are calendar years of order_date. Amounts are in INR.
</definitions>

<rules>
- Return ONE SELECT statement only, no comments or explanations.
- Never modify data. Use explicit column names. Round money to 2 decimals.
- If the question cannot be answered from this table, return: SELECT 'cannot answer' AS message
</rules>

<question>{{question}}</question>"""
print(PROMPT.format(question="Which region had the highest profit margin in 2025?")[:900])

FORBIDDEN = r"\b(insert|update|delete|drop|alter|create|replace|copy|attach|detach|pragma|install|load|export)\b"
def safe_execute(sql_text, max_rows=200):
    sql_text = sql_text.strip().strip("`").removeprefix("sql").strip().rstrip(";")
    if ";" in sql_text: raise ValueError("multiple statements are not allowed")
    if not re.match(r"^(with|select)\b", sql_text, re.I): raise ValueError("only SELECT/WITH queries are allowed")
    if re.search(FORBIDDEN, sql_text, re.I): raise ValueError("forbidden keyword")
    return con.execute(f"SELECT * FROM ({sql_text}) LIMIT {max_rows}").df()
print(safe_execute("```sql\nSELECT region, ROUND(100*SUM(profit)/SUM(sales), 2) AS margin FROM sales WHERE YEAR(order_date)=2025 GROUP BY 1 ORDER BY 2 DESC\n```"))
for bad in ["DROP TABLE sales", "SELECT 1; DELETE FROM sales", "COPY sales TO 'leak.csv'"]:
    try: safe_execute(bad)
    except ValueError as e: print("blocked:", bad, "→", e)

GOLD = {
    "Total revenue in 2024 in crores": "SELECT ROUND(SUM(sales)/1e7, 2) FROM sales WHERE YEAR(order_date) = 2024",
    "Number of orders in 2025": "SELECT COUNT(DISTINCT order_id) FROM sales WHERE YEAR(order_date) = 2025",
    "Top 3 states by profit in 2025": "SELECT state FROM sales WHERE YEAR(order_date) = 2025 GROUP BY state ORDER BY SUM(profit) DESC LIMIT 3",
    "Average order value in 2025": "SELECT ROUND(SUM(sales) / COUNT(DISTINCT order_id), 2) FROM sales WHERE YEAR(order_date) = 2025",
    "Share of lines with discount above 20%": "SELECT ROUND(100.0 * AVG(CASE WHEN discount > 0.2 THEN 1 ELSE 0 END), 2) FROM sales",
}
GENERATED = {   # what a model returned (one plausible correct, one subtly wrong, one different-but-correct, ...)
    "Total revenue in 2024 in crores": "SELECT ROUND(SUM(sales) / 10000000, 2) AS revenue_cr FROM sales WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'",
    "Number of orders in 2025": "SELECT COUNT(order_id) FROM sales WHERE YEAR(order_date) = 2025",                      # counts lines, not orders
    "Top 3 states by profit in 2025": "SELECT state FROM (SELECT state, SUM(profit) p FROM sales WHERE YEAR(order_date)=2025 GROUP BY state) ORDER BY p DESC LIMIT 3",
    "Average order value in 2025": "SELECT ROUND(AVG(sales), 2) FROM sales WHERE YEAR(order_date) = 2025",               # average line value, not order value
    "Share of lines with discount above 20%": "SELECT ROUND(100.0 * COUNT(*) FILTER (WHERE discount > 0.2) / COUNT(*), 2) FROM sales",
}
def same_result(a, b): return [tuple(r) for r in a.round(2).astype(str).values] == [tuple(r) for r in b.round(2).astype(str).values]
rows = []
for q, g in GOLD.items():
    try: ok = same_result(safe_execute(GENERATED[q]), safe_execute(g))
    except Exception as e: ok = False
    rows.append({"question": q, "execution_match": ok})
r = pd.DataFrame(rows); print(r.to_string(index=False)); print(f"execution accuracy: {r.execution_match.mean():.0%}")

# A typical AI-drafted cleaning snippet, with the reconciliation checks you should always add
before_rows, before_sales = len(sales), sales.sales.sum()
clean = sales.drop_duplicates().assign(margin=lambda d: d.profit / d.sales)
clean = clean[clean.sales > 0]
assert len(clean) == before_rows, "row count changed: investigate before continuing"
assert abs(clean.sales.sum() - before_sales) < 1, "revenue no longer reconciles"
print("checks passed:", len(clean), "rows, revenue", f"{clean.sales.sum():,.0f}")

from difflib import get_close_matches
CANONICAL = ["Bengaluru", "Mumbai", "Delhi", "Hyderabad", "Chennai", "Kolkata", "Pune", "Ahmedabad"]
raw = ["Bangalore", "bengaluru ", "BLR", "Bombay", "mumbai", "New Delhi", "Dilli", "Hyd", "Calcutta", "Poona", "Pune-411001", "Gurgaon"]
def rule_based(x):
    m = get_close_matches(x.strip().title().split("-")[0], CANONICAL, n=1, cutoff=0.75); return m[0] if m else None
llm_mapping = {"Bangalore": "Bengaluru", "bengaluru ": "Bengaluru", "BLR": "Bengaluru", "Bombay": "Mumbai", "mumbai": "Mumbai", "New Delhi": "Delhi", "Dilli": "Delhi",
               "Hyd": "Hyderabad", "Calcutta": "Kolkata", "Poona": "Pune", "Pune-411001": "Pune", "Gurgaon": "OTHER"}       # what an LLM prompt returned
out = pd.DataFrame({"raw": raw, "fuzzy_match": [rule_based(x) for x in raw], "llm": [llm_mapping[x] for x in raw]})
out["llm_valid"] = out.llm.isin(CANONICAL + ["OTHER"])
print(out.to_string(index=False))

emails = ["Hi, order NM-1042 (laptop) arrived with a cracked screen on 3 Oct. I need a replacement urgently - Priya",
          "Payment of Rs 2,499 was debited twice for order NM-2210. Please refund one. Thanks, Arjun"]
extracted = [{"order_id": "NM-1042", "product": "laptop", "issue": "damaged on arrival", "amount_inr": None, "urgency": "high"},
             {"order_id": "NM-2210", "product": None, "issue": "duplicate payment", "amount_inr": 2499, "urgency": "medium"}]   # model output via structured outputs
df = pd.DataFrame(extracted)
df["order_id_valid"] = df.order_id.str.fullmatch(r"NM-\d{4}"); df["order_id_in_text"] = [o in e for o, e in zip(df.order_id, emails)]
print(df.to_string(index=False))

s = sales.assign(year=sales.order_date.dt.year)
k = s.groupby("year").agg(revenue=("sales", "sum"), profit=("profit", "sum"), orders=("order_id", "nunique"))
k["margin"] = 100 * k.profit / k.revenue
facts = {"revenue_2025_cr": round(k.revenue[2025] / 1e7, 2), "revenue_growth_pct": round(100 * (k.revenue[2025] / k.revenue[2024] - 1), 1),
         "margin_2025_pct": round(k.margin[2025], 1), "margin_2024_pct": round(k.margin[2024], 1), "orders_2025": int(k.orders[2025])}
print(json.dumps(facts))
prompt = f"""Write a 3-sentence executive summary for the CFO using ONLY these facts. Do not add any other numbers.
<facts>{json.dumps(facts)}</facts>"""
draft = ("Revenue reached ₹7.84 crore in 2025, up 19.8% on 2024, from 1,232 orders. "
         "Profit margin improved slightly to 9.6% from 9.4%. Growth was broad-based, so the priority is protecting margin as volumes rise.")   # an LLM-style draft
nums_in_draft = set(re.findall(r"\d[\d,]*\.?\d*", draft.replace(",", "")))
allowed = {str(v).replace(",", "") for v in facts.values()} | {"2025", "2024", "3"}
print("numbers not in facts:", sorted(nums_in_draft - allowed) or "none")