From SQL to Pandas: A Translation Guide with FreshBasket Queries

Many analysts arrive at pandas already knowing SQL, and many more will be asked "can you do this in SQL and in Python?" in interviews. The good news is that the two languages express the same ideas: filter rows, choose columns, group and aggregate, join tables, rank within groups. Only the syntax differs. Once you have a mental dictionary that maps each SQL clause to its pandas equivalent, you can move between the two easily and decide which tool suits each job.

This lesson is that dictionary. Every example shows a SQL query, runs it against a real SQLite database built from the FreshBasket files, shows the pandas version, and asserts that the two answers are identical. That is the best way to learn a translation: seeing both produce the same numbers. We cover the whole SQL toolbox: SELECT, WHERE, ORDER BY, GROUP BY, HAVING, joins, CASE WHEN, window functions, subqueries and CTEs, plus when each tool is the better choice.

Worked examples

Example 1: Set up a database

Question: Put the FreshBasket tables into SQLite so we can run SQL.

import sqlite3
import pandas as pd
import numpy as np

orders = pd.read_csv("orders.csv")
items = pd.read_csv("order_items.csv")
products = pd.read_csv("products.csv")
customers = pd.read_csv("customers.csv")
stores = pd.read_csv("stores.csv")

con = sqlite3.connect(":memory:")
for name, t in [("orders", orders), ("order_items", items), ("products", products), ("customers", customers), ("stores", stores)]:
    t.to_sql(name, con, index=False)

def sql(q):
    return pd.read_sql_query(q, con)

print(sql("SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name")["name"].tolist())
print(sql("SELECT COUNT(*) AS n FROM orders").iloc[0, 0])

['customers', 'order_items', 'orders', 'products', 'stores']
6000

How it works: DataFrame.to_sql writes a table into a database (here an in-memory SQLite one) and pd.read_sql_query runs a SQL string and returns a DataFrame. We wrap the second in a helper sql(...). Every example below runs the SQL and the pandas side by side, so you can see they agree.

Reading the result: The database holds 5 tables and 6,000 orders, the same as the DataFrame.

Example 2: SELECT, WHERE, ORDER BY, LIMIT

Question: The five slowest delivered orders from store S01.

q = """
SELECT order_id, delivery_minutes
FROM orders
WHERE store_id = 'S01' AND order_status = 'Delivered'
ORDER BY delivery_minutes DESC, order_id
LIMIT 5
"""
a = sql(q)

b = (
    orders.loc[(orders["store_id"] == "S01") & (orders["order_status"] == "Delivered"), ["order_id", "delivery_minutes"]]
    .sort_values(["delivery_minutes", "order_id"], ascending=[False, True])
    .head(5)
    .reset_index(drop=True)
)
print(a)
pd.testing.assert_frame_equal(a, b)
print("identical")

  order_id  delivery_minutes
0   O00231              45.0
1   O02003              44.0
2   O02698              42.0
3   O02478              41.0
4   O00053              40.0
identical

How it works: Map the clauses one by one: FROM is the DataFrame, WHERE is a boolean mask, SELECT is the column list in .loc[mask, cols], ORDER BY is sort_values (with a list of directions), and LIMIT is head. assert_frame_equal raises an error if the two results differ, so reaching "identical" means they match exactly.

Reading the result: The slowest delivery from S01 took 45 minutes. The structure of the pandas expression follows the SQL clause order almost line by line.

Example 3: DISTINCT, COUNT DISTINCT, IN, BETWEEN, LIKE

Question: Translate the small operators.

a1 = sql("SELECT COUNT(DISTINCT customer_id) AS n FROM orders").iloc[0, 0]
b1 = orders["customer_id"].nunique()

a2 = sql("SELECT COUNT(*) AS n FROM orders WHERE payment_method IN ('UPI', 'Card') AND discount_pct BETWEEN 10 AND 20").iloc[0, 0]
b2 = (orders["payment_method"].isin(["UPI", "Card"]) & orders["discount_pct"].between(10, 20)).sum()

a3 = sql("SELECT COUNT(*) AS n FROM customers WHERE name LIKE 'A%' AND city <> 'Pune'").iloc[0, 0]
b3 = (customers["name"].str.startswith("A") & (customers["city"] != "Pune")).sum()

