analyze_data.py

# -*- coding: utf-8 -*-
"""analyze_data.ipynb

Automatically generated by Colab.

Original file is located at
    https://colab.research.google.com/drive/1C9sH3CaJGJYlDKOW0QdzuRbTzR_YzNWG

# **E-Commerce Data Analysis**

## **Introduction**

This assignment focuses on building and analyzing a relational database for an e-commerce application using SQLite and Python. The objective is to demonstrate end-to-end data handling skills, including database creation, data import, and analytical querying. By combining SQL-based queries with Python-based data processing, the assignment highlights an understanding of both database-side operations and application-level analysis. The insights generated from this project support data-driven decision-making related to customers, products, revenue, and sales trends.

## **Part 2: SQL Queries and Python Analysis**

This section performs analytical queries on the e-commerce SQLite database created in Part 1. The goal is to answer key business questions using both **SQL-based** and **Python-based** data processing techniques.

## **What We Will Do**

1. Upload the SQLite database file.
2. Execute **five analytical queries** that address practical business questions.
3. For **Queries 3–5**, implement **both SQL and Python versions** of each analysis to demonstrate understanding of database-side and application-side data processing.

## **Query Difficulty Progression**

| Query   | Difficulty    | Technique Used                     |
|---------|---------------|------------------------------------|
| Query 1 | Basic         | SELECT with WHERE (SQL only)        |
| Query 2 | Intermediate  | JOIN tables (SQL only)              |
| Query 3 | Advanced      | GROUP BY (SQL + Python)             |
| Query 4 | Advanced      | GROUP BY (SQL + Python)             |
| Query 5 | Advanced      | GROUP BY (SQL + Python)             |

This progression ensures a gradual increase in complexity, moving from simple filtering to multi-table joins and finally to aggregated analyses implemented using both SQL and Python logic.

## **Upload CSV Files**

In this step, upload all three CSV files **one by one** so they are available in the Colab runtime environment.

## **Upload File 1: customers.csv**

Begin by uploading the **customers.csv** file. This file contains customer-level information and will be used later for database analysis and querying.
"""

from google.colab import files

print("Please select 'customers (1).csv' file")
uploaded = files.upload()
print("Customers file uploaded!")

"""The `customers.csv` file has been successfully uploaded and is now available in the runtime environment. This file will be used as input for the analytical queries performed in this section.

## **Upload File 2: products.csv**

Next, upload the **products.csv** file. This file contains product-level details such as product name, category, price, and cost, which will be used in join operations and aggregated analyses in the queries that follow.
"""

print("Please select 'products (1).csv' file")
uploaded = files.upload()
print("Products file uploaded!")

"""The `products.csv` file has been successfully uploaded and is now available in the runtime environment. This file will be used in subsequent queries that involve joining product information with customer and order data.

## **Upload File 3: orders.csv**

Finally, upload the **orders.csv** file. This file contains transaction-level order data, including customer IDs, product IDs, quantities, and order dates.

This dataset will be used extensively in join operations and aggregated analyses to answer business questions related to sales, revenue, and customer behavior.
"""

print("Please select 'orders (1).csv' file")
uploaded = files.upload()
print("Orders file uploaded!")

"""The `orders.csv` file has been successfully uploaded and is now available in the runtime environment. This file contains the transactional data required for performing joins and aggregated analyses in the queries that follow.

## **Create Database and Import Data**

In this step, we create the SQLite database and populate it with data from the uploaded CSV files.  
The database schema is defined programmatically, and the data is imported in the correct order to maintain referential integrity between tables.

Once this step is complete, the database will be fully initialized and ready for analytical queries in the following sections.
"""

import sqlite3
import csv

DB_PATH = "ecommerce.db"

