SQL, Pandas, and AI: Who Really Excels in Analytics?

SQL, Pandas, and AI: Who Truly Excels in Analytics?

Data analysis is a constantly evolving field, and choosing the right tool for the job can be crucial. To evaluate the effectiveness of different tools, a study was conducted by asking the same three interview questions from StrataScratch to three major players: SQL, Pandas, and an AI agent named Claude. Each solution was tested on the same dataset, and execution times were calculated as the median of 500 runs. Claude's responses were generated in response to a specific prompt, ensuring a fair comparison.

This study assessed the tools across eight essential dimensions: speed, accuracy, explainability, debugging, scalability, flexibility, risk of hallucination, and production readiness. The questions posed varied in difficulty, ranging from easy to hard, allowing for the differences between SQL, Pandas, and the AI agent to be highlighted.

Study Methodology

The questions used for this comparison came from the StrataScratch interview bank, covering three levels of difficulty: easy, medium, and hard. SQL was executed on SQLite in memory, with time measurements taken over 500 runs to obtain a median. Pandas was used with Python 3.12 on the same dataset, also measured over 500 runs. The AI agent, Claude's claude-sonnet-4-6, was queried via the Anthropic API.

For each question, a specific user prompt was created, including table names, column names, and some example rows. The system prompt remained constant across the three calls, and the agent's response times were measured from the request submission to the receipt of the first token.

Simple Retrieval: A Consensus Among Tools

The first interview question, posed by Meta, asked to find each user who performed at least one scroll_up event and return the distinct user IDs. The data was contained in a single table, facebook_web_log.

Here’s a glimpse of the facebook_web_log table:

2019-04-25 13:30:15
2019-04-25 13:30:18
2019-04-25 13:30:40
2019-04-25 13:30:45
2019-04-25 13:30:40

• SQL Solution (0.002 ms)

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

• Pandas Solution (0.40 ms)

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

Table: facebook_web_log (user_id INTEGER, action TEXT, timestamp TEXT)

(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 the distinct user IDs.

• Agent Output (2 s)

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

Output: All three tools returned users 1 and 2.

For this simple filtering problem, the AI agent produced an identical solution to that of SQL. The main risk at this level of difficulty lies in the naming of columns. Without the schema in the prompt, naming errors could occur, leading to incorrect results without generating an explicit error.

Multi-Step Aggregation: The Importance of Anchoring in the Schema

The second question focused on the completion of product features. An application tracked each user's progress across a set of features, each feature having a fixed number of steps. The task was to calculate the average completion percentage for each feature among all users, considering users who never started a feature, counted as 0% completion.

Two tables were used: facebook_product_features and facebook_product_features_realizations.

2019-03-11 17:15:00
2019-03-11 17:22:00
2019-03-11 17:25:00
2019-03-11 17:27:00
2019-04-05 13:00:07

• SQL 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,
           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 AVG(share_of_completion) * 100 AS avg_share_of_completion
FROM calc_per_feature
GROUP BY feature_id;

• Pandas Solution (2.05 ms)

import pandas as pd
# max step per user per feature
facebook_product_features_realizations.groupby(['feature_id', 'user_id'])['step_reached']
# join with features, fill users who never started with 0
facebook_product_features,
# completion percentage per user-feature row
df['share_of_completion'] = (df['step_reached'] / df['n_steps']) * 100
# average per feature
df.groupby('feature_id')['share_of_completion'].to_frame('avg_share_of_completion')

• 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 among all users. User 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 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 tools produced the same results.

The AI agent succeeded in this task thanks to a key phrase in the prompt: "Users who never started count as 0% completion." Without this precision, the agent might have generated an inner join, excluding non-starters and thus skewing the averages. This error is silent, as the numbers may seem correct but are actually inaccurate.

Multiple Tables and Window Logic: A Performance Challenge

The third question focused on the energy consumption of Meta's data centers spread across three regions. Each region had its own table: fb_eu_energy, fb_na_energy, and fb_asia_energy. The task was to combine these tables, sum the consumption by date, and produce two derived columns: the cumulative total and this total as a percentage of the overall total, rounded to the nearest integer.

Each regional table had the same structure.

• SQL 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 total_energy,
       SUM(total_energy) OVER (ORDER BY recorded_date ASC) AS cumulative_total_energy,
       SUM(total_energy) OVER (ORDER BY recorded_date ASC) * 100.0 / (SELECT SUM(total_energy) FROM energy_by_date) AS percentage_of_total_energy
FROM energy_by_date;

• Pandas 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']]

• 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 the consumption by date. Output:

• cumulative_total_energy (cumulative sum across dates, ordered by date)

• percentage_of_total_energy (cumulative / overall total * 100, rounded to the nearest integer)

• 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 daily_total,
       SUM(daily_total) OVER (ORDER BY recorded_date) AS cumulative_total_energy,
       SUM(daily_total) OVER (ORDER BY recorded_date) * 100.0 / SUM(daily_total) OVER () AS percentage_of_total_energy
FROM daily_totals
ORDER BY recorded_date;

Output: All three tools produced the same table.

The AI agent used SUM(daily_total) OVER () (a window function without ORDER BY) as the denominator, unlike the scalar subquery in the reference SQL solution. Both approaches are valid, and the output was consistent.

Performance Comparison

At this data scale, SQL operated in 0.002-0.010 ms, Pandas in 0.4-2.1 ms, while the AI agent added 2-4 seconds of inference time before the SQL was executed.

The agent first generates the code, and this generation time represents the end-to-end latency for each query cycle. At the scale of a warehouse, the gap narrows once the code is generated; SQL remains faster as it executes within the database engine, while Pandas hits a memory ceiling around 10 million rows, requiring Apache Spark or Polars beyond this limit.

Accuracy and Risk of Hallucination

SQL and Pandas are deterministic tools. The same code on the same data always produces the same answer. With prompts anchored in the schema, Claude responded well to the three questions, but each call generated different SQL (different CTE names, different column aliases, different but equivalent approaches). Without the schema, the risk of hallucination increases rapidly.

Explainability and Debugging

A SQL query reads as a single block. A bad join condition is directly visible in the text. Pandas requires some mastery of Python, but you can inspect the DataFrame at each step. Agents explain their reasoning in English, then produce code that you may or may not see. If the generated SQL is incorrect, you trace an error through a model's reasoning chain rather than reading a query you wrote.

Flexibility and Production Readiness

Pandas is the cle...