a4 = sql("SELECT DISTINCT delivery_slot FROM orders ORDER BY 1")["delivery_slot"].tolist()
b4 = sorted(orders["delivery_slot"].unique())

print(a1 == b1, a2 == b2, a3 == b3, a4 == b4)
print(a1, a2, a3, len(a4))

True True True True
588 1751 123 4

How it works: COUNT(DISTINCT x) is nunique(), IN (...) is isin, BETWEEN a AND b is between (inclusive in both), LIKE 'A%' is str.startswith ('%x%' is str.contains), <> is !=, and SELECT DISTINCT is unique() or drop_duplicates().

Reading the result: All four pairs agree (True, True, True, True). There are 588 distinct ordering customers and 4 delivery slots.

Example 4: GROUP BY and aggregates

Question: Orders, average delivery time and delivered share per payment method.

q = """
SELECT payment_method,
       COUNT(*) AS orders,
       ROUND(AVG(delivery_minutes), 2) AS avg_minutes,
       ROUND(100.0 * SUM(order_status = 'Delivered') / COUNT(*), 1) AS delivered_pct
FROM orders
GROUP BY payment_method
ORDER BY orders DESC
"""
a = sql(q)

b = (
    orders.groupby("payment_method")
    .agg(orders=("order_id", "count"),
         avg_minutes=("delivery_minutes", "mean"),
         delivered_pct=("order_status", lambda s: (s == "Delivered").mean() * 100))
    .round({"avg_minutes": 2, "delivered_pct": 1})
    .sort_values("orders", ascending=False)
    .reset_index()
)
print(a)
pd.testing.assert_frame_equal(a, b, check_dtype=False)
print("identical")

  payment_method  orders  avg_minutes  delivered_pct
0            UPI    3330        23.64           89.8
1           Card    1225        23.70           90.3
2            COD     976        23.63           89.2
3         Wallet     469        23.88           90.4
identical

How it works: GROUP BY becomes groupby, and each aggregate becomes a named aggregation new_name=("column", "function"). COUNT(*) is size or count of any non-null column, and AVG skips nulls just as mean skips NaN. A boolean average is the natural way to compute a percentage, replacing SUM(condition) / COUNT(*).

Reading the result: The top method is UPI with 3,330 orders. Both tools give identical figures.

Example 5: HAVING and filtering groups

Question: Stores with more than 600 delivered orders and an average delivery under 24 minutes.

q = """
SELECT store_id, COUNT(*) AS delivered, ROUND(AVG(delivery_minutes), 2) AS avg_minutes
FROM orders
WHERE order_status = 'Delivered'
GROUP BY store_id
HAVING COUNT(*) > 600 AND AVG(delivery_minutes) < 24
ORDER BY store_id
"""
a = sql(q)

g = (
    orders[orders["order_status"] == "Delivered"]
    .groupby("store_id")
    .agg(delivered=("order_id", "count"), avg_minutes=("delivery_minutes", "mean"))
    .round({"avg_minutes": 2})
    .reset_index()
)
b = g[(g["delivered"] > 600) & (g["avg_minutes"] < 24)].reset_index(drop=True)
print(a)
pd.testing.assert_frame_equal(a, b, check_dtype=False)
print(len(a))

  store_id  delivered  avg_minutes
0      S01        601        23.61
1      S07        616        23.46
2

How it works: WHERE filters rows before grouping, so in pandas it is a mask on the original DataFrame. HAVING filters after aggregation, so it is a mask on the grouped result. Keeping this order straight (rows, then groups) is the key to translating correctly.

Reading the result: 2 stores meet both conditions. If you applied the delivery-time test before aggregating, you would be filtering orders rather than stores, which is a different question.

Example 6: JOIN

Question: Revenue of delivered orders per city.

q = """
SELECT s.city, ROUND(SUM(i.quantity * i.unit_price), 0) AS revenue
FROM order_items i
JOIN orders o ON o.order_id = i.order_id
JOIN stores s ON s.store_id = o.store_id
WHERE o.order_status = 'Delivered'
GROUP BY s.city
ORDER BY revenue DESC
"""
a = sql(q)