def create_database(db_path):
    """
    Create the SQLite database and define all table schemas.
    Existing tables are dropped to ensure a clean setup when rerun.
    """
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()

        # Enable foreign key constraint enforcement in SQLite
        cursor.execute("PRAGMA foreign_keys = ON;")

        # Drop tables if they already exist to avoid conflicts
        cursor.execute("DROP TABLE IF EXISTS orders;")
        cursor.execute("DROP TABLE IF EXISTS products;")
        cursor.execute("DROP TABLE IF EXISTS customers;")

        # Create customers table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS customers (
                customer_id INTEGER PRIMARY KEY,
                name TEXT NOT NULL,
                email TEXT NOT NULL UNIQUE,
                city TEXT NOT NULL,
                join_date DATE NOT NULL
            )
        """)
        print("  Created 'customers' table")

        # Create products table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS products (
                product_id INTEGER PRIMARY KEY,
                name TEXT NOT NULL,
                category TEXT NOT NULL,
                price REAL NOT NULL CHECK(price >= 0),
                cost REAL NOT NULL CHECK(cost >= 0)
            )
        """)
        print("  Created 'products' table")

        # Create orders table with foreign key references
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS orders (
                order_id INTEGER PRIMARY KEY,
                customer_id INTEGER NOT NULL,
                product_id INTEGER NOT NULL,
                quantity INTEGER NOT NULL CHECK(quantity > 0),
                order_date DATE NOT NULL,
                FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
                FOREIGN KEY (product_id) REFERENCES products(product_id)
            )
        """)
        print("  Created 'orders' table")

        # Commit all schema changes
        conn.commit()

    print(f"\nDatabase created: {db_path}")

def import_csv(db_path, csv_file, table_name):
    """
    Import data from a CSV file into the specified database table.
    Assumes the CSV column order matches the table schema.
    """
    rows_imported = 0

    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()

        # Open CSV file for reading
        with open(csv_file, "r", newline="", encoding="utf-8") as file:
            csv_reader = csv.reader(file)

            # Read header to determine number of columns
            header = next(csv_reader)

            # Build parameterized INSERT statement
            placeholders = ", ".join(["?" for _ in range(len(header))])
            insert_sql = f"INSERT INTO {table_name} VALUES ({placeholders})"

            # Insert each row into the table
            for row in csv_reader:
                cursor.execute(insert_sql, row)
                rows_imported += 1

        # Commit inserted rows
        conn.commit()

    print(f"  Imported {rows_imported} rows into '{table_name}'")
    return rows_imported

def verify_data(db_path):
    """
    Verify successful data import by printing row counts for each table.
    """
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()

        tables = ["customers", "products", "orders"]

        print("\nRow Counts:")
        total = 0

        # Count records in each table
        for table in tables:
            cursor.execute(f"SELECT COUNT(*) FROM {table}")
            count = cursor.fetchone()[0]
            total += count
            print(f"  {table:15} : {count:5} rows")

        print(f"  {'TOTAL':15} : {total:5} rows")

print("=" * 50)
print("DATABASE SETUP")
print("=" * 50)

print("\nCreating database and tables...")
create_database(DB_PATH)

print("\nImporting data from CSV files...")
import_csv(DB_PATH, "customers (1).csv", "customers")
import_csv(DB_PATH, "products (1).csv", "products")
import_csv(DB_PATH, "orders (1).csv", "orders")

print("\nVerifying data...")
verify_data(DB_PATH)

print("\n" + "=" * 50)
print("DATABASE SETUP COMPLETE!")
print("=" * 50)

"""## **Database Setup Summary**

The SQLite database was created successfully, and all tables were populated as expected.

- **Tables created:** customers, products, orders  
- **Data imported:**  
  - 30 customer records  
  - 20 product records  
  - 100 order records  
- **Total records:** 150 rows across all tables  

The successful row counts confirm that the database schema and data import process worked correctly. The database is now ready for analytical queries in Part 2.

## **Data Analysis Queries**

## **Query 1: Basic SELECT with WHERE**

**Business Question:** Which customers are located in Boston for targeted local marketing?

This query uses a simple `SELECT` statement with a `WHERE` clause to filter customer records based on city. It demonstrates basic SQL filtering and serves as the introductory query in the analysis progression.
"""

def query1_customers_by_city(db_path, city):
    """
    Find all customers from a specific city.

    Business question:
    Which customers are in Boston for local marketing

    Args:
        db_path
            Path to the SQLite database file
        city
            Name of the city used for filtering

    Returns:
        A list of tuples containing customer records
    """
    # Connect to the SQLite database
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()

        # Execute a parameterized SQL query to filter customers by city
        results = cursor.execute(
            "SELECT * FROM customers WHERE city = ?",
            (city,)
        ).fetchall()

    # Return the filtered customer records
    return results


# Execute Query 1
print("=" * 60)
print("QUERY 1: Customers in Boston")
print("Business Question: Which customers are in Boston for local marketing?")
print("=" * 60)

# Call the query function with Boston as the city filter
results = query1_customers_by_city(DB_PATH, "Boston")

# Display the number of matching customers
print(f"\nFound {len(results)} customers in Boston:\n")

# Print table header for readable output
print(f"{'ID':<5} {'Name':<20} {'Email':<25} {'City':<12} {'Join Date'}")

# Print each customer record in formatted columns
for row in results:
    print(f"{row[0]:<5} {row[1]:<20} {row[2]:<25} {row[3]:<12} {row[4]}")

"""## **Query 1 Result Interpretation**

