Name: Ayush Katkurwar

Phone: +91-9022496085

Email: ayushkatkurwar3@gmail.com

Alt-Mobility Task for Data Analyst Intern Role:

• Environment Setup

Successfully installed the PostgreSQL database server and pgAdmin, and established a secure connection to the local PostgreSQL server using the appropriate credentials.

• Database Creation

• Connected to the local PostgreSQL server instance using pgAdmin.
• Created a new dedicated database named alt_mobility_assignment to keep the project data organized and isolated for this assignment.

• Database Schema

Within the alt_mobility_assignment database (under the public schema), the following table structures were defined:

1. customer_orders Table

• order_id (TEXT, Primary Key)
• customer_id (INTEGER)
• order_date (TEXT) – Initially defined as TEXT to accommodate import format
• order_amount (NUMERIC)
• shipping_address (TEXT)
• order_status (TEXT)

2. payments Table

• payment_id (TEXT, Primary Key)
• order_id (TEXT, Foreign Key referencing customer_orders.order_id)
• payment_date (TEXT) – Initially defined as TEXT to accommodate import format
• payment_amount (NUMERIC)
• payment_method (TEXT)
• payment_status (TEXT)

Constraints Applied:

• Primary Key constraints on order_id and payment_id
• Foreign Key constraint linking payments.order_id to customer_orders.order_id

• Data Loading

Data was imported into the PostgreSQL database using pgAdmin:

1. Navigated to the desired table (e.g., customer_orders, payments) in pgAdmin.
2. Used the "Import/Export Data..." feature.
3. Configured the import settings:
   • Selected the appropriate CSV file:
     • customer_orders.csv
     • payments.csv
   • Set the format to CSV.
   • Enabled the "Header" option.
   • Confirmed the delimiter was set to a comma (,).
4. Successfully loaded the data into the respective tables.

• Initial Data Inspection & Verification

• Used the pgAdmin Query Tool to run basic SQL commands for initial data checks:
  • Viewed sample rows using:
    SELECT * FROM table_name LIMIT 10;
  • Checked total row counts:
    SELECT COUNT(*) FROM table_name;
  • Investigated unique values in key categorical columns:
    • SELECT DISTINCT order_status FROM customer_orders;
    • SELECT DISTINCT payment_status FROM payments;
    • SELECT DISTINCT payment_method FROM payments;
  • Checked for NULL values in essential columns.
  • Verified the uniqueness of primary keys to ensure data integrity.

• Data Type Conversion (Critical Date Handling)

• Confirmed that although the original CSVs used the DD-MM-YYYY date format, the import process resulted in order_date and payment_date being stored as text in the format YYYY-MM-DD.

• Added temporary columns with the correct DATE data type:

  • order_date_dt in the customer_orders table
  • payment_date_dt in the payments table

• Populated the new DATE columns by casting the existing text columns:

  UPDATE customer_orders
  SET order_date_dt = CAST(order_date AS DATE);
  
  UPDATE payments
  SET payment_date_dt = CAST(payment_date AS DATE);
  

• Compared the old text columns with the new date columns to ensure the values matched.

• Dropped the original text-based date columns:

  • order_date from customer_orders
  • payment_date from payments

• Renamed the new DATE columns back to the original names:

  ALTER TABLE customer_orders
  RENAME COLUMN order_date_dt TO order_date;
  
  ALTER TABLE payments
  RENAME COLUMN payment_date_dt TO payment_date;
  

📝 Task 1: Order and Sales Analysis

🎯 Objective:
Analyze order status and sales data to provide insights into order fulfillment and revenue trends. Identify key metrics and trends related to order status and sales.

This task involves looking at two main aspects:

• Order Fulfillment: Understanding the lifecycle and status of orders placed.
• Sales & Revenue: Understanding the monetary value generated and its patterns over time.

1. Analyzing Order Fulfillment Status

Understanding the status distribution of orders helps assess operational efficiency.

• A high number of 'pending' or 'shipped' (but not 'delivered') orders might indicate processing bottlenecks.
• Comparing 'delivered' to other final states (like 'canceled', if present) provides insight into the completion rate.