m = (
    items.merge(orders[["order_id", "store_id", "order_status"]], on="order_id")
    .merge(stores[["store_id", "city"]], on="store_id")
)
m = m[m["order_status"] == "Delivered"]
m["revenue"] = m["quantity"] * m["unit_price"]
b = m.groupby("city", as_index=False)["revenue"].sum().round({"revenue": 0}).sort_values("revenue", ascending=False).reset_index(drop=True)
print(a.head(3))
pd.testing.assert_frame_equal(a, b, check_dtype=False)
print("identical")

        city   revenue
0      Delhi  746553.0
1  Bengaluru  742990.0
2     Mumbai  585420.0
identical

How it works: Each JOIN ... ON is a merge. The default how="inner" matches SQL's JOIN; LEFT JOIN becomes how="left". Calculated expressions inside SUM(...) must be computed as a column first. Finally GROUP BY and ORDER BY follow as before.

Reading the result: Delhi earns the most at ₹746,553. The same answer from two different tools builds confidence in both.

Example 7: CASE WHEN

Question: Count orders by delivery performance band.

q = """
SELECT CASE WHEN delivery_minutes IS NULL THEN 'none'
            WHEN delivery_minutes <= 20 THEN 'fast'
            WHEN delivery_minutes <= 35 THEN 'normal'
            ELSE 'slow' END AS band,
       COUNT(*) AS orders
FROM orders
GROUP BY band
ORDER BY band
"""
a = sql(q)

band = np.select(
    [orders["delivery_minutes"].isna(), orders["delivery_minutes"] <= 20, orders["delivery_minutes"] <= 35],
    ["none", "fast", "normal"],
    default="slow",
)
b = pd.Series(band, name="band").value_counts().rename("orders").rename_axis("band").reset_index().sort_values("band").reset_index(drop=True)
print(a)
pd.testing.assert_frame_equal(a, b, check_dtype=False)
print(a.set_index("band")["orders"].to_dict())

     band  orders
0    fast    1836
1    none     370
2  normal    3514
3    slow     280
{'fast': 1836, 'none': 370, 'normal': 3514, 'slow': 280}

How it works: CASE WHEN with several branches maps to np.select(conditions, choices, default): the first true condition wins, exactly as in SQL, so the order of conditions matters (the NULL test must come first). Grouping by the result is then a normal value_counts or groupby.

Reading the result: The bands split the orders into {'fast': 1836, 'none': 370, 'normal': 3514, 'slow': 280}. For two branches np.where is simpler than np.select.

Example 8: Window functions

Question: Rank each store's days by orders, and add a running total.

q = """
WITH daily AS (
    SELECT store_id, order_date, COUNT(*) AS orders
    FROM orders GROUP BY store_id, order_date
)
SELECT store_id, order_date, orders,
       RANK() OVER (PARTITION BY store_id ORDER BY orders DESC) AS rnk,
       SUM(orders) OVER (PARTITION BY store_id ORDER BY order_date) AS running,
       LAG(orders) OVER (PARTITION BY store_id ORDER BY order_date) AS prev
FROM daily
ORDER BY store_id, order_date
"""
a = sql(q)

d = orders.groupby(["store_id", "order_date"]).size().rename("orders").reset_index().sort_values(["store_id", "order_date"])
d["rnk"] = d.groupby("store_id")["orders"].rank(method="min", ascending=False).astype(int)
d["running"] = d.groupby("store_id")["orders"].cumsum()
d["prev"] = d.groupby("store_id")["orders"].shift(1)
b = d.reset_index(drop=True)

pd.testing.assert_frame_equal(a, b, check_dtype=False)
print(a.head(3).to_string(index=False))
print(a[a["rnk"] == 1].shape[0] >= a["store_id"].nunique())

store_id order_date  orders  rnk  running  prev
     S01 2025-01-03       1  184        1   NaN
     S01 2025-01-05       3   33        4   1.0
     S01 2025-01-06       1  184        5   3.0
True