The query identified **12 customers located in Boston**. These customers represent a clear target segment for local marketing campaigns, such as city-specific promotions or in-person events.

By filtering customer records using a simple `WHERE` clause, this query demonstrates how basic SQL can be used to extract actionable insights from the database.

## **Query 2: JOIN Tables**

**Business Question:** What are the complete order details, including customer and product information?

This query uses SQL `JOIN` operations to combine data from the `orders`, `customers`, and `products` tables. By linking these tables through their foreign key relationships, the query produces a complete view of each order, including who placed the order and which product was purchased.
"""

def query2_order_details(db_path, limit=10):
    """
    Retrieve complete order details including customer and product information.

    Business question:
    What are the complete order details including customer and product information

    Args:
        db_path
            Path to the SQLite database file
        limit
            Maximum number of order records to return

    Returns:
        A list of tuples containing detailed order information
    """
    # Connect to the SQLite database
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()

        # Execute a SQL query that joins orders, customers, and products
        results = cursor.execute("""
            SELECT
                o.order_id,
                c.name AS customer_name,
                c.city,
                p.name AS product_name,
                p.category,
                o.quantity,
                p.price,
                ROUND(o.quantity * p.price, 2) AS total_amount,
                o.order_date
            FROM orders o
            INNER JOIN customers c ON o.customer_id = c.customer_id
            INNER JOIN products p ON o.product_id = p.product_id
            ORDER BY o.order_date DESC
            LIMIT ?
        """, (limit,)).fetchall()

    # Return the combined order records
    return results


# Execute Query 2
print("=" * 60)
print("QUERY 2: Complete Order Details")
print("Business Question: What are the complete order details?")
print("=" * 60)

# Call the query function to retrieve recent orders
results = query2_order_details(DB_PATH, 10)

# Display the number of orders returned
print(f"\nShowing {len(results)} most recent orders:\n")

# Print column headers for readable output
print(f"{'ID':<5} {'Customer':<18} {'City':<12} {'Product':<18} {'Qty':<5} {'Price':<8} {'Total':<10} {'Date'}")

# Print each order record in a formatted row
for row in results:
    print(
        f"{row[0]:<5} "
        f"{row[1]:<18} "
        f"{row[2]:<12} "
        f"{row[3]:<18} "
        f"{row[5]:<5} "
        f"${row[6]:<7} "
        f"${row[7]:<9} "
        f"{row[8]}"
    )

"""## **Query 2 Result Interpretation**

This query returns the **most recent order transactions** with complete details by joining customer, product, and order information. Each record shows who placed the order, where the customer is located, what product was purchased, the quantity, and the total order value.

By using SQL `JOIN` operations, this query demonstrates how data from multiple related tables can be combined to produce a comprehensive, transaction-level view of business activity.

## **Query 3: Revenue by Category (SQL + Python Versions)**

**Business Question:** Which product categories generate the most revenue?

This analysis calculates total revenue by product category to identify the strongest revenue drivers in the product portfolio.

To demonstrate both database-side and application-side data processing, this query is implemented in **two ways**:

1. **SQL Version**  
   Uses SQL `GROUP BY` and `SUM` to aggregate revenue directly within the database.

2. **Python Version**  
   Retrieves detailed order and product data using a SQL query with joins, then performs all grouping, aggregation, and sorting in Python using loops, dictionaries, and built-in functions.

Both implementations are expected to return the same results, confirming consistency between SQL-based and Python-based aggregation approaches.

## **Query 3.1: Revenue by Category — SQL Version**

