Python and SQL for data analysis: the same questions answered both ways

SQL and Python solve overlapping problems. SQL is the language for asking questions of data stored in a database. Python, with the pandas library, is a general tool for loading, reshaping and analysing data that also does charts, statistics and automation. Most analysts use both. This guide answers the same questions in each, against a 30-day sample (columns day, ad_spend, website_orders, app_orders, avg_basket). Each snippet was run on that data and returns the value shown. In SQL it works as written in DuckDB, and the simpler ones also in SQLite.

Setup

import pandas as pd

df = pd.read_csv("daily.csv")

print(df.head())
print(df.shape)   # (30, 5)

For SQL, load the same CSV into a table called daily. DuckDB can query the file directly with SELECT * FROM "daily.csv", and SQLite can import it with .import. The queries below assume a table called daily.

1. How many orders in total?

SELECT SUM(website_orders + app_orders) AS total_orders
FROM daily;

import pandas as pd

df = pd.read_csv("daily.csv")

df["orders"] = df["website_orders"] + df["app_orders"]
total_orders = df["orders"].sum()
print(total_orders)

Both return 1,772. In SQL you describe the result you want and the database works out how; in pandas you transform a table step by step.

2. What share of orders come from the app?

SELECT ROUND(100.0 * SUM(app_orders) / SUM(website_orders + app_orders), 2) AS app_share_pct
FROM daily;

import pandas as pd

df = pd.read_csv("daily.csv")

app_share_pct = 100 * df["app_orders"].sum() / (df["website_orders"] + df["app_orders"]).sum()
print(round(app_share_pct, 2))

44.58%. Note the 100.0 in the SQL: dividing two integers in some databases drops the decimals, so make one side a decimal.

3. Group by week

SELECT SUM(website_orders) AS week_3_orders
FROM daily
WHERE day BETWEEN 15 AND 21;

import pandas as pd

df = pd.read_csv("daily.csv")

df["week"] = (df["day"] - 1) // 7 + 1
weekly = df.groupby("week")["website_orders"].sum()
week_3_orders = weekly.loc[3]
print(weekly)

Week 3 has 254 website orders. pandas computes every week in one groupby; in SQL the same is a GROUP BY on a week expression.

4. Revenue from a price column

SELECT SUM(app_orders * avg_basket) AS app_revenue
FROM daily;

import pandas as pd

df = pd.read_csv("daily.csv")

app_revenue = (df["app_orders"] * df["avg_basket"]).sum()
print(app_revenue)

App revenue is ₹472,295. Multiply first, then add up: adding orders and multiplying by an average basket gives a different number.

5. Find the unusual day

import pandas as pd

df = pd.read_csv("daily.csv")

x = df["website_orders"]
q1, q3 = x.quantile([0.25, 0.75])
fence = 1.5 * (q3 - q1)
outliers = df[(x < q1 - fence) | (x > q3 + fence)]
print(outliers[["day", "website_orders"]])
outlier_days = len(outliers)

WITH q AS (
  SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY website_orders) AS q1,
         PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY website_orders) AS q3
  FROM daily
)
SELECT COUNT(*) AS outlier_days
FROM daily, q
WHERE website_orders < q1 - 1.5 * (q3 - q1)
   OR website_orders > q3 + 1.5 * (q3 - q1);

One outlier: day 17, with 61 website orders against a fence of 45.88. SQLite lacks PERCENTILE_CONT, which is a good example of where SQL dialects differ.

6. Trend and relationship

SELECT ROUND(CORR(ad_spend, website_orders), 3) AS r
FROM daily;

SELECT ROUND(REGR_INTERCEPT(website_orders, day) + REGR_SLOPE(website_orders, day) * 31, 2) AS forecast_day_31
FROM daily;

import numpy as np
import pandas as pd

df = pd.read_csv("daily.csv")

slope, intercept = np.polyfit(df["day"], df["website_orders"], 1)
forecast_day_31 = intercept + slope * 31
print(round(forecast_day_31, 2))

The correlation between ad spend and website orders is 0.723 and the straight-line forecast for day 31 is 39.75. Statistical functions like these are one place where Python tends to be more convenient, because libraries such as SciPy and statsmodels cover far more than SQL does.

Which should you learn first?

• If your data lives in a database or a warehouse, learn SQL first: it is how you get the right rows out.
• If you work from files and need charts, statistics or repeatable scripts, learn Python and pandas first.
• If you are not sure, a spreadsheet plus SQL covers a great deal of business analysis.

Or skip the code

If you only need the answers, AnalyzeIt writes and runs the query for you and shows it next to the result, so you can check it, or learn from it. You can try it with five free runs and no account.

Read more: Free browser tools for data and code, or try the live demo with no account.