How it works: PARTITION BY is groupby, ORDER BY inside the window is a prior sort_values, RANK() is rank(method="min"), SUM() OVER (... ORDER BY) is cumsum, and LAG is shift. ROW_NUMBER() is rank(method="first") and DENSE_RANK() is rank(method="dense"). The CTE (WITH daily AS) is simply the intermediate DataFrame d.

Reading the result: Both versions produce 3,385 store-day rows. Ties share a rank in RANK(), so a store can have more than one rank-1 day.

Example 9: Subqueries and CTEs

Question: Customers who ordered more than the average customer.

q = """
WITH per_customer AS (
    SELECT customer_id, COUNT(*) AS n FROM orders GROUP BY customer_id
)
SELECT customer_id, n
FROM per_customer
WHERE n > (SELECT AVG(n) FROM per_customer)
ORDER BY n DESC, customer_id
LIMIT 5
"""
a = sql(q)

pc = orders.groupby("customer_id").size().rename("n").reset_index()
b = pc[pc["n"] > pc["n"].mean()].sort_values(["n", "customer_id"], ascending=[False, True]).head(5).reset_index(drop=True)

pd.testing.assert_frame_equal(a, b, check_dtype=False)
print(a)
print(round(pc["n"].mean(), 2), (pc["n"] > pc["n"].mean()).sum())

  customer_id    n
0       C0406  232
1       C0532  202
2       C0122  125
3       C0584   99
4       C0051   79
10.2 138

How it works: A subquery that returns a single number (SELECT AVG(n) ...) becomes a Python scalar (pc["n"].mean()) used in a mask. A CTE becomes a named variable. Writing intermediate results to variables is the pandas advantage: you can inspect each step.

Reading the result: The average customer placed 10.20 orders, and 138 customers placed more than that. The top customer placed 232.

Example 10: Anti-join with NOT EXISTS, and UNION

Question: Products never ordered in 2026, and the combination of two lists.

q = """
SELECT p.product_id
FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM order_items i JOIN orders o ON o.order_id = i.order_id
    WHERE i.product_id = p.product_id AND o.order_date >= '2026-01-01'
)
ORDER BY p.product_id
"""
a = sql(q)

recent_orders = orders.loc[orders["order_date"] >= "2026-01-01", "order_id"]
sold_recent = items.loc[items["order_id"].isin(recent_orders), "product_id"].unique()
b = products.loc[~products["product_id"].isin(sold_recent), ["product_id"]].sort_values("product_id").reset_index(drop=True)
pd.testing.assert_frame_equal(a, b, check_dtype=False)
print(len(a), len(products))

u = sql("SELECT city FROM customers UNION SELECT city FROM stores ORDER BY city")
v = pd.Series(sorted(set(customers["city"]) | set(stores["city"])), name="city")
pd.testing.assert_frame_equal(u, v.to_frame(), check_dtype=False)
print(len(u))

0 50
8

How it works: NOT EXISTS is an anti-join: build the set of matching keys and keep the rows whose key is not in it (~isin). UNION (which removes duplicates) is pd.concat(...).drop_duplicates() or a set union; UNION ALL is plain concat.

Reading the result: 0 of the 50 products had no sales in 2026, and the union of customer and store cities has 8 distinct cities.

When to use which

SQL is the better tool when the data is large, already lives in a database, or needs to be shared as a view. Pandas is the better tool for exploration, complex reshaping, statistics, visualisation and anything that needs Python's ecosystem. Many analysts combine them: do the heavy filtering and joining in SQL, pull the result into pandas for analysis and charts.

Common mistakes

1. Treating HAVING like WHERE (filtering the wrong stage).

2. Forgetting that SQL ignores NULLs in aggregates and pandas does too, but count(*) versus count(col) differ.

3. Comparing results without sorting both sides, since neither engine guarantees order without ORDER BY or sort_values.

4. Joining on duplicate keys in either tool and inflating totals.

5. Writing a SQL query with a typo in a table name.

sql("SELECT * FROM order")

Traceback (most recent call last):
  File "example.py", line 2745, in execute
    cur.execute(sql, *args)
    ~~~~~~~~~~~^^^^^^^^^^^^