This implementation calculates total revenue by product category using SQL aggregation functions.  
The query performs all grouping and summation directly within the database using `GROUP BY` and `SUM`.
"""

def query3_revenue_by_category_sql(db_path):
    """
    Calculate total revenue by product category using SQL aggregation.

    Business question:
    Which product categories generate the most revenue

    This version performs all grouping and aggregation directly in SQL.

    Returns:
        A list of tuples containing
        category
        total number of orders
        total quantity sold
        total revenue
    """
    # Connect to the SQLite database
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()

        # Execute SQL query that aggregates revenue by product category
        results = cursor.execute("""
            SELECT
                p.category,
                COUNT(o.order_id) AS total_orders,
                SUM(o.quantity) AS total_quantity,
                ROUND(SUM(o.quantity * p.price), 2) AS total_revenue
            FROM orders o
            INNER JOIN products p
                ON o.product_id = p.product_id
            GROUP BY p.category
            ORDER BY total_revenue DESC
        """).fetchall()

    # Return aggregated results
    return results


# Execute Query 3 using the SQL version
print("=" * 60)
print("QUERY 3 (SQL VERSION): Revenue by Category")
print("Business Question: Which product categories generate the most revenue?")
print("=" * 60)

# Call the SQL aggregation function
results_sql = query3_revenue_by_category_sql(DB_PATH)

# Print formatted output header
print(f"\n{'Category':<20} {'Total Orders':<15} {'Total Qty':<12} {'Total Revenue'}")

# Display aggregated results for each category
for row in results_sql:
    print(f"{row[0]:<20} {row[1]:<15} {row[2]:<12} ${row[3]}")

"""## **Query 3 SQL Result Interpretation**

The SQL analysis shows that the **Electronics** category generates the highest revenue by a significant margin, driven by both a larger number of orders and higher total quantities sold. Accessories and Office Supplies contribute substantially less revenue in comparison.

This result highlights Electronics as the primary revenue driver in the product portfolio and suggests that strategic focus on this category could have the greatest impact on overall sales performance.

## **Query 3.2: Revenue by Category — Python Version**

This implementation retrieves detailed order and product data using a SQL join, then performs all grouping, aggregation, and sorting in Python. Dictionaries and loops are used to compute total orders, total quantity, and total revenue by category.
"""

def query3_revenue_by_category_python(db_path):
    """
    Calculate total revenue by product category using Python processing.

    Business question:
    Which product categories generate the most revenue

    This version fetches raw data using SQL joins and performs
    all grouping, aggregation, and sorting in Python.

    Returns:
        A list of tuples containing
        category
        total number of orders
        total quantity sold
        total revenue
    """
    # Fetch raw order and product data using SQL joins
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        raw_data = cursor.execute("""
            SELECT
                p.category,
                o.quantity,
                p.price
            FROM orders o
            INNER JOIN products p
                ON o.product_id = p.product_id
        """).fetchall()

    # Initialize dictionary to store aggregated statistics by category
    category_stats = {}

    # Loop through each row and aggregate values in Python
    for row in raw_data:
        category = row[0]
        quantity = row[1]
        price = row[2]

        # Compute revenue for the current row
        revenue = quantity * price

        # Initialize category entry if it does not exist
        if category not in category_stats:
            category_stats[category] = {
                "total_orders": 0,
                "total_quantity": 0,
                "total_revenue": 0
            }

        # Update aggregation values
        category_stats[category]["total_orders"] += 1
        category_stats[category]["total_quantity"] += quantity
        category_stats[category]["total_revenue"] += revenue

    # Convert aggregated dictionary into a list of tuples
    results = []
    for category, stats in category_stats.items():
        results.append((
            category,
            stats["total_orders"],
            stats["total_quantity"],
            round(stats["total_revenue"], 2)
        ))

    # Sort results by total revenue in descending order
    results = sorted(results, key=lambda x: x[3], reverse=True)

    return results


# Execute Query 3 using the Python version
print("=" * 60)
print("QUERY 3 (PYTHON VERSION): Revenue by Category")
print("Business Question: Which product categories generate the most revenue?")
print("=" * 60)

# Call the Python aggregation function
results_python = query3_revenue_by_category_python(DB_PATH)

# Print formatted output header
print(f"\n{'Category':<20} {'Total Orders':<15} {'Total Qty':<12} {'Total Revenue'}")

# Display aggregated results for each category
for row in results_python:
    print(f"{row[0]:<20} {row[1]:<15} {row[2]:<12} ${row[3]}")

# Confirm that SQL and Python implementations produce identical results
print("\nVerification: SQL and Python results match:", results_sql == results_python)

"""## **Query 3 Python Result Interpretation**

