SQL vs Pandas vs AI Brokers: Which Solves Analytics Issues Greatest?

# Introduction

 
We gave the identical three interview questions from StrataScratch to SQL, Pandas, and a Claude agent. Every bit of code executed towards the identical dataset, and each timing quantity is a median over 500 runs. The agent’s solutions are precisely what Claude generated in response to a documented immediate, as an alternative of a hypothetical instance of what an agent would possibly produce.

The comparability runs throughout eight dimensions: velocity, accuracy, explainability, debugging, scalability, flexibility, hallucination danger, and manufacturing readiness. The three questions span Simple, Medium, and Arduous issue ranges. The more durable the query, the extra the variations between SQL, Pandas, and the agent develop into seen.

# How We Ran This Comparability

 
The three questions come from the StrataScratch interview financial institution and canopy Simple, Medium, and Arduous issue ranges. SQL ran on SQLite in-memory, timed over 500 runs, with the median taken. Pandas ran on the identical dataset in Python 3.12, additionally over 500 runs. The agent is Claude’s claude-sonnet-4-6, referred to as by way of the Anthropic API.

Every query acquired its personal schema-grounded consumer immediate that included the desk names, column names, and some pattern rows. The system immediate beneath stayed the identical for all three calls. Agent response occasions are measured from the time the request is distributed to the primary token acquired.

# Easy Retrieval: All Three Agree

 
For the first interview query from Meta, customers are requested to seek out each consumer who carried out at the least one scroll_up occasion and return the distinct consumer IDs. The info lives in a single desk referred to as facebook_web_log.

// Information

Here is the facebook_web_log desk.

// SQL Coding Resolution (0.002 ms)

SELECT DISTINCT user_id
FROM facebook_web_log
WHERE motion = 'scroll_up';

// Pandas Coding Resolution (0.40 ms)

import pandas as pd
outcome = (
    facebook_web_log[facebook_web_log['action'] == 'scroll_up']
    .drop_duplicates(subset="user_id")[['user_id']]
)

// Agent Immediate

Desk: facebook_web_log (user_id INTEGER, motion TEXT, timestamp TEXT)
Pattern rows:
(1, 'scroll_up',   '2019-01-01 00:00:00')
(2, 'scroll_down', '2019-01-01 00:01:00')
(3, 'like',        '2019-01-01 00:03:00')
(2, 'scroll_up',   '2019-01-01 00:04:00')
Query: Discover all customers who carried out at the least one scroll_up occasion.
Return distinct consumer IDs.

// Agent Output (2 s)

SELECT DISTINCT user_id
FROM facebook_web_log
WHERE motion = 'scroll_up';

Output: All three return customers 1 and a pair of.

On a single-filter drawback, the agent matches SQL precisely. The one actual danger at this issue is column naming. With out the schema within the immediate, motion would possibly come again as event_type or event_name, which returns nothing and throws no error.

# Multi-Step Aggregation: The place Schema Grounding Issues Most

 
The second query is about product characteristic completion. An app tracks how far every consumer will get by means of a set of product options, the place each characteristic has a set variety of steps.

The duty is to calculate the typical completion proportion for every characteristic throughout all customers, the place a consumer’s completion is their most step reached divided by the whole steps for that characteristic, occasions 100. Customers who’ve by no means began a characteristic are counted as 0% full.

Two tables feed this: facebook_product_features:

and facebook_product_features_realizations.

// SQL Coding Resolution (0.007 ms)

WITH max_step AS (
    SELECT
        feature_id,
        user_id,
        MAX(step_reached) AS max_step_reached
    FROM facebook_product_features_realizations
    GROUP BY feature_id, user_id
),
calc_per_feature AS (
    SELECT
        feats.feature_id,
        n_steps,
        max_step_reached,
        COALESCE(max_step_reached, 0) * 1.0 / n_steps AS share_of_completion
    FROM facebook_product_features feats
    LEFT OUTER JOIN max_step
        ON feats.feature_id = max_step.feature_id
)
SELECT
    feature_id,
    AVG(share_of_completion) * 100 AS avg_share_of_completion
FROM calc_per_feature
GROUP BY feature_id;

// Pandas Coding Resolution (2.05 ms)

import pandas as pd
# max step per consumer per characteristic
max_step = (
    facebook_product_features_realizations
    .groupby(['feature_id', 'user_id'])['step_reached']
    .max()
    .reset_index()
)

# be part of to options, fill customers who by no means began with 0
df = pd.merge(
    facebook_product_features,
    max_step,
    how='outer',
    on='feature_id'
).fillna(0)

# completion % per user-feature row
df['share_of_completion'] = (df['step_reached'] / df['n_steps']) * 100