🔍 SQL Query:

SELECT
    order_status,
    COUNT(*) AS number_of_orders,
    ROUND((COUNT(*) * 100.0 / SUM(COUNT(*)) OVER ()), 2) AS percentage_of_total_orders
FROM
    customer_orders
GROUP BY
    order_status
ORDER BY
    number_of_orders DESC;

2. Analyzing Sales Revenue

🔍 SQL Query:

SELECT
    SUM(payment_amount) AS total_revenue
FROM
    payments
WHERE
    LOWER(payment_status) = 'completed';

Provides insight into the overall top-line revenue based on successful (completed) transactions.
This helps evaluate the financial performance of the system.

🔍 SQL Query:

SELECT 
    TO_CHAR(payment_date, 'YYYY-MM') AS payment_month,
    SUM(payment_amount) AS monthly_revenue
FROM
    payments
WHERE
    LOWER(payment_status) = 'completed'
GROUP BY
    payment_month
ORDER BY
    payment_month;

Identifies growth patterns, seasonality, or declines in sales performance. Crucial for business health monitoring.

3. Identifying Key Sales Metrics

🔍 SQL Query:

SELECT
    AVG(payment_amount) AS average_order_value
FROM
    payments
WHERE
    LOWER(payment_status) = 'completed';

Measures the average revenue generated per successful transaction. Helps understand purchasing behavior and optimize pricing/promotions.

🔍 SQL Query:

WITH CompletedOrderTotals AS (
    SELECT
        order_id,
        SUM(payment_amount) AS total_revenue_for_order
    FROM
        payments
    WHERE
        LOWER(payment_status) = 'completed'
    GROUP BY
        order_id
)
SELECT
    AVG(total_revenue_for_order) AS average_revenue_per_completed_order
FROM
    CompletedOrderTotals;

Slightly different perspective, focusing on the value derived per unique customer order that was successfully paid for.

4. Analyzing Trends Related to Order Status

🔍 SQL Query:

SELECT
    TO_CHAR(order_date, 'YYYY-MM') AS order_placement_month,
    COUNT(order_id) AS number_of_orders_placed
FROM
    customer_orders
GROUP BY
    order_placement_month
ORDER BY
    order_placement_month;

Shows the trend in customer activity or demand initiation.

📝 Task 2: Customer Analysis

🎯 Objective: Explore customer ordering behavior to identify patterns such as repeat ordering, customer segmentation based on frequency, and trends over time.

1. Identifying Repeat Customers

🔍 SQL Query:

SELECT
    customer_id,
    COUNT(order_id) AS number_of_orders
FROM
    customer_orders
GROUP BY
    customer_id 
HAVING
    COUNT(order_id) > 1
ORDER BY
    number_of_orders DESC;

This identifies your loyal or engaged customer base. Understanding the size and purchasing frequency of this group is vital for retention strategies.

2. Customer Segmentation by Order Frequency

🔍 SQL Query:

WITH CustomerOrderCounts AS (
    SELECT
        customer_id,
        COUNT(order_id) AS order_count
    FROM
        customer_orders
    GROUP BY
        customer_id
)
SELECT
    order_count,
    COUNT(customer_id) AS number_of_customers
FROM
    CustomerOrderCounts
GROUP BY
    order_count
ORDER BY
    order_count;

This provides a simple but powerful behavioral segmentation. It shows the distribution of your customer base from one-time buyers to highly frequent purchasers. This helps understand the overall loyalty structure.

3. Analyzing Customer Activity Trends Over Time

🔍 SQL Query:

WITH CustomerFirstOrder AS (
    SELECT
        customer_id,
        MIN(order_date) AS first_order_date
    FROM customer_orders
    GROUP BY customer_id
),
MonthlyOrders AS (
     SELECT
        customer_id,
        TO_CHAR(order_date, 'YYYY-MM') AS order_month,
        order_date
     FROM customer_orders
)
SELECT
    mo.order_month,
    COUNT(DISTINCT mo.customer_id) AS total_active_customers,
    COUNT(DISTINCT CASE WHEN mo.order_date = cfo.first_order_date THEN mo.customer_id ELSE NULL END) AS new_customers_this_month, -- Customers whose first order was this month
    COUNT(DISTINCT CASE WHEN mo.order_date > cfo.first_order_date THEN mo.customer_id ELSE NULL END) AS returning_customers_this_month -- Customers active this month but whose first order was earlier