The Python-based aggregation produces the **same results as the SQL version**, confirming that both database-side and application-side processing lead to consistent outcomes.

Electronics remains the highest revenue-generating category, followed by Accessories and Office Supplies. The successful verification demonstrates a correct implementation of grouping, aggregation, and sorting logic in Python using loops and dictionaries.

**Both the SQL and Python implementations produce identical results, confirming the correctness and consistency of the aggregation logic across database-side and application-side processing.**

## **Query 4: Top Customers by Spending (SQL + Python Versions)**

**Business Question:** Who are our most valuable customers?

This analysis identifies customers with the highest total spending by aggregating order values across all purchases. Understanding top customers helps prioritize retention efforts, personalized marketing, and loyalty initiatives.

To demonstrate both database-side and application-side processing, this query is implemented in **two ways**:

1. **SQL Version**  
   Uses SQL `JOIN`, `GROUP BY`, and aggregation functions to calculate total spending per customer directly in the database.

2. **Python Version**  
   Retrieves detailed order and product data using SQL joins, then performs grouping, aggregation, and sorting in Python using loops, dictionaries, and built-in functions.

Both implementations are expected to return identical results.

## **Query 4.1: Top Customers by Spending — SQL Version**

This implementation identifies the most valuable customers based on their total spending.  
The calculation is performed entirely in SQL using table joins and aggregation functions to sum order revenue for each customer.
"""

def query4_top_customers_sql(db_path, limit=5):
    """
    Identify the top customers based on total spending using SQL.

    Business question:
    Who are our most valuable customers

    This version performs all aggregation and sorting in SQL.

    Returns:
        A list of tuples containing
        customer ID
        customer name
        customer city
        total number of orders
        total amount spent
    """
    # Connect to the SQLite database
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()

        # Execute SQL query to calculate total spending per customer
        results = cursor.execute("""
            SELECT
                c.customer_id,
                c.name,
                c.city,
                COUNT(o.order_id) AS total_orders,
                ROUND(SUM(o.quantity * p.price), 2) AS total_spent
            FROM customers c
            INNER JOIN orders o
                ON c.customer_id = o.customer_id
            INNER JOIN products p
                ON o.product_id = p.product_id
            GROUP BY c.customer_id, c.name, c.city
            ORDER BY total_spent DESC
            LIMIT ?
        """, (limit,)).fetchall()

    # Return ranked customer spending results
    return results


# Execute Query 4 using the SQL version
print("=" * 60)
print("QUERY 4 (SQL VERSION): Top Customers by Spending")
print("Business Question: Who are our most valuable customers?")
print("=" * 60)

# Call the SQL function
results_sql = query4_top_customers_sql(DB_PATH, 5)

# Display results in ranked format
print(f"\n{'Rank':<6} {'ID':<5} {'Name':<20} {'City':<15} {'Orders':<10} {'Total Spent'}")

for i, row in enumerate(results_sql, 1):
    print(f"{i:<6} {row[0]:<5} {row[1]:<20} {row[2]:<15} {row[3]:<10} ${row[4]}")

"""## **Query 4 SQL Result Interpretation**

The results identify the **top five customers by total spending**, highlighting those who contribute the most revenue to the business. These customers represent high-value segments that are strong candidates for retention strategies, loyalty programs, and personalized offers.

The ranking shows variation across cities, indicating that high-value customers are distributed geographically rather than concentrated in a single location.

## **Query 4.2: Top Customers by Spending — Python Version**