sqlite3.OperationalError: near "order": syntax error

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "example.py", line 1, in <module>
    sql("SELECT * FROM order")
    ~~~^^^^^^^^^^^^^^^^^^^^^^^
  File "example.py", line 16, in sql
    return pd.read_sql_query(q, con)
           ~~~~~~~~~~~~~~~~~^^^^^^^^
  File "example.py", line 497, in read_sql_query
    return pandas_sql.read_query(
           ~~~~~~~~~~~~~~~~~~~~~^
        sql,
        ^^^^
    ...<6 lines>...
        dtype_backend=dtype_backend,
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "example.py", line 2809, in read_query
    cursor = self.execute(sql, params)
  File "example.py", line 2757, in execute
    raise ex from exc
pandas.errors.DatabaseError: Execution failed on sql 'SELECT * FROM order': near "order": syntax error

Practice questions

Practice 1 (Easy): Translate a filter

Write the pandas version of: SELECT COUNT(*) FROM orders WHERE order_status = 'Returned' AND discount_pct >= 15.

a = sql("SELECT COUNT(*) AS n FROM orders WHERE order_status = 'Returned' AND discount_pct >= 15").iloc[0, 0]
b = ((orders["order_status"] == "Returned") & (orders["discount_pct"] >= 15)).sum()
print(a, b, a == b)

54 54 True

Practice 2 (Medium): Translate a grouped query

Write the pandas version of: average delivery minutes by city for delivered orders, only cities above 23.5, highest first.

q = """SELECT s.city, ROUND(AVG(o.delivery_minutes), 2) AS avg_minutes
FROM orders o JOIN stores s ON s.store_id = o.store_id
WHERE o.order_status = 'Delivered'
GROUP BY s.city HAVING AVG(o.delivery_minutes) > 23.5
ORDER BY avg_minutes DESC, s.city"""
a = sql(q)

m = orders[orders["order_status"] == "Delivered"].merge(stores[["store_id", "city"]], on="store_id")
g = m.groupby("city", as_index=False)["delivery_minutes"].mean().rename(columns={"delivery_minutes": "avg_minutes"})
b = g[g["avg_minutes"] > 23.5].round({"avg_minutes": 2}).sort_values(["avg_minutes", "city"], ascending=[False, True]).reset_index(drop=True)
pd.testing.assert_frame_equal(a, b, check_dtype=False)
print(a)

        city  avg_minutes
0  Ahmedabad        24.00
1  Bengaluru        23.81
2      Delhi        23.81
3       Pune        23.80
4    Kolkata        23.68
5     Mumbai        23.58

Practice 3 (Hard): Top 2 products per category (window function)

Write SQL using ROW_NUMBER() OVER (PARTITION BY category ORDER BY units DESC, product_name) to get the top two products per category by units sold, and reproduce it in pandas.

q = """
WITH u AS (
    SELECT p.category, p.product_name, SUM(i.quantity) AS units
    FROM order_items i JOIN products p ON p.product_id = i.product_id
    GROUP BY p.category, p.product_name
), r AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY units DESC, product_name) AS rn FROM u
)
SELECT category, product_name, units FROM r WHERE rn <= 2 ORDER BY category, rn
"""
a = sql(q)

u = items.merge(products[["product_id", "category", "product_name"]], on="product_id").groupby(["category", "product_name"], as_index=False)["quantity"].sum().rename(columns={"quantity": "units"})
u = u.sort_values(["category", "units", "product_name"], ascending=[True, False, True])
b = u.groupby("category").head(2).reset_index(drop=True)
pd.testing.assert_frame_equal(a, b, check_dtype=False)
print(a.head(4).to_string(index=False))

 category        product_name  units
Beverages Coconut Water 200ml    973
Beverages          Cola 750ml    513
    Dairy         Paneer 200g   2540
    Dairy   Greek Yogurt 100g   1421

Key takeaways

• Every SQL clause has a pandas counterpart: mask, .loc, sort_values, head, groupby, merge, np.select, rank.
• WHERE filters rows before grouping; HAVING filters after.
• CTEs and subqueries become intermediate variables and scalars.
• Window functions become groupby plus rank, cumsum, shift.
• Prove translations correct by comparing both results with assert_frame_equal.
• Use SQL for heavy data extraction and pandas for exploration, reshaping and visualisation.