SQL vs Pandas vs AI Agents: Which Solves Analytics Problems Best?

--> SQL vs Pandas vs AI Agents: Which Solves Analytics Problems Best? - KDnuggets

-->

Join Newsletter

Introduction

We gave the same three interview questions from StrataScratch to SQL, Pandas, and a Claude agent. Every piece of code executed against the same dataset, and every timing number is a median over 500 runs. The agent's answers are exactly what Claude generated in response to a documented prompt, instead of a hypothetical example of what an agent might produce.

The comparison runs across eight dimensions: speed, accuracy, explainability, debugging, scalability, flexibility, hallucination risk, and production readiness. The three questions span Easy, Medium, and Hard difficulty levels. The harder the question, the more the differences between SQL, Pandas, and the agent become visible.

How We Ran This Comparison

The three questions come from the StrataScratch interview bank and cover Easy, Medium, and Hard difficulty levels. SQL ran on SQLite in-memory, timed over 500 runs, with the median taken. Pandas ran on the same dataset in Python 3.12, also over 500 runs. The agent is Claude's claude-sonnet-4-6, called via the Anthropic API.

Each question got its own schema-grounded user prompt that included the table names, column names, and a few sample rows. The system prompt below stayed the same for all three calls. Agent response times are measured from the time the request is sent to the first token received.

Simple Retrieval: All Three Agree

For the first interview question from Meta, users are asked to find every user who performed at least one scroll_up event and return the distinct user IDs. The data lives in a single table called facebook_web_log.

// Data

Here's the facebook_web_log table.

user_id timestamp action

0 2019-04-25 13:30:15 page_load

0 2019-04-25 13:30:18 page_load

0 2019-04-25 13:30:40 scroll_down

0 2019-04-25 13:30:45 scroll_up

… … …

0 2019-04-25 13:30:40 page_exit

// SQL Coding Solution (0.002 ms)

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

// Pandas Coding Solution (0.40 ms)

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

// Agent Prompt

Table: facebook_web_log (user_id INTEGER, action TEXT, timestamp TEXT) Sample 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') Question: Find all users who performed at least one scroll_up event. Return distinct user IDs.

// Agent Output (2 s)

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

Output: All three return users 1 and 2.

user_id

0

2

1

On a single-filter problem, the agent matches SQL exactly. The only real risk at this difficulty is column naming. Without the schema in the prompt, action might come back as event_type or event_name, which returns nothing and throws no error.

Multi-Step Aggregation: Where Schema Grounding Matters Most

The second question is about product feature completion. An app tracks how far each user gets through a set of product features, where every feature has a fixed number of steps.

The task is to calculate the average completion percentage for each feature across all users, where a user's completion is their maximum step reached divided by the total steps for that feature, times 100. Users who have never started a feature are counted as 0% complete.

Two tables feed this: facebook_product_features:

feature_id n_steps

0 5

1 7

2 3

and facebook_product_features_realizations.

feature_id user_id step_reached timestamp

0 0 1 2019-03-11 17:15:00

0 0 2 2019-03-11 17:22:00

0 0 3 2019-03-11 17:25:00

0 0 4 2019-03-11 17:27:00

... ... ... ...

1 1 3 2019-04-05 13:00:07

// SQL Coding Solution (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 Solution (2.05 ms)

import pandas as pd

max step per user per feature

max_step = ( facebook_product_features_realizations .groupby(['feature_id', 'user_id'])['step_reached'] .max() .reset_index() )

join to features, fill users who never started 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

average per feature

result = ( df.groupby('feature_id')['share_of_completion'] .mean() .to_frame('avg_share_of_completion') .reset_index() )

// Agent Prompt

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) Question: For each feature, calculate the average completion percentage across all users. A user's completion = max(step_reached) / n_steps * 100. Users who never started count 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 same numbers.

feature_id avg_share_of_completion

0 80

2 0

1 76.19

The agent got it right here because the prompt said: "Users who never started count as 0% completion." That phrase is load-bearing. Without it, the agent writes an inner join — which drops non-starters — and every average goes up. That failure is silent. The numbers come back clean, and they're wrong. You'd need to know the expected output to catch it.

Multiple Tables and Window Logic: All Three Correct, One Much Slower

The third question covers Meta's data center energy consumption across three regions. Each region has its own table: fb_eu_energy, fb_na_energy, and fb_asia_energy.

The task is to combine them, sum consumption by date, and produce two derived columns: the cumulative running total and that total as a percentage of the grand total, rounded to a whole number.

// Data

Each regional table has the same shape.

fb_eu_energy:

recorded_date consumption

2020-01-01 400

2020-01-02 350

2020-01-03 500

2020-01-04 500

2020-01-07 600

fb_na_energy:

recorded_date consumption

2020-01-01 250

2020-01-02 375

2020-01-03 600

2020-01-06 500

2020-01-07 250

fb_asia_energy:

recorded_date consumption

2020-01-01 400

2020-01-02 400

2020-01-04 675

2020-01-05 1200

2020-01-06 750

2020-01-07 400

// SQL Coding Solution (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 Solution (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'] = round( 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')

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

// Agent Prompt

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)

Question: Combine all three tables. Sum consumption by date. Output:

• recorded_date
• cumulative_total_energy (running sum across dates, ordered by date)
• percentage_of_total_energy (running cumulative / grand total * 100,

rounded to the nearest whole number)

// 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 same table.

recorded_date cumulative_total_energy percentage_of_total_energy

2020-01-01 1050 13

2020-01-02 2175 27

2020-01-03 3275 40

2020-01-04 4450 55

2020-01-05 5650 69

2020-01-06 6900 85

2020-01-07 8150 100

The agent used SUM(daily_total) OVER () (a window function with no ORDER BY) as the denominator rather than the scalar subquery in the SQL reference solution. Both approaches are valid. The output matched exactly.

How the Three Compare

// Speed

At this data scale, SQL ran in 0.002-0.010 ms, Pandas in 0.4-2.1 ms. The agent added 2-4 seconds of large language model (LLM) inference time before any SQL ran.

The agent generates code first; that generation time is the end-to-end latency for each query cycle. At warehouse scale, the gap closes to near zero once code is generated; SQL gains further because it runs inside the database engine, and Pandas hits a memory ceiling around 10 million rows and needs Apache Spark or Polars beyond that.

// Accuracy and Hallucination Risk

SQL and Pandas are deterministic. The same code on the same data gives the same answer every time. With schema-grounded prompts, Claude got all three questions right, but each call produced different SQL (different common table expression (CTE) names, different column aliases, different but equivalent approaches). Without the schema, hallucination risk climbs fast.

// Explainability and Debugging

A SQL query reads in one block. A bad join condition is visible right in the text. Pandas needs Python fluency, but you can inspect the DataFrame at each step. Agents explain their reasoning in English, then produce code that you may or may not be shown. If the generated SQL is wrong, you're tracing an error through a model's reasoning chain rather than reading a query you wrote.

// Flexibility and Production Readiness

Pandas is the clearest option for custom transformations, string parsing, and iterative feature engineering. SQL handles set logic cleanly and gets verbose for procedural work. Agents answer plain-English requests well, with the least consistency when schemas are complex or ambiguous. For shipping

[truncated for AI cost control]