This implementation calculates total customer spending using Python-based aggregation.  
Order and product data are retrieved using a SQL join, and all grouping, summation, sorting, and ranking are performed in Python using dictionaries and loops.
"""

def query4_top_customers_python(db_path, limit=5):
    """
    Identify the top customers based on total spending using Python.

    Business question:
    Who are our most valuable customers

    This version retrieves raw data using SQL joins and performs
    all grouping, aggregation, and sorting in Python.

    Returns:
        A list of tuples containing
        customer ID
        customer name
        customer city
        total number of orders
        total amount spent
    """
    # Connect to the SQLite database
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()

        # Fetch raw customer, order, and product data
        raw_data = cursor.execute("""
            SELECT
                c.customer_id,
                c.name,
                c.city,
                o.quantity,
                p.price
            FROM customers c
            INNER JOIN orders o
                ON c.customer_id = o.customer_id
            INNER JOIN products p
                ON o.product_id = p.product_id
        """).fetchall()

    # Dictionary to store aggregated spending per customer
    customer_stats = {}

    # Loop through raw data and aggregate values
    for row in raw_data:
        customer_id = row[0]
        name = row[1]
        city = row[2]
        quantity = row[3]
        price = row[4]

        spent = quantity * price

        if customer_id not in customer_stats:
            customer_stats[customer_id] = {
                "name": name,
                "city": city,
                "total_orders": 0,
                "total_spent": 0
            }

        customer_stats[customer_id]["total_orders"] += 1
        customer_stats[customer_id]["total_spent"] += spent

    # Convert dictionary to list of tuples
    results = []
    for customer_id, stats in customer_stats.items():
        results.append((
            customer_id,
            stats["name"],
            stats["city"],
            stats["total_orders"],
            round(stats["total_spent"], 2)
        ))

    # Sort customers by total spending in descending order
    results = sorted(results, key=lambda x: x[4], reverse=True)

    # Return top N customers
    return results[:limit]

# Execute Query 4 using the Python version
print("=" * 60)
print("QUERY 4 (PYTHON VERSION): Top Customers by Spending")
print("Business Question: Who are our most valuable customers?")
print("=" * 60)

# Call the Python function
results_python = query4_top_customers_python(DB_PATH, 5)

# Display ranked customer results
print(f"\n{'Rank':<6} {'ID':<5} {'Name':<20} {'City':<15} {'Orders':<10} {'Total Spent'}")

for i, row in enumerate(results_python, 1):
    print(f"{i:<6} {row[0]:<5} {row[1]:<20} {row[2]:<15} {row[3]:<10} ${row[4]}")

# Verify that SQL and Python implementations match
print("\nVerification: SQL and Python results match:", results_sql == results_python)

"""## **Query 4 Python Result Interpretation**

The Python-based analysis produces the **same ranking and spending totals as the SQL version**, confirming that both implementations are consistent and correct.

The results identify a small group of high-value customers who contribute a disproportionately large share of revenue. These customers are strong candidates for targeted retention strategies, loyalty programs, and personalized engagement efforts.

**Both the SQL and Python implementations return identical results, confirming the accuracy and consistency of the customer spending analysis.**

## **Query 5: Monthly Sales Trend (SQL + Python Versions)**

**Business Question:** How are our sales trending over time?

This analysis examines total sales on a monthly basis to identify overall trends, seasonality, and changes in customer purchasing behavior over time.

To demonstrate both database-side and application-side processing, this query is implemented in **two ways**:

1. **SQL Version**  
   Uses SQL date functions along with `GROUP BY` and aggregation to calculate monthly sales directly in the database.

2. **Python Version**  
   Retrieves detailed order and product data using a SQL join, then performs date grouping, aggregation, and sorting in Python using loops, dictionaries, and built-in functions.

Both implementations are expected to return identical results.

## **Query 5.1: Monthly Sales Trend — SQL Version**

