import sqlite3
import pandas as pd
import os

print("Libraries imported successfully.")
print("Current folder:", os.getcwd())
print("Files in this folder:", os.listdir())

Libraries imported successfully.
Current folder: C:\Users\pravi\SQL Project
Files in this folder: ['.ipynb_checkpoints', 'E commerce sales Analytics.ipynb', 'olist.db', 'olist_customers_dataset (1).csv', 'olist_geolocation_dataset.csv', 'olist_orders_dataset.csv', 'olist_order_items_dataset.csv', 'olist_order_payments_dataset.csv', 'olist_order_reviews_dataset.csv', 'olist_products_dataset.csv', 'olist_sellers_dataset.csv', 'product_category_name_translation.csv']

# This creates olist.db in the SAME folder as this notebook
conn = sqlite3.connect('olist.db')
cur = conn.cursor()

print("Connected to olist.db")
print("Database file created at:", os.path.abspath('olist.db'))

Connected to olist.db
Database file created at: C:\Users\pravi\SQL Project\olist.db

import os
[f for f in os.listdir() if f.endswith('.csv')]

['olist_customers_dataset .csv',
 'olist_geolocation_dataset.csv',
 'olist_orders_dataset.csv',
 'olist_order_items_dataset.csv',
 'olist_order_payments_dataset.csv',
 'olist_order_reviews_dataset.csv',
 'olist_products_dataset.csv',
 'olist_sellers_dataset.csv',
 'product_category_name_translation.csv']

DATA_FOLDER = "."   # "." means "this same folder". Change this if your CSVs are elsewhere, e.g. "C:/Users/YourName/Downloads"

# table_name : csv_filename  --- EDIT THE FILENAMES BELOW IF YOURS ARE DIFFERENT
csv_files = {
    'customers':            'olist_customers_dataset .csv',
    'geolocation':          'olist_geolocation_dataset.csv',
    'order_items':          'olist_order_items_dataset.csv',
    'order_payments':       'olist_order_payments_dataset.csv',
    'order_reviews':        'olist_order_reviews_dataset.csv',
    'orders':                'olist_orders_dataset.csv',
    'sellers':               'olist_sellers_dataset.csv',
    'category_translation': 'product_category_name_translation.csv',
}

for table_name, filename in csv_files.items():
    filepath = os.path.join(DATA_FOLDER, filename)
    if not os.path.exists(filepath):
        print(f"NOT FOUND (skipped): {filepath}  <-- fix the filename above")
        continue
    df = pd.read_csv(filepath)
    df.columns = [c.strip() for c in df.columns]           # remove accidental spaces in column names
    df.to_sql(table_name, conn, if_exists='replace', index=False)
    print(f"Loaded '{table_name}' -> {df.shape[0]} rows, {df.shape[1]} columns")

conn.commit()
print("\nAll available files loaded and saved into olist.db")

Loaded 'customers' -> 99441 rows, 5 columns
Loaded 'geolocation' -> 1000163 rows, 5 columns
Loaded 'order_items' -> 112650 rows, 7 columns
Loaded 'order_payments' -> 103886 rows, 5 columns
Loaded 'order_reviews' -> 99224 rows, 7 columns
Loaded 'orders' -> 99441 rows, 8 columns
Loaded 'sellers' -> 3095 rows, 4 columns
Loaded 'category_translation' -> 71 rows, 2 columns

All available files loaded and saved into olist.db

tables = pd.read_sql_query("SELECT name FROM sqlite_master WHERE type='table'", conn)
print("Tables currently in olist.db:")
print(tables)

Tables currently in olist.db:
                   name
0             customers
1           geolocation
2           order_items
3        order_payments
4         order_reviews
5                orders
6               sellers
7  category_translation