# common per characteristic
outcome = (
    df.groupby('feature_id')['share_of_completion']
    .imply()
    .to_frame('avg_share_of_completion')
    .reset_index()
)

// Agent Immediate

Tables:
- facebook_product_features (feature_id INTEGER, n_steps INTEGER)
- facebook_product_features_realizations
  (feature_id INTEGER, user_id INTEGER, step_reached INTEGER, timestamp TEXT)
Query: For every characteristic, calculate the typical completion proportion
throughout all customers. A consumer's completion = max(step_reached) / n_steps * 100.
Customers who by no means began depend as 0% completion.
Return: feature_id, avg_share_of_completion.

// Agent Output (3 s)

WITH user_max_steps AS (
    SELECT feature_id, user_id, MAX(step_reached) AS max_step
    FROM facebook_product_features_realizations
    GROUP BY feature_id, user_id
)
SELECT
    f.feature_id,
    ROUND(AVG(COALESCE(ums.max_step, 0) * 100.0 / f.n_steps), 2) AS avg_share_of_completion
FROM facebook_product_features f
LEFT JOIN user_max_steps ums ON f.feature_id = ums.feature_id
GROUP BY f.feature_id
ORDER BY f.feature_id;

Output: All three return the identical numbers.

The agent acquired it proper right here as a result of the immediate stated: “Customers who by no means began depend as 0% completion.” That phrase is load-bearing. With out it, the agent writes an internal be part of — which drops non-starters — and each common goes up. That failure is silent. The numbers come again clear, and so they’re unsuitable. You’d have to know the anticipated output to catch it.

# A number of Tables and Window Logic: All Three Appropriate, One A lot Slower

 
The third query covers Meta’s knowledge middle power consumption throughout three areas. Every area has its personal desk: fb_eu_energy, fb_na_energy, and fb_asia_energy.

The duty is to mix them, sum consumption by date, and produce two derived columns: the cumulative operating complete and that complete as a proportion of the grand complete, rounded to a complete quantity.

// Information

Every regional desk has the identical form.

fb_eu_energy:

fb_na_energy:

fb_asia_energy:

// SQL Coding Resolution (0.010 ms)

WITH total_energy AS (
    SELECT recorded_date, consumption FROM fb_eu_energy
    UNION ALL
    SELECT recorded_date, consumption FROM fb_asia_energy
    UNION ALL
    SELECT recorded_date, consumption FROM fb_na_energy
),
energy_by_date AS (
    SELECT
        recorded_date,
        SUM(consumption) AS total_energy
    FROM total_energy
    GROUP BY recorded_date
    ORDER BY recorded_date ASC
)
SELECT
    recorded_date,
    SUM(total_energy) OVER (
        ORDER BY recorded_date ASC
    ) AS cumulative_total_energy,
    ROUND(
        SUM(total_energy) OVER (ORDER BY recorded_date ASC) * 100.0
        / (SELECT SUM(total_energy) FROM energy_by_date),
        0
    ) AS percentage_of_total_energy
FROM energy_by_date;

// Pandas Coding Resolution (1.84 ms)

import pandas as pd
merged_df = pd.concat([fb_eu_energy, fb_asia_energy, fb_na_energy])

energy_by_date = (
    merged_df.groupby('recorded_date', as_index=False)['consumption']
    .sum()
    .sort_values('recorded_date')
)

energy_by_date['cumulative_total_energy'] = energy_by_date['consumption'].cumsum()
energy_by_date['percentage_of_total_energy'] = spherical(
    energy_by_date['cumulative_total_energy'] * 100 / energy_by_date['consumption'].sum()
)

energy_by_date['recorded_date'] = pd.to_datetime(
    energy_by_date['recorded_date']
).dt.strftime('%Y-%m-%d')

outcome = energy_by_date[
    ['recorded_date', 'cumulative_total_energy', 'percentage_of_total_energy']
]

// Agent Immediate

Tables:
- fb_eu_energy   (recorded_date TEXT, consumption INTEGER)
- fb_na_energy   (recorded_date TEXT, consumption INTEGER)
- fb_asia_energy (recorded_date TEXT, consumption INTEGER)
Query: Mix all three tables. Sum consumption by date. Output:
- recorded_date
- cumulative_total_energy (operating sum throughout dates, ordered by date)
- percentage_of_total_energy (operating cumulative / grand complete * 100,
  rounded to the closest complete quantity)

// Agent Output (4 s)