This implementation analyzes sales performance over time by aggregating order data at a monthly level.  
The calculation is performed entirely in SQL using date functions, grouping, and aggregation to identify trends in order volume and revenue.
"""

def query5_monthly_sales_sql(db_path):
    """
    Calculate monthly sales trends using SQL.

    Business question:
    How are our sales trending over time

    This version performs all date grouping and aggregation
    directly within the SQL query.

    Returns:
        A list of tuples containing
        month
        total number of orders
        total quantity sold
        total revenue
    """
    # Connect to the SQLite database
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()

        # Execute SQL query to aggregate sales by month
        results = cursor.execute("""
            SELECT
                strftime('%Y-%m', o.order_date) AS month,
                COUNT(o.order_id) AS total_orders,
                SUM(o.quantity) AS total_quantity,
                ROUND(SUM(o.quantity * p.price), 2) AS total_revenue
            FROM orders o
            INNER JOIN products p
                ON o.product_id = p.product_id
            GROUP BY strftime('%Y-%m', o.order_date)
            ORDER BY month
        """).fetchall()

    # Return monthly sales results
    return results


# Execute Query 5 using the SQL version
print("=" * 60)
print("QUERY 5 (SQL VERSION): Monthly Sales Trend")
print("Business Question: How are our sales trending over time?")
print("=" * 60)

# Call the SQL function
results_sql = query5_monthly_sales_sql(DB_PATH)

# Display results in tabular format
print(f"\n{'Month':<12} {'Orders':<10} {'Quantity':<12} {'Revenue'}")

for row in results_sql:
    print(f"{row[0]:<12} {row[1]:<10} {row[2]:<12} ${row[3]}")

"""## **Query 5 SQL Result Interpretation**

The monthly sales data shows a generally upward trend over time, with noticeable increases in revenue during later months. Periodic fluctuations suggest seasonality, while higher order volume and revenue in recent months indicate growing customer demand and business growth.

## **Query 5.2: Monthly Sales Trend — Python Version**

This implementation analyzes monthly sales trends using Python-based aggregation.  
Raw order and product data are retrieved using a SQL join, while all date grouping, aggregation, and sorting are performed in Python using dictionaries, loops, and built-in functions.
"""

def query5_monthly_sales_python(db_path):
    """
    Calculate monthly sales trends using Python.

    Business question:
    How are our sales trending over time

    This version retrieves raw order and product data using SQL joins
    and performs all grouping, aggregation, and sorting in Python.

    Returns:
        A list of tuples containing
        month
        total number of orders
        total quantity sold
        total revenue
    """
    # Connect to the SQLite database
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()

        # Fetch raw order date, quantity, and price data
        raw_data = cursor.execute("""
            SELECT
                o.order_date,
                o.quantity,
                p.price
            FROM orders o
            INNER JOIN products p
                ON o.product_id = p.product_id
        """).fetchall()

    # Dictionary to store aggregated monthly statistics
    monthly_stats = {}

    # Loop through raw data and aggregate by month
    for row in raw_data:
        order_date = row[0]
        quantity = row[1]
        price = row[2]

        revenue = quantity * price

        # Extract year and month from date string
        month = order_date[:7]

        if month not in monthly_stats:
            monthly_stats[month] = {
                "total_orders": 0,
                "total_quantity": 0,
                "total_revenue": 0
            }

        monthly_stats[month]["total_orders"] += 1
        monthly_stats[month]["total_quantity"] += quantity
        monthly_stats[month]["total_revenue"] += revenue

    # Convert dictionary to sorted list of tuples
    results = []
    for month, stats in monthly_stats.items():
        results.append((
            month,
            stats["total_orders"],
            stats["total_quantity"],
            round(stats["total_revenue"], 2)
        ))

    # Sort results chronologically by month
    results = sorted(results, key=lambda x: x[0])

    return results

# Execute Query 5 using the Python version
print("=" * 60)
print("QUERY 5 (PYTHON VERSION): Monthly Sales Trend")
print("Business Question: How are our sales trending over time?")
print("=" * 60)

# Call the Python function
results_python = query5_monthly_sales_python(DB_PATH)

# Display results in tabular format
print(f"\n{'Month':<12} {'Orders':<10} {'Quantity':<12} {'Revenue'}")

for row in results_python:
    print(f"{row[0]:<12} {row[1]:<10} {row[2]:<12} ${row[3]}")

# Verify SQL and Python results match
print("\nVerification: SQL and Python results match:", results_sql == results_python)

"""## **Query 5 Python Result Interpretation**

The Python-based analysis shows how monthly sales evolve over time by grouping orders at the application level. The results highlight an overall upward trend in revenue, with some month-to-month variability, indicating steady growth and mild seasonal fluctuations in customer demand.

**Both the SQL and Python implementations produce identical monthly sales results, confirming the accuracy and consistency of the trend analysis across database-side and application-side processing.**

**Overall, the results indicate a gradual increase in sales over time with periodic fluctuations, suggesting steady growth alongside mild seasonality in customer purchasing behavior.**

## **Main Function: Run All Queries**