cleaning_sql = """
CREATE VIEW IF NOT EXISTS clean_order_reviews AS
SELECT * FROM (
    SELECT r.*,
           ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY review_answer_timestamp DESC) AS rn
    FROM order_reviews r
) WHERE rn = 1;

CREATE VIEW IF NOT EXISTS order_revenue AS
SELECT order_id,
       SUM(price) AS items_revenue,
       SUM(freight_value) AS freight_revenue,
       SUM(price + freight_value) AS total_revenue,
       COUNT(*) AS item_count
FROM order_items
GROUP BY order_id;

CREATE VIEW IF NOT EXISTS fact_orders AS
SELECT
    o.order_id,
    o.customer_id,
    c.customer_unique_id,
    c.customer_state,
    c.customer_city,
    o.order_status,
    o.order_purchase_timestamp,
    o.order_approved_at,
    o.order_delivered_carrier_date,
    o.order_delivered_customer_date,
    o.order_estimated_delivery_date,
    r.total_revenue,
    r.item_count,
    rv.review_score,
    JULIANDAY(o.order_delivered_customer_date) - JULIANDAY(o.order_purchase_timestamp) AS delivery_days,
    JULIANDAY(o.order_estimated_delivery_date) - JULIANDAY(o.order_delivered_customer_date) AS delivery_buffer_days
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
LEFT JOIN order_revenue r ON r.order_id = o.order_id
LEFT JOIN clean_order_reviews rv ON rv.order_id = o.order_id
WHERE o.order_status = 'delivered'
  AND o.order_delivered_customer_date IS NOT NULL;
"""

cur.executescript(cleaning_sql)
conn.commit()
print("Cleaning views created: clean_order_reviews, order_revenue, fact_orders")

Cleaning views created: clean_order_reviews, order_revenue, fact_orders

# Confirm fact_orders was built correctly
pd.read_sql_query("SELECT COUNT(*) AS total_delivered_orders FROM fact_orders", conn)

# Q1: Headline KPIs — total revenue, total orders, total customers, average order value
query = """
SELECT
    ROUND(SUM(total_revenue),2) AS total_revenue,
    COUNT(DISTINCT order_id) AS total_orders,
    COUNT(DISTINCT customer_unique_id) AS total_customers,
    ROUND(SUM(total_revenue)/COUNT(DISTINCT order_id),2) AS avg_order_value
FROM fact_orders;
"""
pd.read_sql_query(query, conn)

# Q2: Monthly revenue trend
query = """
SELECT strftime('%Y-%m', order_purchase_timestamp) AS month,
       ROUND(SUM(total_revenue),2) AS monthly_revenue,
       COUNT(DISTINCT order_id) AS orders
FROM fact_orders
GROUP BY month
ORDER BY month;
"""
pd.read_sql_query(query, conn)

# Q3: Repeat customer rate — the single most important retention metric
query = """
SELECT
    ROUND(100.0 *
        (SELECT COUNT(*) FROM (
            SELECT customer_unique_id FROM fact_orders
            GROUP BY customer_unique_id HAVING COUNT(DISTINCT order_id) > 1
        ))
        / (SELECT COUNT(DISTINCT customer_unique_id) FROM fact_orders)
    , 2) AS repeat_customer_rate_pct;
"""
pd.read_sql_query(query, conn)

# Q4: On-time vs late delivery rate, and its effect on review scores
query = """
SELECT
    CASE WHEN order_delivered_customer_date <= order_estimated_delivery_date
         THEN 'On-Time' ELSE 'Late' END AS delivery_status,
    COUNT(*) AS orders,
    ROUND(AVG(review_score),2) AS avg_review_score
FROM fact_orders
WHERE review_score IS NOT NULL
GROUP BY delivery_status;
"""
pd.read_sql_query(query, conn)

# Q5: Revenue by customer state, ranked highest to lowest
query = """
SELECT customer_state,
       ROUND(SUM(total_revenue),2) AS revenue,
       COUNT(DISTINCT order_id) AS orders,
       DENSE_RANK() OVER (ORDER BY SUM(total_revenue) DESC) AS revenue_rank
FROM fact_orders
GROUP BY customer_state
ORDER BY revenue_rank;
"""
pd.read_sql_query(query, conn)

# Q6: Top 10 sellers by revenue
query = """
SELECT oi.seller_id, s.seller_state,
       ROUND(SUM(oi.price),2) AS revenue,
       COUNT(DISTINCT oi.order_id) AS orders_fulfilled
FROM order_items oi
JOIN sellers s ON s.seller_id = oi.seller_id
JOIN fact_orders fo ON fo.order_id = oi.order_id
GROUP BY oi.seller_id, s.seller_state
ORDER BY revenue DESC
LIMIT 10;
"""
pd.read_sql_query(query, conn)

# Q7: Payment method mix
query = """
SELECT payment_type,
       COUNT(*) AS transactions,
       ROUND(SUM(payment_value),2) AS total_value
FROM order_payments
GROUP BY payment_type
ORDER BY total_value DESC;
"""
pd.read_sql_query(query, conn)