FROM MonthlyOrders mo
JOIN CustomerFirstOrder cfo ON mo.customer_id = cfo.customer_id
GROUP BY mo.order_month
ORDER BY mo.order_month;

Tracks overall customer engagement monthly. Distinguishing between new and returning customers helps understand if growth is driven by acquisition or retention.

📝 Task 3: Payment Status Analysis

🎯 Objective: Investigate payment status data to identify potential issues or trends related to payment success and failure.

1. Overall Payment Status Distribution

🔍 SQL Query:

SELECT
    payment_status,
    COUNT(*) AS count_status,
    ROUND((COUNT(*) * 100.0 / SUM(COUNT(*)) OVER ()), 2) AS percentage_status
FROM
    payments
GROUP BY
    payment_status
ORDER BY
    count_status DESC;

Provides a high-level view of payment processing health. A high failure or pending rate signals potential problems needing investigation.

2. Payment Status Breakdown by Payment Method

🔍 SQL Query:

SELECT
    payment_method,
    payment_status,
    COUNT(*) AS count_status
FROM
    payments
GROUP BY
    payment_method, payment_status
ORDER BY
    payment_method, count_status DESC;

Helps pinpoint if issues are widespread or isolated to specific payment processors or types (e.g., credit cards failing more often than bank transfers).

3. Trend Analysis of Payment Failures

🔍 SQL Query:

SELECT
    TO_CHAR(payment_date, 'YYYY-MM') AS payment_month,
    COUNT(*) AS failed_payment_count
FROM
    payments
WHERE
    LOWER(payment_status) = 'failed'
GROUP BY
    payment_month
ORDER BY
    payment_month;

Identifies whether payment problems are worsening systemically or perhaps improving after interventions. Can reveal seasonality in failures or impacts from external events/changes.

🔍 SQL Query:

SELECT
    payment_month,
    total_payments,
    failed_payments,
    CASE
        WHEN total_payments = 0 THEN 0
        ELSE ROUND((failed_payments * 100.0 / total_payments), 2)
    END AS failure_rate_percent
FROM (
    SELECT
        TO_CHAR(payment_date, 'YYYY-MM') AS payment_month,
        COUNT(*) AS total_payments,
        COUNT(*) FILTER (WHERE LOWER(payment_status) = 'failed') AS failed_payments
    FROM payments
    GROUP BY payment_month
) AS MonthlyCounts 
ORDER BY payment_month;

This gives better context than just the count, as it accounts for changes in overall transaction volume.

📝 Task 4: Order Details Report Query

🎯 Objective: Create a comprehensive SQL query that provides a detailed, row-level overview of order information, payment details, and potentially some calculated row-level metrics if applicable (though the prompt primarily implies joining existing details).

🔍 SQL Query:

SELECT
    co.order_id,
    co.customer_id,
    co.order_date,         
    co.order_amount,        
    co.order_status,        
    co.shipping_address,
    p.payment_id,
    p.payment_date,  
    p.payment_amount AS payment_attempt_amount,
    p.payment_method,
    p.payment_status,
    CASE
        WHEN LOWER(p.payment_status) = 'completed' AND p.payment_amount = co.order_amount THEN 'Match'
        WHEN LOWER(p.payment_status) = 'completed' AND p.payment_amount <> co.order_amount THEN 'Mismatch'
        ELSE 'N/A'
    END AS payment_order_amount_match_status,
    CASE
        WHEN p.payment_date IS NOT NULL THEN p.payment_date - co.order_date 
        ELSE NULL
    END AS days_between_order_and_payment
FROM
    customer_orders co
LEFT JOIN
    payments p ON co.order_id = p.order_id 
ORDER BY
    co.order_date DESC, 
    co.order_id,     
    p.payment_date;