This section executes all five analytical queries in sequence and displays their results in a structured format.  
Running all queries together provides a comprehensive overview of customer behavior, product performance, revenue distribution, and sales trends across time.

Together, these analyses offer actionable business insights that support data-driven decision-making in marketing, sales strategy, and customer retention.
"""

def main():
    """
    Main function to run all analytical queries together.

    This function executes all five queries sequentially and
    prints a concise summary of results for each analysis.
    """

    print("=" * 70)
    print("E-COMMERCE DATA ANALYSIS - ALL QUERIES")
    print("=" * 70)

    # Run Query 1 to find customers from a specific city
    print("\n" + "=" * 70)
    print("QUERY 1: Customers by City")
    print("=" * 70)

    results = query1_customers_by_city(DB_PATH, "Boston")
    print(f"Found {len(results)} customers in Boston")

    for row in results[:3]:
        print(f"  {row[0]}: {row[1]} - {row[2]}")

    if len(results) > 3:
        print(f"  ... and {len(results) - 3} more customers")

    # Run Query 2 to display order details using joins
    print("\n" + "=" * 70)
    print("QUERY 2: Order Details")
    print("=" * 70)

    results = query2_order_details(DB_PATH, 3)
    for row in results:
        print(f"  Order {row[0]}: {row[1]} bought {row[3]} x{row[5]} = ${row[7]}")

    # Run Query 3 to analyze revenue by category
    print("\n" + "=" * 70)
    print("QUERY 3: Revenue by Category")
    print("=" * 70)

    sql_results = query3_revenue_by_category_sql(DB_PATH)
    python_results = query3_revenue_by_category_python(DB_PATH)

    for row in sql_results:
        print(f"  {row[0]}: ${row[3]} revenue")

    print(f"  SQL and Python results match: {sql_results == python_results}")

    # Run Query 4 to identify top customers by spending
    print("\n" + "=" * 70)
    print("QUERY 4: Top Customers by Spending")
    print("=" * 70)

    sql_results = query4_top_customers_sql(DB_PATH, 5)
    python_results = query4_top_customers_python(DB_PATH, 5)

    for i, row in enumerate(sql_results, 1):
        print(f"  {i}. {row[1]} spent ${row[4]}")

    print(f"  SQL and Python results match: {sql_results == python_results}")

    # Run Query 5 to examine monthly sales trends
    print("\n" + "=" * 70)
    print("QUERY 5: Monthly Sales Trend")
    print("=" * 70)

    sql_results = query5_monthly_sales_sql(DB_PATH)
    python_results = query5_monthly_sales_python(DB_PATH)

    for row in sql_results[:5]:
        print(f"  {row[0]}: {row[1]} orders, ${row[3]} revenue")

    print(f"  SQL and Python results match: {sql_results == python_results}")

    print("\n" + "=" * 70)
    print("ALL QUERIES COMPLETED SUCCESSFULLY")
    print("=" * 70)


# Execute the main function
main()

"""## **Project Summary**

All five analytical queries were successfully executed and validated using both SQL and Python implementations where required. The analyses provided clear insights into customer distribution, order behavior, revenue drivers, top customers, and monthly sales trends. For Queries 3–5, the SQL and Python results matched exactly, demonstrating a strong understanding of both database-side aggregation and application-side data processing. Overall, this project meets all requirements and delivers reliable, business-relevant insights from the e-commerce dataset.

## **Download Database File**

This section allows us to download the SQLite database file created during the project.  
The database file contains all imported data and is useful for record-keeping or local inspection after completing the analysis.
"""

from google.colab import files

# Download the SQLite database file from the Colab runtime to our computer
print("Downloading ecommerce.db...")
files.download("ecommerce.db")

"""The database file has been successfully downloaded from the Colab environment.  
This file can now be used for local inspection, backup, or submission if required.

## **Conclusion**

This assignment successfully demonstrated the use of SQLite and Python to perform structured data analysis on an e-commerce dataset. A relational database was queried using progressively complex SQL operations, and key business questions were answered through customer, order, revenue, and sales trend analyses. For the advanced queries, implementing both SQL-based and Python-based approaches and verifying identical results confirmed a strong understanding of data aggregation at both the database and application levels. Overall, the project meets all requirements and showcases practical skills in database querying, data processing, and analytical reasoning.
"""