# Q8: Month-over-month revenue growth — using LAG() to compare each month to the previous one
query = """
WITH monthly AS (
    SELECT strftime('%Y-%m', order_purchase_timestamp) AS month,
           SUM(total_revenue) AS revenue
    FROM fact_orders
    GROUP BY month
)
SELECT month, ROUND(revenue,2) AS revenue,
       ROUND(revenue - LAG(revenue) OVER (ORDER BY month), 2) AS mom_change,
       ROUND(100.0*(revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month), 2) AS mom_growth_pct
FROM monthly
ORDER BY month;
"""
pd.read_sql_query(query, conn)

# Q9: Cumulative revenue over time — shows total business growth trajectory
query = """
SELECT strftime('%Y-%m', order_purchase_timestamp) AS month,
       ROUND(SUM(total_revenue),2) AS monthly_revenue,
       ROUND(SUM(SUM(total_revenue)) OVER (ORDER BY strftime('%Y-%m', order_purchase_timestamp)), 2) AS running_total_revenue
FROM fact_orders
GROUP BY month
ORDER BY month;
"""
pd.read_sql_query(query, conn)

# Q10: 3-month moving average — removes month-to-month noise to see the real trend
query = """
WITH monthly AS (
    SELECT strftime('%Y-%m', order_purchase_timestamp) AS month, SUM(total_revenue) AS revenue
    FROM fact_orders GROUP BY month
)
SELECT month, ROUND(revenue,2) AS revenue,
       ROUND(AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3mo
FROM monthly ORDER BY month;
"""
pd.read_sql_query(query, conn)

# Q11: What % of revenue comes from the top 15% of customers? (Pareto / 80-20 style analysis)
query = """
WITH cust_rev AS (
    SELECT customer_unique_id, SUM(total_revenue) AS rev
    FROM fact_orders GROUP BY customer_unique_id
),
tiled AS (
    SELECT *, NTILE(100) OVER (ORDER BY rev DESC) AS pct_tile FROM cust_rev
)
SELECT ROUND(100.0*SUM(CASE WHEN pct_tile <= 15 THEN rev ELSE 0 END)/SUM(rev), 2) AS revenue_pct_from_top15pct_customers
FROM tiled;
"""
pd.read_sql_query(query, conn)

# Q12: Average days between a customer's 1st and 2nd purchase — reveals the retention window
query = """
WITH ordered_purchases AS (
    SELECT customer_unique_id, order_id, order_purchase_timestamp,
           ROW_NUMBER() OVER (PARTITION BY customer_unique_id ORDER BY order_purchase_timestamp) AS purchase_seq
    FROM fact_orders
)
SELECT AVG(JULIANDAY(o2.order_purchase_timestamp) - JULIANDAY(o1.order_purchase_timestamp)) AS avg_days_between_1st_2nd_purchase
FROM ordered_purchases o1
JOIN ordered_purchases o2
  ON o1.customer_unique_id = o2.customer_unique_id
  AND o1.purchase_seq = 1 AND o2.purchase_seq = 2;
"""
pd.read_sql_query(query, conn)

# Q13: Are we dependent on a small number of sellers? Top 10 sellers' share of platform revenue
query = """
WITH seller_rev AS (
    SELECT oi.seller_id, SUM(oi.price) AS revenue
    FROM order_items oi JOIN fact_orders fo ON fo.order_id = oi.order_id
    GROUP BY oi.seller_id
),
ranked AS (SELECT *, RANK() OVER (ORDER BY revenue DESC) AS rnk FROM seller_rev)
SELECT ROUND(100.0*SUM(CASE WHEN rnk<=10 THEN revenue ELSE 0 END)/(SELECT SUM(revenue) FROM seller_rev), 2) AS top10_seller_revenue_pct
FROM ranked;
"""
pd.read_sql_query(query, conn)

# Q15: Break delivery times into 10 equal buckets to see the full distribution, not just the average
query = """
WITH d AS (
    SELECT delivery_days, NTILE(10) OVER (ORDER BY delivery_days) AS decile FROM fact_orders
)
SELECT decile, ROUND(MIN(delivery_days),1) AS min_days, ROUND(MAX(delivery_days),1) AS max_days
FROM d GROUP BY decile ORDER BY decile;
"""
pd.read_sql_query(query, conn)