WITH all_energy AS (
    SELECT recorded_date, consumption FROM fb_eu_energy
    UNION ALL
    SELECT recorded_date, consumption FROM fb_na_energy
    UNION ALL
    SELECT recorded_date, consumption FROM fb_asia_energy
),
daily_totals AS (
    SELECT recorded_date, SUM(consumption) AS daily_total
    FROM all_energy
    GROUP BY recorded_date
)
SELECT
    recorded_date,
    SUM(daily_total) OVER (ORDER BY recorded_date) AS cumulative_total_energy,
    ROUND(
        SUM(daily_total) OVER (ORDER BY recorded_date) * 100.0
        / SUM(daily_total) OVER (),
        0
    ) AS percentage_of_total_energy
FROM daily_totals
ORDER BY recorded_date;

Output: All three return the identical desk.

The agent used SUM(daily_total) OVER () (a window perform with no ORDER BY) because the denominator somewhat than the scalar subquery within the SQL reference resolution. Each approaches are legitimate. The output matched precisely.

# How the Three Evaluate

// Pace

At this knowledge scale, SQL ran in 0.002-0.010 ms, Pandas in 0.4-2.1 ms. The agent added 2-4 seconds of enormous language mannequin (LLM) inference time earlier than any SQL ran.

The agent generates code first; that technology time is the end-to-end latency for every question cycle. At warehouse scale, the hole closes to close zero as soon as code is generated; SQL features additional as a result of it runs contained in the database engine, and Pandas hits a reminiscence ceiling round 10 million rows and desires Apache Spark or Polars past that.

// Accuracy and Hallucination Danger

SQL and Pandas are deterministic. The identical code on the identical knowledge provides the identical reply each time. With schema-grounded prompts, Claude acquired all three questions proper, however every name produced completely different SQL (completely different widespread desk expression (CTE) names, completely different column aliases, completely different however equal approaches). With out the schema, hallucination danger climbs quick.

// Explainability and Debugging

A SQL question reads in a single block. A nasty be part of situation is seen proper within the textual content. Pandas wants Python fluency, however you’ll be able to examine the DataFrame at every step. Brokers clarify their reasoning in English, then produce code that you could be or might not be proven. If the generated SQL is unsuitable, you are tracing an error by means of a mannequin’s reasoning chain somewhat than studying a question you wrote.

// Flexibility and Manufacturing Readiness

Pandas is the clearest possibility for customized transformations, string parsing, and iterative characteristic engineering. SQL handles set logic cleanly and will get verbose for procedural work. Brokers reply plain-English requests effectively, with the least consistency when schemas are complicated or ambiguous. For transport, SQL is probably the most confirmed possibility in analytics; Pandas is reliable with assessments; and brokers are reliable at present for low-stakes queries or when the output is reviewed earlier than it runs.

# What the Agent Outcomes Really Present

 
With a schema-grounded immediate, Claude acquired all three right: Simple, Medium, and Arduous. The agent’s SQL for the Arduous query used a distinct window perform sample than the reference resolution and nonetheless returned the proper desk.

Two issues restrict that discovering. First, reproducibility: every API name can return completely different SQL for a similar query. The logic is equal, however a group reviewing agent-generated queries must confirm the outputs somewhat than belief that at present’s right run will match tomorrow’s. Second, schema dependency: the prompts above embody desk and column names, in addition to pattern rows. Take away these, and the agent guesses.

At Simple issue, a unsuitable guess produces an empty outcome. At Arduous issue, a unsuitable guess produces a believable unsuitable outcome with no error.

The sensible sample is: present the complete schema, ask for SQL, then run and confirm the output earlier than it goes downstream.

# Conclusion

 
SQL, Pandas, and Claude every acquired the identical three analytics questions proper when used appropriately. The variations are in velocity (0.01 ms vs 4 seconds), reproducibility, and what occurs whenever you cut back the context.

SQL suits structured retrieval and set-based logic with millisecond execution and deterministic output. Pandas suits customized transformations and step-by-step pocket book work as much as about 10 million rows. The agent suits first-draft queries and advert hoc exploration, with the complete schema within the immediate and a human reviewing the output.

The agent acquired the Arduous query proper by utilizing SUM() OVER() as an alternative of a scalar subquery, which is a legitimate strategy that SQL’s reference resolution did not take. That is the trustworthy model of this comparability: the agent can generate right, artistic SQL. It simply provides latency, varies between runs, and relies upon completely on what you set within the immediate.
 
 

Nate Rosidi is an information scientist and in product technique. He is additionally an adjunct professor educating analytics, and is the founding father of StrataScratch, a platform serving to knowledge scientists put together for his or her interviews with actual interview questions from prime corporations. Nate writes on the most recent developments within the profession market, provides interview recommendation, shares knowledge science initiatives, and covers all the pieces SQL.