# Q16: Find orders where a customer paid in MORE installments than their own personal average
# (a correlated subquery — the inner query depends on the outer row, re-runs per row)
query = """
SELECT op.order_id, op.payment_installments
FROM order_payments op
WHERE op.payment_installments > (
    SELECT AVG(op2.payment_installments)
    FROM order_payments op2
    JOIN orders o2 ON o2.order_id = op2.order_id
    JOIN orders o1 ON o1.order_id = op.order_id
    WHERE o2.customer_id = o1.customer_id
)
LIMIT 10;
"""
pd.read_sql_query(query, conn)

# Q17: Build a reusable VIEW so any future query/dashboard just references vw_monthly_kpis
query_create = """
CREATE VIEW IF NOT EXISTS vw_monthly_kpis AS
SELECT strftime('%Y-%m', order_purchase_timestamp) AS month,
       COUNT(DISTINCT order_id) AS orders,
       COUNT(DISTINCT customer_unique_id) AS customers,
       ROUND(SUM(total_revenue),2) AS revenue,
       ROUND(AVG(review_score),2) AS avg_review_score,
       ROUND(AVG(delivery_days),1) AS avg_delivery_days
FROM fact_orders GROUP BY month;
"""
cur.executescript(query_create)
conn.commit()
pd.read_sql_query("SELECT * FROM vw_monthly_kpis ORDER BY month LIMIT 5", conn)

# Q18: Add indexes to speed up the joins/filters used throughout, then verify SQLite is using them
index_sql = """
CREATE INDEX IF NOT EXISTS idx_orders_customer_id ON orders(customer_id);
CREATE INDEX IF NOT EXISTS idx_order_items_order_id ON order_items(order_id);
CREATE INDEX IF NOT EXISTS idx_order_items_seller_id ON order_items(seller_id);
CREATE INDEX IF NOT EXISTS idx_order_payments_order_id ON order_payments(order_id);
"""
cur.executescript(index_sql)
conn.commit()

plan = pd.read_sql_query("EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = '0000366f3b9a7992bf8c76cfdf3221e2'", conn)
plan

# Q19: Correlation between delivery days and review score — quantifies the delay-satisfaction link
query = """
SELECT delivery_days, review_score
FROM fact_orders
WHERE review_score IS NOT NULL;
"""
df_corr = pd.read_sql_query(query, conn)
correlation = df_corr['delivery_days'].corr(df_corr['review_score'])
print(f"Correlation between delivery days and review score: {round(correlation, 3)}")

Correlation between delivery days and review score: -0.334

	month	monthly_revenue	orders
0	2016-09	143.46	1
1	2016-10	46490.66	265
2	2016-12	19.62	1
3	2017-01	127482.37	750
4	2017-02	271239.32	1653
5	2017-03	414330.95	2546
6	2017-04	390812.40	2303
7	2017-05	566657.40	3545
8	2017-06	490050.37	3135
9	2017-07	566299.08	3872
10	2017-08	645832.36	4193
11	2017-09	701077.49	4150
12	2017-10	751117.01	4478
13	2017-11	1153229.37	7288
14	2017-12	843078.29	5513
15	2018-01	1077887.46	7069
16	2018-02	966168.41	6555
17	2018-03	1120598.24	7003
18	2018-04	1132878.93	6798
19	2018-05	1128774.52	6749
20	2018-06	1011448.96	6096
21	2018-07	1027286.52	6156
22	2018-08	985491.64	6351

	customer_state	revenue	orders	revenue_rank
0	SP	5768518.23	40494	1
1	RJ	2055401.57	12350	2
2	MG	1818891.67	11354	3
3	RS	861278.79	5344	4
4	PR	781708.80	4923	5
5	SC	595127.78	3546	6
6	BA	591137.81	3256	7
7	DF	346123.35	2080	8
8	GO	334212.35	1957	9
9	ES	317657.93	1995	10
10	PE	308972.05	1593	11
11	CE	266436.77	1279	12
12	PA	212023.57	946	13
13	MT	181224.42	886	14
14	MA	147803.55	717	15
15	PB	137838.55	517	16
16	MS	134367.55	701	17
17	PI	105178.19	476	18
18	RN	100714.78	474	19
19	AL	94172.49	397	20
20	SE	70289.13	335	21
21	TO	60007.37	274	22
22	RO	56966.00	243	23
23	AM	27585.47	145	24
24	AC	19575.33	80	25
25	AP	16141.81	67	26
26	RR	9039.52	41	27

	seller_id	seller_state	revenue	orders_fulfilled
0	4869f7a5dfa277a7dca6462dcf3b52b2	SP	226987.93	1124
1	53243585a1d6dc2643021fd1853d8905	BA	217940.44	348
2	4a3ca9315b744ce9f8e9374361493884	SP	196882.12	1772
3	fa1c13f2614d7b5c4749cbc52fecda94	SP	190917.14	578
4	7c67e1448b00f6e969d365cea6b010ab	SP	186570.05	973
5	7e93a43ef30c4f03f38b393420bc753a	SP	165981.49	319
6	da8622b14eb17ae2831f4ac5b9dab84a	SP	159816.87	1311
7	7a67c85e85bb2ce8582c35f2203ad736	SP	139658.69	1145
8	1025f0e2d44d7041d6cf58b6550e0bfa	SP	138208.56	910
9	955fee9216a65b617aa5c0531780ce60	SP	131836.71	1261

	payment_type	transactions	total_value
0	credit_card	76795	12542084.19
1	boleto	19784	2869361.27
2	voucher	5775	379436.87
3	debit_card	1529	217989.79
4	not_defined	3	0.00

	month	revenue	mom_change	mom_growth_pct
0	2016-09	143.46	NaN	NaN
1	2016-10	46490.66	46347.20	32306.71
2	2016-12	19.62	-46471.04	-99.96
3	2017-01	127482.37	127462.75	649657.24
4	2017-02	271239.32	143756.95	112.77
5	2017-03	414330.95	143091.63	52.75
6	2017-04	390812.40	-23518.55	-5.68
7	2017-05	566657.40	175845.00	44.99
8	2017-06	490050.37	-76607.03	-13.52
9	2017-07	566299.08	76248.71	15.56
10	2017-08	645832.36	79533.28	14.04
11	2017-09	701077.49	55245.13	8.55
12	2017-10	751117.01	50039.52	7.14
13	2017-11	1153229.37	402112.36	53.54
14	2017-12	843078.29	-310151.08	-26.89
15	2018-01	1077887.46	234809.17	27.85
16	2018-02	966168.41	-111719.05	-10.36
17	2018-03	1120598.24	154429.83	15.98
18	2018-04	1132878.93	12280.69	1.10
19	2018-05	1128774.52	-4104.41	-0.36
20	2018-06	1011448.96	-117325.56	-10.39
21	2018-07	1027286.52	15837.56	1.57
22	2018-08	985491.64	-41794.88	-4.07

Loading CSV Files into the Database¶

Double check the tables actually loaded¶

Data cleaning¶

Pareto Analysis¶

Repeat Purchase Gap (self-JOIN + ROW_NUMBER)¶

Seller Revenue Concentration (RANK + Pareto)¶

Sellers with Declining Monthly Revenue (LAG-based anomaly flag)¶

Q14: Detect sellers whose revenue dropped vs. the previous month — an early-warning signal¶

Delivery Delay Deciles (NTILE for percentile buckets)¶

Correlated Subquery: Above-Average Installment Buyers¶

Reusable VIEW: Monthly KPI Dashboard Source¶

Index Optimization + EXPLAIN QUERY PLAN¶

Statistically Significant Finding (via correlation)¶

	decile	min_days	max_days
0	1	0.5	4.2
1	2	4.2	6.0
2	3	6.0	7.3
3	4	7.3	8.7
4	5	8.7	10.2
5	6	10.2	12.1
6	7	12.1	14.2
7	8	14.2	17.4
8	9	17.4	23.1
9	10	23.1	209.6

	order_id	payment_installments
0	332c6742772f2df936696b6512b10edb	6
1	97e8d3eb8f48e3610beed991e5f32296	4
2	ac14a1a3d8c2b071a150eb2990b5ecd8	2
3	d289934bddf878e1cac62c95c8ee5402	3
4	54066aeaaf3ac32e7bb6e45aa3bf65e4	2
5	20b44662cba0e4e87a3d794343c1076c	4
6	7525e8f8b1a51d44f9912ce81146ac7d	10
7	50517e1413e46c6df586274af938d47e	2
8	4821d5af4c2ac98b0f70e47c5d845520	3
9	ce79ae0eb1e344fbf19835147efc64e4	6

	month	orders	customers	revenue	avg_review_score	avg_delivery_days
0	2016-09	1	1	143.46	1.00	54.8
1	2016-10	265	262	46490.66	4.01	19.6
2	2016-12	1	1	19.62	5.00	4.7
3	2017-01	750	718	127482.37	4.20	12.6
4	2017-02	1653	1630	271239.32	4.20	13.2

	delivery_status	orders	avg_review_score
0	Late	7661	2.57
1	On-Time	88163	4.29